RSS/HDT Sec - Different Query Plans???

Posted in 2015

Summary

Dan Mueller (IDS 11.70.FC8 on AIX 7.1) found the same four-table join producing different query plans (and very different estimated costs) on an HDR secondary versus an RSS secondary attached to the same primary. Suggestions included checking that the ONCONFIG files match (OPTCOMPIND was 1 on both), a known bug where update statistics on the primary doesn't refresh the distributions cached in memory on secondaries (workaround: restart the secondary or flush the dictionary cache), and APAR IC92934 on index use after table alters. Art questioned whether either plan was optimal and asked about distributions/indexes on reservation.site_call_date. No resolution is recorded; Dan said he planned to open a PMR.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: High Availability & Replication, Performance & Tuning, Platform-Specific Issues

DAN MUELLER — Mon, 06 Jul 2015 18:06:21 — source: IIUG Forums & Mailing Lists

Hello Folks, O/S AIX 7.1 IDS 11.70.FC8XB8 I have a query (and I can supply it if requested) that gets a different query plan between the RSS and HDR secondary both attached to the same primary. I was certainly not expecting this. What could be causing this behavior? Thanx, Dan

↪ replying to DAN MUELLER

Art Kagel — Mon, 06 Jul 2015 18:17:23 — source: IIUG Forums & Mailing Lists

Are you certain that the ONCONFIG files are identical (except for the secondary config parameters)? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 6, 2015 at 2:06 PM, DAN MUELLER <ddmueller@intercall.com> wrote: > Hello Folks, > > O/S AIX 7.1 > IDS 11.70.FC8XB8 > > I have a query (and I can supply it if requested) that gets a different > query > plan between the RSS and HDR secondary both attached to the same primary. I > was certainly not expecting this. What could be causing this behavior? > > Thanx, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ec892a3c2a6051a38ea7e

↪ replying to Art Kagel

NATE HICKS — Tue, 07 Jul 2015 15:12:52 — source: IIUG Forums & Mailing Lists

Could it be stats too? More current stats on one and stale stats on the other?

↪ replying to NATE HICKS

Art Kagel — Tue, 07 Jul 2015 15:21:05 — source: IIUG Forums & Mailing Lists

Stats and distributions are replicated along with all the other data. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 7, 2015 at 11:12 AM, NATE HICKS <nathaniel.hicks@trnswrks.com> wrote: > Could it be stats too? More current stats on one and stale stats on the > other? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1134b9640fb4ed051a4a923c

↪ replying to Art Kagel

Fernando Nunes — Tue, 07 Jul 2015 16:16:21 — source: IIUG Forums & Mailing Lists

True... But there were some bugs that when we run updatet statistics on the primary, and when the resulting catalog data was sent to the secondary(ies) the in memory cache of the distributions was not refreshed. This can cause the mentioned effect. Regards. On Tue, Jul 7, 2015 at 4:21 PM, Art Kagel <art.kagel@gmail.com> wrote: > Stats and distributions are replicated along with all the other data. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Tue, Jul 7, 2015 at 11:12 AM, NATE HICKS <nathaniel.hicks@trnswrks.com> > wrote: > > > Could it be stats too? More current stats on one and stale stats on the > > other? > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a1134b9640fb4ed051a4a923c > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --089e01493e7c9b291e051a4b578b

↪ replying to Fernando Nunes

Art Kagel — Tue, 07 Jul 2015 16:22:12 — source: IIUG Forums & Mailing Lists

Interesting, Fernando. Good to know. Is there a fix/patch available for this yet or what versions is it already fixed in? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 7, 2015 at 12:16 PM, Fernando Nunes <domusonline@gmail.com> wrote: > True... But there were some bugs that when we run updatet statistics on the > primary, and when the resulting catalog data was sent to the secondary(ies) > the in memory cache of the distributions was not refreshed. > This can cause the mentioned effect. > > Regards. > > On Tue, Jul 7, 2015 at 4:21 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Stats and distributions are replicated along with all the other data. > > > > Art > > > > Art S. Kagel, President and Principal Consultant > > ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on the IIUG, nor any other organization with which I > am > > associated either explicitly, implicitly, or by inference. Neither do > > those opinions reflect those of other individuals affiliated with any > > entity with which I am affiliated nor those of the entities themselves. > > > > On Tue, Jul 7, 2015 at 11:12 AM, NATE HICKS < > nathaniel.hicks@trnswrks.com> > > wrote: > > > > > Could it be stats too? More current stats on one and stale stats on the > > > other? > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1134b9640fb4ed051a4a923c > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --089e01493e7c9b291e051a4b578b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc11bc8e2282051a4b6cc8

↪ replying to Art Kagel

Mueller, Daniel D. — Tue, 07 Jul 2015 16:24:54 — source: IIUG Forums & Mailing Lists

Yes, this is an interesting turn. Would really like to know where those are fixed. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, July 07, 2015 12:22 PM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35394] Interesting, Fernando. Good to know. Is there a fix/patch available for this yet or what versions is it already fixed in? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Jul 7, 2015 at 12:16 PM, Fernando Nunes <domusonline@gmail.com> wrote: > True... But there were some bugs that when we run updatet statistics > on the primary, and when the resulting catalog data was sent to the > secondary(ies) the in memory cache of the distributions was not refreshed. > This can cause the mentioned effect. > > Regards. > > On Tue, Jul 7, 2015 at 4:21 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Stats and distributions are replicated along with all the other data. > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database > > Management www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on the IIUG, nor any other organization > > with which I > am > > associated either explicitly, implicitly, or by inference. Neither > > do those opinions reflect those of other individuals affiliated with > > any entity with which I am affiliated nor those of the entities themselves. > > > > On Tue, Jul 7, 2015 at 11:12 AM, NATE HICKS < > nathaniel.hicks@trnswrks.com> > > wrote: > > > > > Could it be stats too? More current stats on one and stale stats > > > on the other? > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --001a1134b9640fb4ed051a4a923c > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --089e01493e7c9b291e051a4b578b > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bdc11bc8e2282051a4b6cc8 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mueller, Daniel D.

Fernando Nunes — Tue, 07 Jul 2015 16:54:20 — source: IIUG Forums & Mailing Lists

I'm 90% I hit this in 11.50.FC7. And I either opened a PMR which lead into a fix or there were already fixes available. Sorry for being so vague, but I'd say any "recent" version would have the fixes unless there was some regression. Naturally this is technically easy to test... just restart the engine... if you can do it it's another matter :) But keep in midn that there may be other reasons for this like different configurations at the engine or session level. Finally... At the time I was able to "purge" the cache without restarting the engine... but it was very difficult (it would have been easier if the DD_* parameters were at the defaults which was not the case (in fact they were overestimated). The way to do it was to generate a script that would select the first N records of each table in the database.... something like that... after a while sometimes the wrong data was purged from the cache... Regards. On Tue, Jul 7, 2015 at 5:24 PM, Mueller, Daniel D. <ddmueller@intercall.com> wrote: > Yes, this is an interesting turn. Would really like to know where those are > fixed. > > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, July 07, 2015 12:22 PM > To: ids@iiug.org > Subject: Re: RSS/HDT Sec - Different Query Plans??? [35394] > > Interesting, Fernando. Good to know. Is there a fix/patch available for > this > yet or what versions is it already fixed in? > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Tue, Jul 7, 2015 at 12:16 PM, Fernando Nunes <domusonline@gmail.com> > wrote: > > > True... But there were some bugs that when we run updatet statistics > > on the primary, and when the resulting catalog data was sent to the > > secondary(ies) the in memory cache of the distributions was not > refreshed. > > This can cause the mentioned effect. > > > > Regards. > > > > On Tue, Jul 7, 2015 at 4:21 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > > > Stats and distributions are replicated along with all the other data. > > > > > > Art > > > > > > Art S. Kagel, President and Principal Consultant ASK Database > > > Management www.askdbmgt.com > > > > > > Blog: http://informix-myview.blogspot.com/ > > > > > > Disclaimer: Please keep in mind that my own opinions are my own > > > opinions and do not reflect on the IIUG, nor any other organization > > > with which I > > am > > > associated either explicitly, implicitly, or by inference. Neither > > > do those opinions reflect those of other individuals affiliated with > > > any entity with which I am affiliated nor those of the entities > themselves. > > > > > > On Tue, Jul 7, 2015 at 11:12 AM, NATE HICKS < > > nathaniel.hicks@trnswrks.com> > > > wrote: > > > > > > > Could it be stats too? More current stats on one and stale stats > > > > on the other? > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > > > > > --001a1134b9640fb4ed051a4a923c > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --089e01493e7c9b291e051a4b578b > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7bdc11bc8e2282051a4b6cc8 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a1134612c6b0ad4051a4bdff7

