[IQUG] [EXTERNAL] Re: more strange IQ16 performance

John Anthony chirayithaj at gmail.com
Thu Jul 19 08:25:25 MST 2018


2 points :-
1. Posting  the QPs might help analysis although i doubt much can be done
to change plan
2. the QP plan generation process is just too complicated, based on many
factors and evolving at a fair pace  - practically speaking, the path taken
will likely surprise the engineers, in many cases

-JA

On Thu, Jul 19, 2018 at 9:37 AM John KIRKWOOD <
john.kirkwood at uk.bnpparibas.com> wrote:

> Yes it could but the volume for one clc_dt of data is pm_prd: 1G and
> deal_val_exp: 2G in each of the tables
>
>
>
> So cannot see that having an impact – maybe SAP can tell me how to
> estimate that
>
>
>
> JOhn
>
>
>
>
>
> *From:* John Anthony [mailto:chirayithaj at gmail.com]
> *Sent:* 19 July 2018 15:29
> *To:* John KIRKWOOD
> *Cc:* iqug at iqug.org
> *Subject:* [EXTERNAL] Re: [IQUG] more strange IQ16 performance
>
>
>
> Can lower memory in TEST not explain the difference ?
>
>
>
> Overall sortMerge is preferred when QP estimates runtime available memory
> to be insufficient for other strategies - a push down does more in-memory
> filters.
>
>
>
> -JA
>
>
>
> On Thu, Jul 19, 2018 at 9:12 AM John KIRKWOOD <
> john.kirkwood at uk.bnpparibas.com> wrote:
>
> We refreshed our TEST database with our PRD database
>
>
>
> The command
>
>
>
> *select **count*(*)
>
> *from *deal_val_exp *as *expo_deal
>
> *inner join *pm_prd *as *prd *on *prd.clc_dt = expo_deal.clc_dt *and *prd.prd_hsh_id
> = expo_deal.prd_hsh_id
>
> *where case when *prd.rsk_site = 'NA' *then *expo_deal.rsk_sit_cod *else *prd.rsk_site
> *end in *('ALM-BNL-IRD')
>
> *and *(((expo_deal.sim_mtd_cod *in *('OFFICIAL')))) *and *expo_deal.clc_dt
> = '2017/12/29' *and *expo_deal.node_cod *in *('RN_ALM_BNL_IRD')
>
>
>
> registers a fetch time of 620ms in PRD  (0.48s on the plan)
>
> it does a sort-merge push down join on pm_prd to project 664 rows from
> both tables
>
>
>
> the same command registers a fetch time of 1s in TEST (0.85s on the plan)
>
> it does sort-merge join projecting 664 rows from deal_val_exp and
> 10,166,043 rows from pm_prd (based on clc_dt)
>
>
>
> I cannot get it to do a push down join even with the appropriate
> join_preference setting
>
>
>
> Schema/indexes is same; data distribution is same – although again I
> cannot see any cardinality on sp_iqhelp for the TEST tables – which is very
> annoying and extremely suspicious as it shows on PRD sp_iqhelp
>
> Prd_hsh_id and clc_dt both have HG index on both tables
>
> Datatypes are identical on both tables hsh_id unsigned bigint; clc_dt date
>
>
>
> This is part of a much larger command which runs in 1 second in PRD but
> runs over our 10 minute timeout in TEST
>
>
>
> PRD has main and temp buffers of 140G
>
> TEST has main and temp buffer space of 50G
>
>
>
> Appreciate any help on why the plans are different?
>
> The command does not exactly stretch the boundaries of anything that I can
> see
>
>
>
> JOhn
>
>
>
>
> ___________________________________________________________
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received this e-mail in error)
> please notify the sender immediately and delete this e-mail. Any
> unauthorised copying, disclosure or distribution of the material in this
> e-mail is prohibited.
>
> Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for
> additional disclosures.
>
> _______________________________________________
> IQUG mailing list
> IQUG at iqug.org
> http://iqug.org/mailman/listinfo/iqug
>
>
> ___________________________________________________________
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received this e-mail in error)
> please notify the sender immediately and delete this e-mail. Any
> unauthorised copying, disclosure or distribution of the material in this
> e-mail is prohibited.
>
> Please refer to http://www.bnpparibas.co.uk/en/email-disclaimer/ for
> additional disclosures.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20180719/cc63bf82/attachment.html>


More information about the IQUG mailing list