↪ replying to DAN MUELLER

BENJAMIN THOMPSON — Wed, 08 Jul 2015 11:53:39 — source: IIUG Forums & Mailing Lists

Could this bug apply to you? IC92934 INDEX MAY NOT BE USED ON SECONDARY SERVERS FOLLOWING TABLE ALTER It is fixed in 11.70.FC7W3 and presumably later 12.10 fix packs, otherwise restarting the secondary is the workaround.

↪ replying to BENJAMIN THOMPSON

BENJAMIN THOMPSON — Wed, 08 Jul 2015 11:57:04 — source: IIUG Forums & Mailing Lists

Early start for me this morning... Looking at your version I am guessing this probably isn't it. How about giving us more clues by posting the query and the two alternative plans from "set explain on"?

↪ replying to Art Kagel

BENJAMIN THOMPSON — Wed, 08 Jul 2015 11:59:44 — source: IIUG Forums & Mailing Lists

In particular OPTCOMPIND?

↪ replying to BENJAMIN THOMPSON

Mueller, Daniel D. — Wed, 08 Jul 2015 13:25:49 — source: IIUG Forums & Mailing Lists

Both are set to 1. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of BENJAMIN THOMPSON Sent: Wednesday, July 08, 2015 8:00 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35399] In particular OPTCOMPIND? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mueller, Daniel D.

Art Kagel — Wed, 08 Jul 2015 13:35:38 — source: IIUG Forums & Mailing Lists

Both the same they should behave the same. That said, if this is an OLTP system they should be set to 0. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 9:25 AM, Mueller, Daniel D. <ddmueller@intercall.com> wrote: > Both are set to 1. > > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > BENJAMIN > THOMPSON > Sent: Wednesday, July 08, 2015 8:00 AM > To: ids@iiug.org > Subject: Re: RSS/HDT Sec - Different Query Plans??? [35399] > > In particular OPTCOMPIND? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0116197ab16c79051a5d369b

↪ replying to Art Kagel

Mueller, Daniel D. — Wed, 08 Jul 2015 13:42:57 — source: IIUG Forums & Mailing Lists

It is a mixed system but heavy OLTP during the day I am going to post the plans w/query very soon if anyone is interested but think it is time to open a PMR. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, July 08, 2015 9:36 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35401] Both the same they should behave the same. That said, if this is an OLTP system they should be set to 0. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 9:25 AM, Mueller, Daniel D. <ddmueller@intercall.com> wrote: > Both are set to 1. > > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > BENJAMIN THOMPSON > Sent: Wednesday, July 08, 2015 8:00 AM > To: ids@iiug.org > Subject: Re: RSS/HDT Sec - Different Query Plans??? [35399] > > In particular OPTCOMPIND? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0116197ab16c79051a5d369b ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to DAN MUELLER

DAN MUELLER — Wed, 08 Jul 2015 13:53:47 — source: IIUG Forums & Mailing Lists

Forgive the long post but I have been asked for the 2 sqexplain outputs. Also, just because nothiung is easy, the 2 secondaries were switched from their origunal status. IE... What is now the NDR SEC was originally an RSS Sec and vice versa. Not sure if that plays into this or not. Also, it seems to be to be time to open a PMR. HDR QUERY PLAN QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05) ------ SELECT reservation.res_id , reservation.conference_id , reservation.owner_number , reservation.site_id , reservation.series_id , reservation.call_date , reservation.timezone_code , reservation.reservation_status , reservation.placed_via , reservation.employee_id , reservation.call_setup_mins , reservation.site_call_date , reservation.bu_call_date , reservation.duration , reservation.legs , reservation.call_type , reservation.special_indicator , reservation.bridge_id , reservation.team_id , reservation.pac_code_value , reservation.per_fname , reservation.per_lname , reservation.per_position ,reservation.per_country_code , reservation.per_phone_num , reservation.per_ext , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname , reservation.ldr_lname , reservation.ldr_position , reservation.ldr_country_code , reservation.ldr_phone_num , reservation.ldr_ext , reservation.ldr_fax_num , reservation.ldr_email , reservation.confirm_fax_num , reservation.confirm_country_code , reservation.confirm_email , reservation.confirm_format , reservation.confirm_fmt , reservation.password , reservation.setup_status , reservation.upper_ldr_lname , reservation.date_added , reservation.progress_res , reservation.topic , reservation.acct_specialist , reservation.schedule_ind , reservation.bridge_conf_num , reservation.premium , reservation.walk_thru_flag , reservation.cancel_date , reservation.do_conf_only , reservation.template_id FROM informix.owner owner, informix.account account, informix.company company, informix.reservation reservation where ( company.bu_id = 1 OR company.bu_id = 4) and ( account.company_number = company.company_number ) and ( reservation.owner_number = owner.owner_number ) and ( owner.account_number = account.account_number ) and ( reservation.site_call_date > '2015-06-01 00:00:00' ) Estimated Cost: 42464232 Estimated # of Rows Returned: 12383288 1) informix.company: INDEX PATH (1) Index Name: informix.idx_co_bu_co Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.company.bu_id = 1 (2) Index Name: informix.idx_co_bu_co Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.company.bu_id = 4 2) informix.account: SEQUENTIAL SCAN DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.account.company_number = informix.company.company_number 3) informix.owner: INDEX PATH (1) Index Name: informix.idx_own_acct_own Index Keys: account_number owner_number (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.owner.account_number = informix.account.account_number NESTED LOOP JOIN 4) informix.reservation: SEQUENTIAL SCAN Filters: Table Scan Filters: informix.reservation.site_call_date > datetime(2015-06-01 00:00:00) year to second DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: informix.reservation.owner_number = informix.owner.owner_number RSS QUERY PLAN QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:44:06) ------ SELECT reservation.res_id , reservation.conference_id , reservation.owner_number , reservation.site_id , reservation.series_id , reservation.call_date , reservation.timezone_code , reservation.reservation_status , reservation.placed_via , reservation.employee_id , reservation.call_setup_mins , reservation.site_call_date , reservation.bu_call_date , reservation.duration , reservation.legs , reservation.call_type , reservation.special_indicator , reservation.bridge_id , reservation.team_id , reservation.pac_code_value , reservation.per_fname , reservation.per_lname , reservation.per_position ,reservation.per_country_code , reservation.per_phone_num , reservation.per_ext , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname , reservation.ldr_lname , reservation.ldr_position , reservation.ldr_country_code , reservation.ldr_phone_num , reservation.ldr_ext , reservation.ldr_fax_num , reservation.ldr_email , reservation.confirm_fax_num , reservation.confirm_country_code , reservation.confirm_email , reservation.confirm_format , reservation.confirm_fmt , reservation.password , reservation.setup_status , reservation.upper_ldr_lname , reservation.date_added , reservation.progress_res , reservation.topic , reservation.acct_specialist , reservation.schedule_ind , reservation.bridge_conf_num , reservation.premium , reservation.walk_thru_flag , reservation.cancel_date , reservation.do_conf_only , reservation.template_id FROM informix.owner owner, informix.account account, informix.company company, informix.reservation reservation where ( company.bu_id = 1 OR company.bu_id = 4) and ( account.company_number = company.company_number ) and ( reservation.owner_number = owner.owner_number ) and ( owner.account_number = account.account_number ) and ( reservation.site_call_date > '2015-06-01 00:00:00' ) Estimated Cost: 87333752 Estimated # of Rows Returned: 12383288 1) informix.reservation: SEQUENTIAL SCAN Filters: informix.reservation.site_call_date > datetime(2015-06-01 00:00:00) year to second 2) informix.owner: INDEX PATH (1) Index Name: informix.idx_owner_pk Index Keys: owner_number (Serial, fragments: ALL) Lower Index Filter: informix.reservation.owner_number = informix.owner.owner_number NESTED LOOP JOIN 3) informix.account: INDEX PATH (1) Index Name: informix.idx_account_pk Index Keys: account_number (Serial, fragments: ALL) Lower Index Filter: informix.owner.account_number = informix.account.account_number NESTED LOOP JOIN 4) informix.company: INDEX PATH (1) Index Name: informix.idx_co_bu_co Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.company.bu_id = 1 (2) Index Name: informix.idx_co_bu_co Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) Lower Index Filter: informix.company.bu_id = 4 DYNAMIC HASH JOIN Dynamic Hash Filters: informix.account.company_number = informix.company.company_number

↪ replying to DAN MUELLER

Art Kagel — Wed, 08 Jul 2015 14:21:58 — source: IIUG Forums & Mailing Lists

Dan: Both queries estimate 12,383,288 rows returned what is the actual count? I agree that the main issue is that the query plans are different and perform differently, but I suspect that neither is optimal. Are there distributions on the reservation.site_call_date column? Is it indexed? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 9:53 AM, DAN MUELLER <ddmueller@intercall.com> wrote: > Forgive the long post but I have been asked for the 2 sqexplain outputs. > Also, > just because nothiung is easy, the 2 secondaries were switched from their > origunal status. IE... What is now the NDR SEC was originally an RSS Sec > and > vice versa. Not sure if that plays into this or not. Also, it seems to be > to > be time to open a PMR. > > HDR QUERY PLAN > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05) > ------ > SELECT reservation.res_id , reservation.conference_id , > reservation.owner_number , reservation.site_id , reservation.series_id , > reservation.call_date , reservation.timezone_code , > reservation.reservation_status , reservation.placed_via , > reservation.employee_id , reservation.call_setup_mins , > reservation.site_call_date , reservation.bu_call_date , > reservation.duration , > reservation.legs , reservation.call_type , reservation.special_indicator , > reservation.bridge_id , reservation.team_id , reservation.pac_code_value , > reservation.per_fname , reservation.per_lname , reservation.per_position , > reservation.per_country_code , reservation.per_phone_num ,> reservation.per_ext > , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname , > reservation.ldr_lname , reservation.ldr_position , > reservation.ldr_country_code , reservation.ldr_phone_num , > reservation.ldr_ext > , reservation.ldr_fax_num , reservation.ldr_email , > reservation.confirm_fax_num , reservation.confirm_country_code , > reservation.confirm_email , reservation.confirm_format , > reservation.confirm_fmt , reservation.password , reservation.setup_status , > reservation.upper_ldr_lname , reservation.date_added , > reservation.progress_res , reservation.topic , reservation.acct_specialist > , > reservation.schedule_ind , reservation.bridge_conf_num , > reservation.premium , > reservation.walk_thru_flag , reservation.cancel_date , > reservation.do_conf_only , reservation.template_id > FROM informix.owner owner, informix.account account, informix.company > company, > informix.reservation reservation > where ( company.bu_id = 1 OR > company.bu_id = 4) and ( account.company_number = company.company_number ) > and > ( reservation.owner_number = owner.owner_number ) and ( > owner.account_number = > account.account_number ) and ( reservation.site_call_date > '2015-06-01 > 00:00:00' ) > > Estimated Cost: 42464232 > Estimated # of Rows Returned: 12383288 > > 1) informix.company: INDEX PATH > > (1) Index Name: informix.idx_co_bu_co > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.company.bu_id = 1 > > (2) Index Name: informix.idx_co_bu_co > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.company.bu_id = 4 > > 2) informix.account: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN (Build Outer) > > Dynamic Hash Filters: informix.account.company_number = > informix.company.company_number > > 3) informix.owner: INDEX PATH > > (1) Index Name: informix.idx_own_acct_own > > Index Keys: account_number owner_number (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.owner.account_number = > informix.account.account_number > NESTED LOOP JOIN > > 4) informix.reservation: SEQUENTIAL SCAN > > Filters: > > Table Scan Filters: informix.reservation.site_call_date > > datetime(2015-06-01 > 00:00:00) year to second > > DYNAMIC HASH JOIN (Build Outer) > > Dynamic Hash Filters: informix.reservation.owner_number = > informix.owner.owner_number > > RSS QUERY PLAN > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:44:06) > ------ > SELECT reservation.res_id , reservation.conference_id , > reservation.owner_number , reservation.site_id , reservation.series_id , > reservation.call_date , reservation.timezone_code , > reservation.reservation_status , reservation.placed_via , > reservation.employee_id , reservation.call_setup_mins , > reservation.site_call_date , reservation.bu_call_date , > reservation.duration , > reservation.legs , reservation.call_type , reservation.special_indicator , > reservation.bridge_id , reservation.team_id , reservation.pac_code_value , > reservation.per_fname , reservation.per_lname , reservation.per_position , > reservation.per_country_code , reservation.per_phone_num ,> reservation.per_ext > , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname , > reservation.ldr_lname , reservation.ldr_position , > reservation.ldr_country_code , reservation.ldr_phone_num , > reservation.ldr_ext > , reservation.ldr_fax_num , reservation.ldr_email , > reservation.confirm_fax_num , reservation.confirm_country_code , > reservation.confirm_email , reservation.confirm_format , > reservation.confirm_fmt , reservation.password , reservation.setup_status , > reservation.upper_ldr_lname , reservation.date_added , > reservation.progress_res , reservation.topic , reservation.acct_specialist > , > reservation.schedule_ind , reservation.bridge_conf_num , > reservation.premium , > reservation.walk_thru_flag , reservation.cancel_date , > reservation.do_conf_only , reservation.template_id > FROM informix.owner owner, informix.account account, informix.company > company, > informix.reservation reservation > where ( company.bu_id = 1 OR > company.bu_id = 4) and ( account.company_number = company.company_number ) > and > ( reservation.owner_number = owner.owner_number ) and ( > owner.account_number = > account.account_number ) and ( reservation.site_call_date > '2015-06-01 > 00:00:00' ) > > Estimated Cost: 87333752 > Estimated # of Rows Returned: 12383288 > > 1) informix.reservation: SEQUENTIAL SCAN > > Filters: informix.reservation.site_call_date > datetime(2015-06-01 > 00:00:00) > year to second > > 2) informix.owner: INDEX PATH > > (1) Index Name: informix.idx_owner_pk > > Index Keys: owner_number (Serial, fragments: ALL) > > Lower Index Filter: informix.reservation.owner_number = > informix.owner.owner_number > NESTED LOOP JOIN > > 3) informix.account: INDEX PATH > > (1) Index Name: informix.idx_account_pk > > Index Keys: account_number (Serial, fragments: AL

↪ replying to DAN MUELLER

David Williams — Wed, 08 Jul 2015 14:54:48 — source: IIUG Forums & Mailing Lists

Use dbschema to check schema/indexes/stats are the same on both servers. Check client side environment variables, is the same client session used fo= r both queries? Regards, David. -----Original Message----- From: "DAN MUELLER" <ddmueller@intercall.com> Sent: =E2=80=8E08/=E2=80=8E07/=E2=80=8E2015 14:54 To: "ids@iiug.org" <ids@iiug.org> Subject: Re: RSS/HDT Sec - Different Query Plans??? [35403] Forgive the long post but I have been asked for the 2 sqexplain outputs. Al= so,=20 just because nothiung is easy, the 2 secondaries were switched from their=20 origunal status. IE... What is now the NDR SEC was originally an RSS Sec an= d=20 vice versa. Not sure if that plays into this or not. Also, it seems to be t= o=20 be time to open a PMR.=20 HDR QUERY PLAN=20 QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05)=20 ------=20 SELECT reservation.res_id , reservation.conference_id ,=20reservation.owner_number , reservation.site_id , reservation.series_id ,=20 reservation.call_date , reservation.timezone_code ,=20 reservation.reservation_status , reservation.placed_via ,=20 reservation.employee_id , reservation.call_setup_mins ,=20 reservation.site_call_date , reservation.bu_call_date , reservation.duratio= n ,=20 reservation.legs , reservation.call_type , reservation.special_indicator ,= =20 reservation.bridge_id , reservation.team_id , reservation.pac_code_value ,= =20 reservation.per_fname , reservation.per_lname , reservation.per_position ,= =20 reservation.per_country_code , reservation.per_phone_num , reservation.per_= ext=20 , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname ,= =20 reservation.ldr_lname , reservation.ldr_position ,=20 reservation.ldr_country_code , reservation.ldr_phone_num , reservation.ldr_= ext=20 , reservation.ldr_fax_num , reservation.ldr_email ,=20 reservation.confirm_fax_num , reservation.confirm_country_code ,=20 reservation.confirm_email , reservation.confirm_format ,=20 reservation.confirm_fmt , reservation.password , reservation.setup_status ,= =20 reservation.upper_ldr_lname , reservation.date_added ,=20 reservation.progress_res , reservation.topic , reservation.acct_specialist = ,=20 reservation.schedule_ind , reservation.bridge_conf_num , reservation.premiu= m ,=20 reservation.walk_thru_flag , reservation.cancel_date ,=20 reservation.do_conf_only , reservation.template_id=20 FROM informix.owner owner, informix.account account, informix.company compa= ny,=20 informix.reservation reservation=20 where ( company.bu_id =3D 1 OR=20 company.bu_id =3D 4) and ( account.company_number =3D company.company_numbe= r ) and=20 ( reservation.owner_number =3D owner.owner_number ) and ( owner.account_num= ber =3D=20 account.account_number ) and ( reservation.site_call_date > '2015-06-01=20 00:00:00' )=20 Estimated Cost: 42464232=20 Estimated # of Rows Returned: 12383288=20 1) informix.company: INDEX PATH=20 (1) Index Name: informix.idx_co_bu_co=20 Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL)=20 Lower Index Filter: informix.company.bu_id =3D 1=20 (2) Index Name: informix.idx_co_bu_co=20 Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL)=20 Lower Index Filter: informix.company.bu_id =3D 4=20 2) informix.account: SEQUENTIAL SCAN=20 DYNAMIC HASH JOIN (Build Outer)=20 Dynamic Hash Filters: informix.account.company_number =3D=20 informix.company.company_number=20 3) informix.owner: INDEX PATH=20 (1) Index Name: informix.idx_own_acct_own=20 Index Keys: account_number owner_number (Key-Only) (Serial, fragments: ALL)= =20 Lower Index Filter: informix.owner.account_number =3D=20 informix.account.account_number=20 NESTED LOOP JOIN=20 4) informix.reservation: SEQUENTIAL SCAN=20 Filters:=20 Table Scan Filters: informix.reservation.site_call_date > datetime(2015-06-= 01=20 00:00:00) year to second=20 DYNAMIC HASH JOIN (Build Outer)=20 Dynamic Hash Filters: informix.reservation.owner_number =3D=20 informix.owner.owner_number=20 RSS QUERY PLAN=20 QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:44:06)=20 ------=20 SELECT reservation.res_id , reservation.conference_id ,=20reservation.owner_number , reservation.site_id , reservation.series_id ,=20 reservation.call_date , reservation.timezone_code ,=20 reservation.reservation_status , reservation.placed_via ,=20 reservation.employee_id , reservation.call_setup_mins ,=20 reservation.site_call_date , reservation.bu_call_date , reservation.duratio= n ,=20 reservation.legs , reservation.call_type , reservation.special_indicator ,= =20 reservation.bridge_id , reservation.team_id , reservation.pac_code_value ,= =20 reservation.per_fname , reservation.per_lname , reservation.per_position ,= =20 reservation.per_country_code , reservation.per_phone_num , reservation.per_= ext=20 , reservation.per_fax_num , reservation.per_email , reservation.ldr_fname ,= =20 reservation.ldr_lname , reservation.ldr_position ,=20 reservation.ldr_country_code , reservation.ldr_phone_num , reservation.ldr_= ext=20 , reservation.ldr_fax_num , reservation.ldr_email ,=20 reservation.confirm_fax_num , reservation.confirm_country_code ,=20 reservation.confirm_email , reservation.confirm_format ,=20 reservation.confirm_fmt , reservation.password , reservation.setup_status ,= =20 reservation.upper_ldr_lname , reservation.date_added ,=20 reservation.progress_res , reservation.topic , reservation.acct_specialist = ,=20 reservation.schedule_ind , reservation.bridge_conf_num , reservation.premiu= m ,=20 reservation.walk_thru_flag , reservation.cancel_date ,=20 reservation.do_conf_only , reservation.template_id=20 FROM informix.owner owner, informix.account account, informix.company compa= ny,=20 informix.reservation reservation=20 where ( company.bu_id =3D 1 OR=20 company.bu_id =3D 4) and ( account.company_number =3D company.company_numbe= r ) and=20 ( reservation.owner_number =3D owner.owner_number ) and ( owner.account_num= ber =3D=20 account.account_number ) and ( reservation.site_call_date > '2015-06-01=20 00:00:00' )=20 Estimated Cost: 87333752=20 Estimated # of Rows Returned: 12383288=20 1) informix.reservation: SEQUENTIAL SCAN=20 Filters: informix.reservation.site_call_date > datetime(2015-06-01 00:00:00= )=20 year to second=20 2) informix.owner: INDEX PATH=20 (1) Index Name: informix.idx_owner_pk=20 Index Keys: owner_number (Serial, fragments: ALL)=20 Lower Index Filter: informix.reservation.owner_number =3D=20 informix.owner.owner_number=20 NESTED LOOP JOIN=20 3) informix.account: INDEX PATH=20 (1) Index Name: informix.idx_account_pk=20 Index Keys: account_number (Serial, fragments: ALL)=20 Lower Index Filter: informix.owner.account_number =3D=20 informix.account.account_number=20 NESTED LOOP JOIN=20 4) informix.company: INDEX PATH=20 (1) Index Name: informix.idx_co_bu_co=20 Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL)=

↪ replying to Art Kagel

Mueller, Daniel D. — Wed, 08 Jul 2015 15:05:03 — source: IIUG Forums & Mailing Lists

Art, Cannot answer the actual number of rows at this time. This was discovered during some month end processing a few days ago and I was not involved. The DBA on call switched them to run on the RSS vs the HDR and the query ran in the expected time. I have not fully run the query, only with avoid_execute. That is how I got the plans. The reservation.site_call_date IS indexed. It is a btree non-unique index. We run your dostats for the distribution weekly. I actually unloaded the rows from sysdistrib for the tabid/colon and ran the results through diff. They are all identical. Would it be helpful to executw the entire query on both secondaries for the row count? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Wednesday, July 08, 2015 10:22 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35404] Dan: Both queries estimate 12,383,288 rows returned what is the actual count? I agree that the main issue is that the query plans are different and perform differently, but I suspect that neither is optimal. Are there distributions on the reservation.site_call_date column? Is it indexed? Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 9:53 AM, DAN MUELLER <ddmueller@intercall.com> wrote: > Forgive the long post but I have been asked for the 2 sqexplain outputs. > Also, > just because nothiung is easy, the 2 secondaries were switched from > their origunal status. IE... What is now the NDR SEC was originally an > RSS Sec and vice versa. Not sure if that plays into this or not. Also, > it seems to be to be time to open a PMR. > > HDR QUERY PLAN > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05) > ------ > SELECT reservation.res_id , reservation.conference_id ,> reservation.owner_number , reservation.site_id , reservation.series_id > , reservation.call_date , reservation.timezone_code , > reservation.reservation_status , reservation.placed_via , > reservation.employee_id , reservation.call_setup_mins , > reservation.site_call_date , reservation.bu_call_date , > reservation.duration , reservation.legs , reservation.call_type , > reservation.special_indicator , reservation.bridge_id , > reservation.team_id , reservation.pac_code_value , > reservation.per_fname , reservation.per_lname , > reservation.per_position , reservation.per_country_code , > reservation.per_phone_num , reservation.per_ext , > reservation.per_fax_num , reservation.per_email , > reservation.ldr_fname , reservation.ldr_lname , > reservation.ldr_position , reservation.ldr_country_code , > reservation.ldr_phone_num , reservation.ldr_ext , > reservation.ldr_fax_num , reservation.ldr_email , > reservation.confirm_fax_num , reservation.confirm_country_code , > reservation.confirm_email , reservation.confirm_format , > reservation.confirm_fmt , reservation.password , > reservation.setup_status , reservation.upper_ldr_lname , > reservation.date_added , reservation.progress_res , reservation.topic > , reservation.acct_specialist , reservation.schedule_ind , > reservation.bridge_conf_num , reservation.premium , > reservation.walk_thru_flag , reservation.cancel_date , > reservation.do_conf_only , reservation.template_id FROM informix.owner > owner, informix.account account, informix.company company, > informix.reservation reservation where ( company.bu_id = 1 OR > company.bu_id = 4) and ( account.company_number = > company.company_number ) and ( reservation.owner_number = > owner.owner_number ) and ( owner.account_number = > account.account_number ) and ( reservation.site_call_date > > '2015-06-01 00:00:00' ) > > Estimated Cost: 42464232 > Estimated # of Rows Returned: 12383288 > > 1) informix.company: INDEX PATH > > (1) Index Name: informix.idx_co_bu_co > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.company.bu_id = 1 > > (2) Index Name: informix.idx_co_bu_co > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > Lower Index Filter: informix.company.bu_id = 4 > > 2) informix.account: SEQUENTIAL SCAN > > DYNAMIC HASH JOIN (Build Outer) > > Dynamic Hash Filters: informix.account.company_number = > informix.company.company_number > > 3) informix.owner: INDEX PATH > > (1) Index Name: informix.idx_own_acct_own > > Index Keys: account_number owner_number (Key-Only) (Serial, fragments: > ALL) > > Lower Index Filter: informix.owner.account_number = > informix.account.account_number NESTED LOOP JOIN > > 4) informix.reservation: SEQUENTIAL SCAN > > Filters: > > Table Scan Filters: informix.reservation.site_call_date > > datetime(2015-06-01 > 00:00:00) year to second > > DYNAMIC HASH JOIN (Build Outer) > > Dynamic Hash Filters: informix.reservation.owner_number = > informix.owner.owner_number > > RSS QUERY PLAN > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:44:06) > ------ > SELECT reservation.res_id , reservation.conference_id ,> reservation.owner_number , reservation.site_id , reservation.series_id > , reservation.call_date , reservation.timezone_code , > reservation.reservation_status , reservation.placed_via , > reservation.employee_id , reservation.call_setup_mins , > reservation.site_call_date , reservation.bu_call_date , > reservation.duration , reservation.legs , reservation.call_type , > reservation.special_indicator , reservation.bridge_id , > reservation.team_id , reservation.pac_code_value , > reservation.per_fname , reservation.per_lname , > reservation.per_position , reservation.per_country_code , > reservation.per_phone_num , reservation.per_ext , > reservation.per_fax_num , reservation.per_email , > reservation.ldr_fname , reservation.ldr_lname , > reservation.ldr_position , reservation.ldr_country_code , > reservation.ldr_phone_num , reservation.ldr_ext , > reservation.ldr_fax_num , reservation.ldr_email , > reservation.confirm_fax_num , reservation.confirm_country_code , > reservation.confirm_email , reservation.confirm_format , > reservation.confirm_fmt , reservation.password , > reservation.setup_status , reservation.upper_ldr_lname , > reservation.date_added , reservation.progress_res , reservation.topic > , reservation.acct_specialist , reservation.schedule_ind , > reservation.bridge_conf_num , reservation.premium , > reservation.walk_thru_flag , reservation.cancel_date , > reservation.do_conf_only , reservation.template_id FROM informix.owner > owner, informix.account account, informix.company company, > informix.reservation reservation where ( company.bu_id = 1 OR > company.bu_id = 4) an

↪ replying to Mueller, Daniel D.

Art Kagel — Wed, 08 Jul 2015 15:33:04 — source: IIUG Forums & Mailing Lists

Run it on either. I assume that the row counts are the same on both servers. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 8, 2015 at 11:05 AM, Mueller, Daniel D. <ddmueller@intercall.com > wrote: > Art, > > Cannot answer the actual number of rows at this time. This was discovered > during some month end processing a few days ago and I was not involved. The > DBA on call switched them to run on the RSS vs the HDR and the query ran in > the expected time. I have not fully run the query, only with avoid_execute. > That is how I got the plans. > > The reservation.site_call_date IS indexed. It is a btree non-unique index. > We > run your dostats for the distribution weekly. I actually unloaded the rows > from sysdistrib for the tabid/colon and ran the results through diff. They > are > all identical. > > Would it be helpful to executw the entire query on both secondaries for the > row count? > > Thanx, > Dan > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Wednesday, July 08, 2015 10:22 AM > To: ids@iiug.org > Subject: Re: RSS/HDT Sec - Different Query Plans??? [35404] > > Dan: > > Both queries estimate 12,383,288 rows returned what is the actual count? > > I agree that the main issue is that the query plans are different and > perform > differently, but I suspect that neither is optimal. Are there > distributions on > the reservation.site_call_date column? Is it indexed? > > Art > > Art S. Kagel, President and Principal Consultant ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do those > opinions reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Wed, Jul 8, 2015 at 9:53 AM, DAN MUELLER <ddmueller@intercall.com> > wrote: > > > Forgive the long post but I have been asked for the 2 sqexplain outputs. > > Also, > > just because nothiung is easy, the 2 secondaries were switched from > > their origunal status. IE... What is now the NDR SEC was originally an > > RSS Sec and vice versa. Not sure if that plays into this or not. Also, > > it seems to be to be time to open a PMR. > > > > HDR QUERY PLAN > > > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05) > > ------ > > SELECT reservation.res_id , reservation.conference_id ,> > reservation.owner_number , reservation.site_id , reservation.series_id > > , reservation.call_date , reservation.timezone_code , > > reservation.reservation_status , reservation.placed_via , > > reservation.employee_id , reservation.call_setup_mins , > > reservation.site_call_date , reservation.bu_call_date , > > reservation.duration , reservation.legs , reservation.call_type , > > reservation.special_indicator , reservation.bridge_id , > > reservation.team_id , reservation.pac_code_value , > > reservation.per_fname , reservation.per_lname , > > reservation.per_position , reservation.per_country_code , > > reservation.per_phone_num , reservation.per_ext , > > reservation.per_fax_num , reservation.per_email , > > reservation.ldr_fname , reservation.ldr_lname , > > reservation.ldr_position , reservation.ldr_country_code , > > reservation.ldr_phone_num , reservation.ldr_ext , > > reservation.ldr_fax_num , reservation.ldr_email , > > reservation.confirm_fax_num , reservation.confirm_country_code , > > reservation.confirm_email , reservation.confirm_format , > > reservation.confirm_fmt , reservation.password , > > reservation.setup_status , reservation.upper_ldr_lname , > > reservation.date_added , reservation.progress_res , reservation.topic > > , reservation.acct_specialist , reservation.schedule_ind , > > reservation.bridge_conf_num , reservation.premium , > > reservation.walk_thru_flag , reservation.cancel_date , > > reservation.do_conf_only , reservation.template_id FROM informix.owner > > owner, informix.account account, informix.company company, > > informix.reservation reservation where ( company.bu_id = 1 OR > > company.bu_id = 4) and ( account.company_number = > > company.company_number ) and ( reservation.owner_number = > > owner.owner_number ) and ( owner.account_number = > > account.account_number ) and ( reservation.site_call_date > > > '2015-06-01 00:00:00' ) > > > > Estimated Cost: 42464232 > > Estimated # of Rows Returned: 12383288 > > > > 1) informix.company: INDEX PATH > > > > (1) Index Name: informix.idx_co_bu_co > > > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > > > Lower Index Filter: informix.company.bu_id = 1 > > > > (2) Index Name: informix.idx_co_bu_co > > > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > > > Lower Index Filter: informix.company.bu_id = 4 > > > > 2) informix.account: SEQUENTIAL SCAN > > > > DYNAMIC HASH JOIN (Build Outer) > > > > Dynamic Hash Filters: informix.account.company_number = > > informix.company.company_number > > > > 3) informix.owner: INDEX PATH > > > > (1) Index Name: informix.idx_own_acct_own > > > > Index Keys: account_number owner_number (Key-Only) (Serial, fragments: > > ALL) > > > > Lower Index Filter: informix.owner.account_number = > > informix.account.account_number NESTED LOOP JOIN > > > > 4) informix.reservation: SEQUENTIAL SCAN > > > > Filters: > > > > Table Scan Filters: informix.reservation.site_call_date > > > datetime(2015-06-01 > > 00:00:00) year to second > > > > DYNAMIC HASH JOIN (Build Outer) > > > > Dynamic Hash Filters: informix.reservation.owner_number = > > informix.owner.owner_number > > > > RSS QUERY PLAN > > > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:44:06) > > ------ > > SELECT reservation.res_id , reservation.conference_id ,> > reservation.owner_number , reservation.site_id , reservation.series_id > > , reservation.call_date , reservation.timezone_code , > > reservation.reservation_status , reservation.placed_via , > > reservation.employee_id , reservation.call_setup_mins , > > reservation.site_call_date , reservation.bu_call_date , > > reservation.duration , reservation.legs , reservation.call_type , > > reservation.special_indicator , reservation.bridge_id , > > reservation.team_id , reservation.pac_code_value , > > reservation.per_fname , reservation.per_lname , > > reservation.per_position , reservation.per_country_code

↪ replying to Art Kagel

Fernando Nunes — Wed, 08 Jul 2015 21:19:36 — source: IIUG Forums & Mailing Lists

For reference, now that I got a couple of minutes: http://www-01.ibm.com/support/docview.wss?uid=swg1IC73133 I think there was also one for the procedure cache. It would be interesting to see if the nrows column of systables is the same on both servers. Another scenario I saw once was that one create index was no properly replicated (the index was not usable on an RSS or HDR server). But from what I recall, in that case the query plan, or the query withing the query plan was shown twice... Because basically the engine attempted to use the index, and when it found it couldn't then it recalculated (silently) another query plan and executed it. Again, a PMR was opened and a bug created... but this was a bit more weird and I'd need to dive deeper to find it. In most situations, differences on parameters or session settings was the most common cause for the differences. Regards On Wed, Jul 8, 2015 at 4:33 PM, Art Kagel <art.kagel@gmail.com> wrote: > Run it on either. I assume that the row counts are the same on both > servers. > > Art > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > On Wed, Jul 8, 2015 at 11:05 AM, Mueller, Daniel D. < > ddmueller@intercall.com > > wrote: > > > Art, > > > > Cannot answer the actual number of rows at this time. This was discovered > > during some month end processing a few days ago and I was not involved. > The > > DBA on call switched them to run on the RSS vs the HDR and the query ran > in > > the expected time. I have not fully run the query, only with > avoid_execute. > > That is how I got the plans. > > > > The reservation.site_call_date IS indexed. It is a btree non-unique > index. > > We > > run your dostats for the distribution weekly. I actually unloaded the > rows > > from sysdistrib for the tabid/colon and ran the results through diff. > They > > are > > all identical. > > > > Would it be helpful to executw the entire query on both secondaries for > the > > row count? > > > > Thanx, > > Dan > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art > > Kagel > > Sent: Wednesday, July 08, 2015 10:22 AM > > To: ids@iiug.org > > Subject: Re: RSS/HDT Sec - Different Query Plans??? [35404] > > > > Dan: > > > > Both queries estimate 12,383,288 rows returned what is the actual count? > > > > I agree that the main issue is that the query plans are different and > > perform > > differently, but I suspect that neither is optimal. Are there > > distributions on > > the reservation.site_call_date column? Is it indexed? > > > > Art > > > > Art S. Kagel, President and Principal Consultant ASK Database Management > > www.askdbmgt.com > > > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and > > do not reflect on the IIUG, nor any other organization with which I am > > associated either explicitly, implicitly, or by inference. Neither do > those > > opinions reflect those of other individuals affiliated with any entity > with > > which I am affiliated nor those of the entities themselves. > > > > On Wed, Jul 8, 2015 at 9:53 AM, DAN MUELLER <ddmueller@intercall.com> > > wrote: > > > > > Forgive the long post but I have been asked for the 2 sqexplain > outputs. > > > Also, > > > just because nothiung is easy, the 2 secondaries were switched from > > > their origunal status. IE... What is now the NDR SEC was originally an > > > RSS Sec and vice versa. Not sure if that plays into this or not. Also, > > > it seems to be to be time to open a PMR. > > > > > > HDR QUERY PLAN > > > > > > QUERY: (OPTIMIZATION TIMESTAMP: 07-06-2015 13:51:05) > > > ------ > > > SELECT reservation.res_id , reservation.conference_id ,> > > reservation.owner_number , reservation.site_id , reservation.series_id > > > , reservation.call_date , reservation.timezone_code , > > > reservation.reservation_status , reservation.placed_via , > > > reservation.employee_id , reservation.call_setup_mins , > > > reservation.site_call_date , reservation.bu_call_date , > > > reservation.duration , reservation.legs , reservation.call_type , > > > reservation.special_indicator , reservation.bridge_id , > > > reservation.team_id , reservation.pac_code_value , > > > reservation.per_fname , reservation.per_lname , > > > reservation.per_position , reservation.per_country_code , > > > reservation.per_phone_num , reservation.per_ext , > > > reservation.per_fax_num , reservation.per_email , > > > reservation.ldr_fname , reservation.ldr_lname , > > > reservation.ldr_position , reservation.ldr_country_code , > > > reservation.ldr_phone_num , reservation.ldr_ext , > > > reservation.ldr_fax_num , reservation.ldr_email , > > > reservation.confirm_fax_num , reservation.confirm_country_code , > > > reservation.confirm_email , reservation.confirm_format , > > > reservation.confirm_fmt , reservation.password , > > > reservation.setup_status , reservation.upper_ldr_lname , > > > reservation.date_added , reservation.progress_res , reservation.topic > > > , reservation.acct_specialist , reservation.schedule_ind , > > > reservation.bridge_conf_num , reservation.premium , > > > reservation.walk_thru_flag , reservation.cancel_date , > > > reservation.do_conf_only , reservation.template_id FROM informix.owner > > > owner, informix.account account, informix.company company, > > > informix.reservation reservation where ( company.bu_id = 1 OR > > > company.bu_id = 4) and ( account.company_number = > > > company.company_number ) and ( reservation.owner_number = > > > owner.owner_number ) and ( owner.account_number = > > > account.account_number ) and ( reservation.site_call_date > > > > '2015-06-01 00:00:00' ) > > > > > > Estimated Cost: 42464232 > > > Estimated # of Rows Returned: 12383288 > > > > > > 1) informix.company: INDEX PATH > > > > > > (1) Index Name: informix.idx_co_bu_co > > > > > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > > > > > Lower Index Filter: informix.company.bu_id = 1 > > > > > > (2) Index Name: informix.idx_co_bu_co > > > > > > Index Keys: bu_id company_number (Key-Only) (Serial, fragments: ALL) > > > > > > Lower Index Filter: informix.company.bu_id = 4 > > > > > > 2) informix.account: SEQUENTIAL SCAN > > > > > > DYNAMIC HASH JOIN (Build Outer) > > > > > > Dynamic Hash Filters: informix.account.company_number = > > > informix.company.company_number > > > > > > 3) informix.owner: INDEX PATH > > > > > > (1) Index Name: informix.idx_own_acct_own > > > > > >

↪ replying to DAN MUELLER

BENJAMIN THOMPSON — Thu, 09 Jul 2015 11:23:59 — source: IIUG Forums & Mailing Lists

I would be most concerned by the RSS plan as it has higher costs than the HDR one and you would therefore expect it not to be the one chosen. Can you force the HDR plan on the RSS machine by using hints like +ORDERED or similar? If you can this would at least show that all the indices needed for it are available. Neither plan uses the index on reservation.site_call_date which you say is indexed. Art has already pointed this out, however. With OPTCOMPIND=0 I would expect it to be used regardless of the state of the distribution on the column but with 1 (and not with repeatable read isolation) or 2, it will be purely costs based. Unless you don't have much reservation history I think it would be advantageous to use this. Indexing account.company_number would help here too. Having said all this I suspect the different plans may be a bug.

↪ replying to BENJAMIN THOMPSON

Art Kagel — Thu, 09 Jul 2015 13:38:59 — source: IIUG Forums & Mailing Lists

One thing you could try is to adjust the OPT_SEEK_FACTOR parameter in the ONCONFIG file. This is an undocumented parameter that controls the new (in 11.70+) cost factor applied to index accesses when the costs are calculated. The range is zero to 25 and the default is 6. Before this change the equivalent factor was zero. So, you might try setting this to zero. OPT_SEEK_FACTOR is supported by onmode -wm/wf so you can make the change dynamically. I know that this has nothing to do with the different query plans between the different servers which I think is more due to stale distributions in memory on one server or the other as Fernando noted, but I also don't like the fact that both query plans are using hash joins and this is one way to adjust that behavior. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jul 9, 2015 at 7:24 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > I would be most concerned by the RSS plan as it has higher costs than the > HDR > one and you would therefore expect it not to be the one chosen. > > Can you force the HDR plan on the RSS machine by using hints like +ORDERED > or > similar? If you can this would at least show that all the indices needed > for > it are available. > > Neither plan uses the index on reservation.site_call_date which you say is > indexed. Art has already pointed this out, however. With OPTCOMPIND=0 I > would > expect it to be used regardless of the state of the distribution on the > column > but with 1 (and not with repeatable read isolation) or 2, it will be purely > costs based. Unless you don't have much reservation history I think it > would > be advantageous to use this. > > Indexing account.company_number would help here too. > > Having said all this I suspect the different plans may be a bug. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01183c928944d8051a716008

↪ replying to Art Kagel

Mueller, Daniel D. — Thu, 09 Jul 2015 14:06:32 — source: IIUG Forums & Mailing Lists

I appreciate all of the replies from everyone but my thought is that both servers were bounced during the swap process to resolve dbserveralias differences. That was 1.5 weeks ago. That would have emptied all buffers. They should not act differently should they? As for optimizer hints, why should that be be necessary if the servers are replicated from the same primary? To add to this, I am noticing a lot more temp dbspaces usage so I suspect there are different query plans in other places as well. I have compared onconfigs again and see nothing that would make a difference. I have tested outside the app so no client side diffs there. I have thought about whacking the hdr sec and restoring it again but I have no proof this will do any good and it is a big todo here. I cannot go on a guess there. I think something else is wrong. I have opened a PMR so IBM is looking at it. Thanx again, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, July 09, 2015 9:39 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35420] One thing you could try is to adjust the OPT_SEEK_FACTOR parameter in the ONCONFIG file. This is an undocumented parameter that controls the new (in 11.70+) cost factor applied to index accesses when the costs are calculated. The range is zero to 25 and the default is 6. Before this change the equivalent factor was zero. So, you might try setting this to zero. OPT_SEEK_FACTOR is supported by onmode -wm/wf so you can make the change dynamically. I know that this has nothing to do with the different query plans between the different servers which I think is more due to stale distributions in memory on one server or the other as Fernando noted, but I also don't like the fact that both query plans are using hash joins and this is one way to adjust that behavior. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jul 9, 2015 at 7:24 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > I would be most concerned by the RSS plan as it has higher costs than > the HDR one and you would therefore expect it not to be the one > chosen. > > Can you force the HDR plan on the RSS machine by using hints like > +ORDERED or similar? If you can this would at least show that all the > indices needed for it are available. > > Neither plan uses the index on reservation.site_call_date which you > say is indexed. Art has already pointed this out, however. With > OPTCOMPIND=0 I would expect it to be used regardless of the state of > the distribution on the column but with 1 (and not with repeatable > read isolation) or 2, it will be purely costs based. Unless you don't > have much reservation history I think it would be advantageous to use > this. > > Indexing account.company_number would help here too. > > Having said all this I suspect the different plans may be a bug. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01183c928944d8051a716008 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mueller, Daniel D.

Madison Pruet — Thu, 09 Jul 2015 14:24:17 — source: IIUG Forums & Mailing Lists

I suspect that the dictionary cache on the secondary is not being invalidated as the update stats are being performed and am guessing that you are doing automatic update stats. The secondary has to know what needs to be invalidated as the statistics are replicated. If we don't know that they have been updated, then the old dictionary cache will remain intact. I'd look at it, but then again, I'm a bit of a short-timer now. M.P. From: "Mueller, Daniel D." <ddmueller@intercall.com> To: ids@iiug.org Date: 07/09/2015 09:07 AM Subject: RE: RSS/HDT Sec - Different Query Plans??? [35424] Sent by: ids-bounces@iiug.org I appreciate all of the replies from everyone but my thought is that both servers were bounced during the swap process to resolve dbserveralias differences. That was 1.5 weeks ago. That would have emptied all buffers. They should not act differently should they? As for optimizer hints, why should that be be necessary if the servers are replicated from the same primary? To add to this, I am noticing a lot more temp dbspaces usage so I suspect there are different query plans in other places as well. I have compared onconfigs again and see nothing that would make a difference. I have tested outside the app so no client side diffs there. I have thought about whacking the hdr sec and restoring it again but I have no proof this will do any good and it is a big todo here. I cannot go on a guess there. I think something else is wrong. I have opened a PMR so IBM is looking at it. Thanx again, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, July 09, 2015 9:39 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35420] One thing you could try is to adjust the OPT=5FSEEK=5FFACTOR parameter in t= he ONCONFIG file. This is an undocumented parameter that controls the new (in 11.70+) cost factor applied to index accesses when the costs are calculated. The range is zero to 25 and the default is 6. Before this change the equivalent factor was zero. So, you might try setting this to zero. OPT=5FSEEK=5FFACTOR is supported by onmode -wm/wf so you can make the change dynamically. I know that this has nothing to do with the different query plans between the different servers which I think is more due to stale distributions in memory on one server or the other as Fernando noted, but I also don't like the fact that both query plans are using hash joins and this is one way to adjust that behavior. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jul 9, 2015 at 7:24 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > I would be most concerned by the RSS plan as it has higher costs than > the HDR one and you would therefore expect it not to be the one > chosen. > > Can you force the HDR plan on the RSS machine by using hints like > +ORDERED or similar? If you can this would at least show that all the > indices needed for it are available. > > Neither plan uses the index on reservation.site=5Fcall=5Fdate which you > say is indexed. Art has already pointed this out, however. With > OPTCOMPIND=3D0 I would expect it to be used regardless of the state of > the distribution on the column but with 1 (and not with repeatable > read isolation) or 2, it will be purely costs based. Unless you don't > have much reservation history I think it would be advantageous to use > this. > > Indexing account.company=5Fnumber would help here too. > > Having said all this I suspect the different plans may be a bug. > > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01183c928944d8051a716008 ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum. ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Madison Pruet

Mueller, Daniel D. — Thu, 09 Jul 2015 15:16:17 — source: IIUG Forums & Mailing Lists

Thanx Short timer, I am going to forward your comments to the PMR owner. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Thursday, July 09, 2015 10:24 AM To: ids@iiug.org Subject: RE: RSS/HDT Sec - Different Query Plans??? [35425] I suspect that the dictionary cache on the secondary is not being invalidated as the update stats are being performed and am guessing that you are doing automatic update stats. The secondary has to know what needs to be invalidated as the statistics are replicated. If we don't know that they have been updated, then the old dictionary cache will remain intact. I'd look at it, but then again, I'm a bit of a short-timer now. M.P. From: "Mueller, Daniel D." <ddmueller@intercall.com> To: ids@iiug.org Date: 07/09/2015 09:07 AM Subject: RE: RSS/HDT Sec - Different Query Plans??? [35424] Sent by: ids-bounces@iiug.org I appreciate all of the replies from everyone but my thought is that both servers were bounced during the swap process to resolve dbserveralias differences. That was 1.5 weeks ago. That would have emptied all buffers. They should not act differently should they? As for optimizer hints, why should that be be necessary if the servers are replicated from the same primary? To add to this, I am noticing a lot more temp dbspaces usage so I suspect there are different query plans in other places as well. I have compared onconfigs again and see nothing that would make a difference. I have tested outside the app so no client side diffs there. I have thought about whacking the hdr sec and restoring it again but I have no proof this will do any good and it is a big todo here. I cannot go on a guess there. I think something else is wrong. I have opened a PMR so IBM is looking at it. Thanx again, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, July 09, 2015 9:39 AM To: ids@iiug.org Subject: Re: RSS/HDT Sec - Different Query Plans??? [35420] One thing you could try is to adjust the OPT=5FSEEK=5FFACTOR parameter in t= he ONCONFIG file. This is an undocumented parameter that controls the new (in 11.70+) cost factor applied to index accesses when the costs are calculated. The range is zero to 25 and the default is 6. Before this change the equivalent factor was zero. So, you might try setting this to zero. OPT=5FSEEK=5FFACTOR is supported by onmode -wm/wf so you can make the change dynamically. I know that this has nothing to do with the different query plans between the different servers which I think is more due to stale distributions in memory on one server or the other as Fernando noted, but I also don't like the fact that both query plans are using hash joins and this is one way to adjust that behavior. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Jul 9, 2015 at 7:24 AM, BENJAMIN THOMPSON < benjamin.thompson@bskyb.com> wrote: > I would be most concerned by the RSS plan as it has higher costs than > the HDR one and you would therefore expect it not to be the one > chosen. > > Can you force the HDR plan on the RSS machine by using hints like > +ORDERED or similar? If you can this would at least show that all the > indices needed for it are available. > > Neither plan uses the index on reservation.site=5Fcall=5Fdate which > you say is indexed. Art has already pointed this out, however. With > OPTCOMPIND=3D0 I would expect it to be used regardless of the state of > the distribution on the column but with 1 (and not with repeatable > read isolation) or 2, it will be purely costs based. Unless you don't > have much reservation history I think it would be advantageous to use > this. > > Indexing account.company=5Fnumber would help here too. > > Having said all this I suspect the different plans may be a bug. > > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01183c928944d8051a716008 ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum. ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mueller, Daniel D.

BENJAMIN — Fri, 10 Jul 2015 09:06:52 — source: IIUG Forums & Mailing Lists

Hi Dan, Optimiser hints should not be necessary but the reason is to show that the same plan is possible and also see what costs are assigned to it. This would tell you whether the problem is with the optimiser or the information available to it, or there is a problem with one of the indices. To do this in an ad-hoc session would not take too long. As you've now raised a PMR I will leave it to IBM support but I would be interested to hear what the eventual resolution is. Ben.

↪ replying to DAN MUELLER

DAN MUELLER — Tue, 18 Aug 2015 14:35:05 — source: IIUG Forums & Mailing Lists

This took long enough but we have resolved this issue. DS_TOTAL_MEMORY was set WAY higher on the system with the poor query plan. We were not using PDQ but it affected this query anyway. IBM is putting a defect together to address the issue. thanx for all the replies, dan

↪ replying to DAN MUELLER

Art Kagel — Tue, 18 Aug 2015 14:43:35 — source: IIUG Forums & Mailing Lists

Very interesting! When you get the defect number from IBM, please post it. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 18, 2015 at 9:35 AM, DAN MUELLER <ddmueller@intercall.com> wrote: > This took long enough but we have resolved this issue. DS_TOTAL_MEMORY was > set > WAY higher on the system with the poor query plan. We were not using PDQ > but > it affected this query anyway. IBM is putting a defect together to address > the > issue. > > thanx for all the replies, > dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e013c66143aefd0051d96f1f4

RSS/HDT Sec - Different Query Plans???

Glossary

Your privacy choices