IDS 12.1- Query cost High

Hi, We now plan to migration our IDS from 11.10 to 12.10 on HPUX platform. I try to run SQL syntax to estimated cost using set explain to determine performance will be increase at latest IDS but i surprise, it's not what i think, take longer time to execution at new IDS. 1. Result at ids 11.10 QUERY: (OPTIMIZATION TIMESTAMP: 04-07-2016 14:03:46) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 1344 Estimated # of Rows Returned: 903 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N OT NULL ) (1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acc_pay type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 903 829946 00:00:39 1345 2. result at ids 12.60 QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:17) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 2162855 Estimated # of Rows Returned: 246479 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn _cd IS NOT NULL ) (1) Index Name: informix.i_acc_pay @ "acc_pay.txt" 78 lines, 3018 characters QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:17) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 2162855 Estimated # of Rows Returned: 246479 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N OT NULL ) (1) Index Name: informix.i_acc_pay Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acc_pay type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 246479 827409 05:38.52 2162855 QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:50) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 2162855 Estimated # of Rows Returned: 246479 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N OT NULL ) (1) Index Name: informix.i_acc_pay Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acc_pay type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 246479 827409 05:34.13 2162855 Any parameter i need to configure at IDS 12.10. How to improve performance query at ids 12.10? Please advice. Thank You

Mohd: You cannot use the cost figures to determine whether the query is running better in one version of IDS versus another. The costs are calculated differently in version 11.70 and later than they were in versions 11.50 and earlier. One of several changes is that there is now a cost associated with using an index which was never included in the cost values before. In general you cannot use the costs to compare two versions of the same query or performance between two different platforms or engine releases and with the changes in cost calculations this is now more true than ever. The only proper measure of whether the query is running better or worse from one version to another is run time. In your case the query is running in 5:38 under v11.10 and in 5:34 under v12.10 which is slightly faster. The difference which is about 1.2% may not be significant at all. I would run the queries several times both with an empty cache (so right after a restart) and after having run it once already to prime the cache to see how it will perform. BTW, the change in cost calculations will sometimes cause the optimizer to choose a different query path than it did under v11.10. Sometimes this is a better path, sometimes it is not. The symptom is that some queries queries that used to use an index may now use a sequential scan and be slower. There is an undocumented ONCONFIG parameter, OPT_SEEK_FACTOR, which controls the value used for calculating the cost of index reads. This has a range from 0 to 25 and defaults to 6. If you have this problem, you can set it to zero (0) which will return the costing calculation to something closer to what v11.10 used and will usually change the query paths back to those used by v11.10. 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, Apr 7, 2016 at 4:52 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > We now plan to migration our IDS from 11.10 to 12.10 on HPUX platform. > > I try to run SQL syntax to estimated cost using set explain to determine > performance will be increase at latest IDS but i surprise, it's not what i > think, take longer time to execution at new IDS. > > 1. Result at ids 11.10 > > QUERY: (OPTIMIZATION TIMESTAMP: 04-07-2016 14:03:46) > ------ > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), > rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no > from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 > and ref_no ="T00001" > and ref_typ = "AGT" > and ( trn_cd is not null and trn_cd != " ") > and pay_dt <="31/03/2016" > order by pay_dt> > Estimated Cost: 1344 > Estimated # of Rows Returned: 903 > > 1) informix.acc_pay: INDEX PATH > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - > informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > informix.acc_pay.trn_cd IS N > OT NULL ) > > (1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ > bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, > fragments: ALL) > > Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND > informix.acc_pay.ref_typ = 'AGT' ) > > Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 > > Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 acc_pay > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 903 829946 00:00:39 1345 > > 2. result at ids 12.60 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:17) > ------ > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), > rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no > from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 > and ref_no ="T00001" > and ref_typ = "AGT" > and ( trn_cd is not null and trn_cd != " ") > and pay_dt <="31/03/2016" > order by pay_dt> > Estimated Cost: 2162855 > Estimated # of Rows Returned: 246479 > > 1) informix.acc_pay: INDEX PATH > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info > rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > informix.acc_pay.trn > _cd IS NOT NULL ) > > (1) Index Name: informix.i_acc_pay > @ > "acc_pay.txt" 78 lines, 3018 characters > > QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:17) > ------ > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), > rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no > from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 > and ref_no ="T00001" > and ref_typ = "AGT" > and ( trn_cd is not null and trn_cd != " ") > and pay_dt <="31/03/2016" > order by pay_dt> > Estimated Cost: 2162855 > Estimated # of Rows Returned: 246479 > > 1) informix.acc_pay: INDEX PATH > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - > informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > informix.acc_pay.trn_cd IS N > OT NULL ) > > (1) Index Name: informix.i_acc_pay > > Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ > bas_doc_no > clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) > > Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND > informix.acc_pay.ref_typ = 'AGT' ) > > Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 > > Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 acc_pay > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 246479 827409 05:38.52 2162855 > > QUERY: (OPTIMIZATION TIMESTAMP: 03-31-2016 13:29:50) > ------ > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), > rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no > from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 > and ref_no ="T00001" > and ref_typ = "AGT" > and ( trn_cd is not null and trn_cd != " ") > and pay_dt <="31/03/2016" > order by pay_dt> > Estimated Cost: 2162855 > Estimated # of Row

Hi, Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 return 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, but not exists. If we want add this parameter, what best value we can put. Do you have any related document about this parameter configuration can share we us? Thank You

Is this on the same hardware, same configuration (memory etc.) and disks? Are you able to reproduce these on instances without any other activity? Given that the query plans are equal, this difference in time must be an external factor like disk access, instance or hardware configuration.... Regards. On Thu, Apr 7, 2016 at 12:04 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 > return > 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. > > I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, > but > not exists. If we want add this parameter, what best value we can put. Do > you > have any related document about this parameter configuration can share we > us? > > Thank You > > > > ******************************************************************************* > 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... --047d7bdca44c0c9a01052fe36e84

OUCH! Sorry about that. I completely misread your explain output. I was comparing the run times for two of the v12.10 runs. Confusing output, but my fault, you labeled the runs, I just missed it. OK, I see the following: - The two query paths are the same, so OPT_SEEK_FACTOR is not the problem. - The costs ARE different and that is because of the differences in the engines' costing algorithms and other factors. However, it didn't affect the query plan. - Version 11.10 estimates returning 903 rows while v12.10 estimates returning 246,479 rows. That indicates that the data distributions in the v12.10 are way off. Questions: 1. Is it possible that the table is far more fragmented in the v12.10 instance than it was in the v11.10 instance? 2. Was this an in-place upgrade or a new instance? I suspect a new instance. If so then how did you load the data? 3. Does the table have any LVARCHAR or VARCHAR columns that are typically not full or that in the original instance may have had their contents expanded after the rows were inserted? 4. Is there a difference in the partitioning of the table in the two instances? 5. Is the storage used by both instances comparable (ie both the same RAID level, same number of spindles or types of drives, same stripe block size, etc.)? 6. If the upgrade was in-place did you drop all data distributions then recreate them from scratch after the upgrade? Post the results of this query from both instances: select * from systabnames st, systabinfo sti where st.partnum = sti.partnum and st.tabname = 'acc_pay' and st.dbsname = <your database>; Also, try running my dostats utility (or manually run a complete set of update statistics LOW, MEDIUM, and HIGH commands for the table asrecommended in the Performance Guide manual) on the v12.10 database and test run the query again after that. 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, Apr 7, 2016 at 7:04 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 > return > 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. > > I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, > but > not exists. If we want add this parameter, what best value we can put. Do > you > have any related document about this parameter configuration can share we > us? > > Thank You > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bea43fca1ecdd052fe3c953

UPDATE STATISTICS > On 7 Apr 2016, at 12:04, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > > Hi, > > Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 return > 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. > > I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, but > not exists. If we want add this parameter, what best value we can put. Do you > have any related document about this parameter configuration can share we us? > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

It's kind of hard to work out in the original post, but it looks like the same query plan is being used for both versions. Let me know if that's not the case. Don't worry about the different estimated costs - as Art said that can be misleading if using that to compare across engines. Do you get the same results if you repeat the test? Could it be better caching on the 11 engine, vs the 12? Do you have similar sized bufferpools on both versions? Are you using equivalent disks on both of these servers? Is there activity on the 12.10 server that may be causing locks, or consuming resources? Mike -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MOHD FADZIL JUSOH Sent: Thursday, April 07, 2016 5:05 AM To: ids@iiug.org Subject: Re: IDS 12.1- Query cost High [36915] Hi, Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 return 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, but not exists. If we want add this parameter, what best value we can put. Do you have any related document about this parameter configuration can share we us? Thank You **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

You're sounding like Obnoxio.... ;) The query plans are the same, so the statistics are a bit irrelevant. The same query plan should not have such a huge difference unless some "external" factor is involved like: - Disk access times - Physical table layout (if one is "loaded" in a way that resembles a clustered index for example) - Memory cache - Disk read ahead - CPU power - ??? Regards. On Thu, Apr 7, 2016 at 1:04 PM, Spokey Wheeler <spokey.wheeler@gmail.com> wrote: > UPDATE STATISTICS> > > On 7 Apr 2016, at 12:04, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > > > > Hi, > > > > Based on our query cost ids 11.10 return 00:00:39 second and ids 12.10 > return > > 05:34.13 second. A lot of different running on ids 11.10 and ids 12.10. > > > > I try found this parameter "OPT_SEEK_FACTOR" on onconfig file ids 12.10, > but > > not exists. If we want add this parameter, what best value we can put. Do > you > > have any related document about this parameter configuration can share we > us? > > > > Thank You > > > > > > > > ******************************************************************************* > > 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... --001a1141bb98107875052fe5aa6d

Hi, same query plan is being used for both versions, yes. Don't worry about the different estimated costs - as Art said that can be misleading if using that to compare across engines.- I worry becouse this syntax make our operation take long time to complete/produce report. Before this only take 2 hour (running on ids 11.10) but after try run on ids 12, finish after 6 hour. Do you get the same results if you repeat the test? - yes, i repeat query after drop & create again index (on ids 12.10), do update statistic low,medium & high. Could it be better caching on the 11 engine, vs the 12?-i try run on ids 12.10 for first time. Do you have similar sized bufferpools on both versions? yes. Are you using equivalent disks on both of these servers? - disk for ids 12.10 better that disk for ids 11.10. Is there activity on the 12.10 server that may be causing locks, or consuming resources? - no, only this process run on new ids 12.10. I try to point back our problem, same query return higher query cost on ids 12.10, if u see my first posting, on ids 11.10 this query successfully running using index patch, but on ids 12.10 it running using sequence scan, why it return different result, any wrong with my exiting index? Any parameter config on ids 12.10 need to enable to allow systax using index. Thank you

Are you using raw disk in 11.10 and cooked files in 12.10? Are you perhaps using a journaled filesystem in 12.10? > On 7 Apr 2016, at 15:46, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > > Hi, > > same query plan is being used for both versions, yes. > > Don't worry about the different estimated costs - as Art said > that can be misleading if using that to compare across engines.- I worry > becouse this syntax make our operation take long time to complete/produce > report. Before this only take 2 hour (running on ids 11.10) but after try run > on ids 12, finish after 6 hour. > > Do you get the same results if you repeat the test? - yes, i repeat query > after drop & create again index (on ids 12.10), do update statistic low,medium > & high. > > Could it be better caching on the 11 engine, vs the 12?-i try run on ids 12.10 > for first time. > Do you have similar sized bufferpools on both versions? yes. > > Are you using equivalent disks on both of these servers? - disk for ids 12.10 > better that disk for ids 11.10. > > Is there activity on the 12.10 server that may be causing locks, or consuming > resources? - no, only this process run on new ids 12.10. > > I try to point back our problem, same query return higher query cost on ids > 12.10, if u see my first posting, on ids 11.10 this query successfully running > using index patch, but on ids 12.10 it running using sequence scan, why it > return different result, any wrong with my exiting index? Any parameter config > on ids 12.10 need to enable to allow systax using index. > > Thank you > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, That indicates that the data distributions in the v12.10 are way off, What do you means "way off"? need to change configuration to on this? Questions: 1.Was this an in-place upgrade or a new instance? I suspect a new instance. If so then how did you load the data? - I do in-place migration. At new server, i install ids version 11.10 first and than restore data using ontape command. After complete restored, i install ids 12.10 and bring up the instance using ids 12.10. After that before test application, we run updates statistics low, medium & high for all table. Any step i miss during migration? 2.. Is there a difference in the partitioning of the table in the two instances? - all table at same instance 3. Is the storage used by both instances comparable (ie both the same RAID level, same number of spindles or types of drives, same stripe block size, etc.)? A. ids 12.10 using better disk/storage compare with ids 11.10 4. If the upgrade was in-place did you drop all data distributions then recreate them from scratch after the upgrade? - Yes, we do in-place upgrade, how to drop all data distributions and recreate again? We need to do this? I try to point back my problem, same query return higher query cost on ids 12.10, if u see my first posting, on ids 11.10 this query successfully running using index patch, but on ids 12.10 it running using sequence scan, why it return different result, any wrong with my exiting index? Any parameter config on ids 12.10 need to enable to allow syntax using index. Here i share my table structure : $ dbschema -db life -t acc_pay DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC6AEE { TABLE "informix".acc_pay row size = 118 number of columns = 22 index size = 201 } create table "informix".acc_pay ( ref_typ char(3) not null , ref_no char(13) not null , pay_dt date not null , pay_typ char(3) not null , pay_chg_typ char(3) not null , trn_cd char(2), doc_series char(2), yymm char(4), doc_no integer, prio_no smallint, pay_amt decimal(12,2), pay_int_amt decimal(12,2), paid_amt decimal(12,2), adj_amt decimal(12,2), rel_amt decimal(12,2), bnk_cd char(4), bas_doc_typ char(3), bas_doc_no char(14), clt_cd char(8), surr_key char(8), lst_int_dt date, versn smallint ); revoke all on "informix".acc_pay from "public" as "informix"; create index "stjf".i05_acc_pay on "informix".acc_pay (ref_typ, trn_cd,pay_dt) using btree ; create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) using btree ; create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) using btree ; create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, bas_doc_no,pay_typ,pay_dt) using btree ; create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, yymm,doc_no) using btree ; create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, pay_typ,pay_chg_typ,bas_doc_no) using btree ; create unique index "informix".i_acc_pay on "informix".acc_pay (ref_typ,ref_no,pay_dt,pay_typ,pay_chg_typ,bas_doc_typ,bas_doc_no, clt_cd,trn_cd,doc_series,yymm,doc_no) using btree ; create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) using btree ; And my syntax as below: select ref_no, pay_dt, surr_key, trn_cd, doc_series, yymm, doc_no, bas_doc_no, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid fromacc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no = "T00001" and ref_typ = "AGT" and trn_cd is not null and trn_cd != " " and pay_dt <="31/03/2016" order by pay_dt On ids 11.10, look i success run using index path as below: 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N OT NULL ) (1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) But when we run it at ids 12.10 after i drop & recreate again index, it give us that syntax not running using index path , but running using sequence scan as below: 1) informix.acc_pay: SEQUENTIAL SCAN Filters: (((((informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_ amt > 0.00 ) AND informix.acc_pay.ref_typ = 'AGT' ) AND informix.acc_pay.trn_cd != ' ' ) AND informix.acc_pay.trn_cd IS NOT NULL ) AND informix.acc_pay.pay_dt <= 31/03/ 2016 ) What happen to current index? Based on my syntax, can u advice the right index we should created for this table? Thank You

I'm sorry, but the query plan on 12.10 shows index path also... Please clarify this. If the query plasn are different that's a hole different issue. And we would need the schema of the table.... any of the indexed columns is a VARCHAR? On Thu, Apr 7, 2016 at 3:46 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > same query plan is being used for both versions, yes. > > Don't worry about the different estimated costs - as Art said > that can be misleading if using that to compare across engines.- I worry > becouse this syntax make our operation take long time to complete/produce > report. Before this only take 2 hour (running on ids 11.10) but after try > run > on ids 12, finish after 6 hour. > > Do you get the same results if you repeat the test? - yes, i repeat query > after drop & create again index (on ids 12.10), do update statistic > low,medium > & high. > > Could it be better caching on the 11 engine, vs the 12?-i try run on ids > 12.10 > for first time. > Do you have similar sized bufferpools on both versions? yes. > > Are you using equivalent disks on both of these servers? - disk for ids > 12.10 > better that disk for ids 11.10. > > Is there activity on the 12.10 server that may be causing locks, or > consuming > resources? - no, only this process run on new ids 12.10. > > I try to point back our problem, same query return higher query cost on ids > 12.10, if u see my first posting, on ids 11.10 this query successfully > running > using index patch, but on ids 12.10 it running using sequence scan, why it > return different result, any wrong with my exiting index? Any parameter > config > on ids 12.10 need to enable to allow systax using index. > > Thank you > > > > ******************************************************************************* > 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... --047d7bdca44c684027052fe67c89

I'm confused - you said the same query plan is being used, yet then you say that it's using an index on 11.10, but a sequential scan on 12.10. Which is it? For the estimated costs, I'm saying don't read too much into those numbers. That is really only useful when comparing different query plans on the SAME version of the server. Best to focus on the query plan shown and real world timings (which I understand are slower on the new version). About the repeated tests - have you run it twice in a row, without restarting the engine in-between, and without changes to the index? Could be that the pages are nicely cached on 11.10, but they haven't had a chance yet to cache on 12.10. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MOHD FADZIL JUSOH Sent: Thursday, April 07, 2016 8:47 AM To: ids@iiug.org Subject: Re: RE: IDS 12.1- Query cost High [36921] Hi, same query plan is being used for both versions, yes. Don't worry about the different estimated costs - as Art said that can be misleading if using that to compare across engines.- I worry becouse this syntax make our operation take long time to complete/produce report. Before this only take 2 hour (running on ids 11.10) but after try run on ids 12, finish after 6 hour. Do you get the same results if you repeat the test? - yes, i repeat query after drop & create again index (on ids 12.10), do update statistic low,medium & high. Could it be better caching on the 11 engine, vs the 12?-i try run on ids 12.10 for first time. Do you have similar sized bufferpools on both versions? yes. Are you using equivalent disks on both of these servers? - disk for ids 12.10 better that disk for ids 11.10. Is there activity on the 12.10 server that may be causing locks, or consuming resources? - no, only this process run on new ids 12.10. I try to point back our problem, same query return higher query cost on ids 12.10, if u see my first posting, on ids 11.10 this query successfully running using index patch, but on ids 12.10 it running using sequence scan, why it return different result, any wrong with my exiting index? Any parameter config on ids 12.10 need to enable to allow systax using index. Thank you **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

When you did the first update statistics, was it with the drop distributions option? I'm pretty sure that you need to do that after an in place upgrade. --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > MOHD FADZIL JUSOH > Sent: Thursday, April 07, 2016 10:08 AM > To: ids@iiug.org > Subject: Re: IDS 12.1- Query cost High [36923] > > Hi, > > That indicates that the data distributions in the v12.10 are way off, > What do you means "way off"? need to change configuration to on this? > > Questions: > > 1.Was this an in-place upgrade or a new instance? I suspect a new > > instance. If so then how did you load the data? > > - I do in-place migration. At new server, i install ids version 11.10 > first and than restore data using ontape command. After complete > restored, i install ids 12.10 and bring up the instance using ids > 12.10. After that before test application, we run updates statistics > low, medium & high for all table. > > Any step i miss during migration? > > 2.. Is there a difference in the partitioning of the table in the two > > instances? - all table at same instance > > 3. Is the storage used by both instances comparable (ie both the same > > RAID level, same number of spindles or types of drives, same stripe > block > > size, etc.)? > > A. ids 12.10 using better disk/storage compare with ids 11.10 > > 4. If the upgrade was in-place did you drop all data distributions then > > recreate them from scratch after the upgrade? > - Yes, we do in-place upgrade, how to drop all data distributions and > recreate again? We need to do this? > > I try to point back my problem, same query return higher query cost on > ids 12.10, if u see my first posting, on ids 11.10 this query > successfully running using index patch, but on ids 12.10 it running > using sequence scan, why it return different result, any wrong with my > exiting index? Any parameter config on ids 12.10 need to enable to > allow syntax using index. > > Here i share my table structure : > > $ dbschema -db life -t acc_pay> > DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC6AEE > > { TABLE "informix".acc_pay row size = 118 number of columns = 22 index > size = > 201 } > > create table "informix".acc_pay > ( > > ref_typ char(3) not null , > > ref_no char(13) not null , > > pay_dt date not null , > > pay_typ char(3) not null , > > pay_chg_typ char(3) not null , > > trn_cd char(2), > > doc_series char(2), > > yymm char(4), > > doc_no integer, > > prio_no smallint, > > pay_amt decimal(12,2), > > pay_int_amt decimal(12,2), > > paid_amt decimal(12,2), > > adj_amt decimal(12,2), > > rel_amt decimal(12,2), > > bnk_cd char(4), > > bas_doc_typ char(3), > > bas_doc_no char(14), > > clt_cd char(8), > > surr_key char(8), > > lst_int_dt date, > > versn smallint > ); > > revoke all on "informix".acc_pay from "public" as "informix";> > create index "stjf".i05_acc_pay on "informix".acc_pay (ref_typ, > > trn_cd,pay_dt) using btree ; > create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) > > using btree ; > create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) > > using btree ; > create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, > > bas_doc_no,pay_typ,pay_dt) using btree ; create index "stjf".i4_acc_pay > on "informix".acc_pay (trn_cd,doc_series, > > yymm,doc_no) using btree ; > create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, > > pay_typ,pay_chg_typ,bas_doc_no) using btree ; create unique index > "informix".i_acc_pay on "informix".acc_pay > > (ref_typ,ref_no,pay_dt,pay_typ,pay_chg_typ,bas_doc_typ,bas_doc_no, > > clt_cd,trn_cd,doc_series,yymm,doc_no) using btree ; create index > "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) > > using btree ; > > And my syntax as below: > > select ref_no, pay_dt, surr_key, trn_cd, doc_series, yymm, doc_no, > bas_doc_no, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid from> acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and > ref_no = "T00001" > and ref_typ = "AGT" > and trn_cd is not null > and trn_cd != " " > and pay_dt <="31/03/2016" > order by pay_dt > > On ids 11.10, look i success run using index path as below: > > 1) informix.acc_pay: INDEX PATH > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - > informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > informix.acc_pay.trn_cd IS N OT NULL ) > > (1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ > bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, > fragments: ALL) > > Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND > informix.acc_pay.ref_typ = 'AGT' ) > > Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 > > Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) > > But when we run it at ids 12.10 after i drop & recreate again index, it > give us that syntax not running using index path , but running using > sequence scan as below: > > 1) informix.acc_pay: SEQUENTIAL SCAN > > Filters: (((((informix.acc_pay.ref_no = 'T00001' AND > informix.acc_pay.pay_amt > + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - > informix.acc_pay.adj_ > amt > 0.00 ) AND informix.acc_pay.ref_typ = 'AGT' ) AND > informix.acc_pay.trn_cd != ' ' ) AND informix.acc_pay.trn_cd IS NOT > NULL ) AND informix.acc_pay.pay_dt <= 31/03/ > 2016 ) > > What happen to current index? Based on my syntax, can u advice the > right index we should created for this table? > > Thank You > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.

Mohd: See my responses below: 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, Apr 7, 2016 at 11:07 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > That indicates that the data distributions in the v12.10 are way off, What > do > you means "way off"? need to change configuration to on this? > No, you need to replace the existing data distributions. If you just ran update statistics without dropping distributions, a) the commands may havebeen no-op because since v11.50 update statistics, by default, will only do anything if the distributions are stale, meaning that the engine's estimates of the number of rows that have been modified since the last time distributions were calculated is beyond the percentage specified in the STATCHANGE parameter in the ONCONFIG file. IB the default is 10%. When you upgraded the engine from v11.10 to v12.10 the table's partition header pages had to be modified to the newer version which includes a history of inserts, updates, and deletes. But no history existed so those values were zeros. Similarly the sysdistrib records had to be altered to include the columns needed to record the levels of updates, deletes, and inserts at the time distributions were calculated. Those were also zeros. So, no changes were detected and the update statistics commands were likely no-op'd. Changes in the internal handling of update statistics from one version to another is why the migration guide recommends that you drop distributions after an upgrade and then recalculate the distributions into an empty sysdistrib table. > > Questions: > > 1.Was this an in-place upgrade or a new instance? I suspect a new > > instance. If so then how did you load the data? > > - I do in-place migration. At new server, i install ids version 11.10 first > and than restore data using ontape command. After complete restored, i > install > ids 12.10 and bring up the instance using ids 12.10. After that before test > application, we run updates statistics low, medium & high for all table. > See above. > > Any step i miss during migration? > Yes, dropping distributions and rebuilding them. See below. > > 2.. Is there a difference in the partitioning of the table in the two > > instances? - all table at same instance > > 3. Is the storage used by both instances comparable (ie both the same > > RAID level, same number of spindles or types of drives, same stripe block > > size, etc.)? > > A. ids 12.10 using better disk/storage compare with ids 11.10 > Better means what? > > 4. If the upgrade was in-place did you drop all data distributions then > > recreate them from scratch after the upgrade? > - Yes, we do in-place upgrade, how to drop all data distributions and > recreate > again? We need to do this? > Yes, you need to do this -drop distributions. This is done with: UPDATE STATISTICS DROP DISTRIBUTIONS; -- run this before any other updatestatistics on individual tables. Then rerun dostats or the recommended commands for all tables. > > I try to point back my problem, same query return higher query cost on ids > 12.10, if u see my first posting, on ids 11.10 this query successfully > running > using index patch, but on ids 12.10 it running using sequence scan, why it > return different result, any wrong with my exiting index? Any parameter > config > on ids 12.10 need to enable to allow syntax using index. > Your original SET EXPLAIN output showed both servers using INDEX PATH. I do see that the SET EXPLAIN output you have below is showing SEQUENTIAL SCAN for v12.10. That points back to my suggestion that if the update statistics (after drop distributions) doesn't fix this then you should try setting the "OPT_SEEK_FACTOR 0" in your ONCONFIG file and bounce the instance or run "onmode -wf OPT_SEEK_FACTOR=0" to set it dynamically and see if that fixed it. > > Here i share my table structure : > > $ dbschema -db life -t acc_pay> > DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC6AEE > > { TABLE "informix".acc_pay row size = 118 number of columns = 22 index > size = > 201 } > > create table "informix".acc_pay > ( > > ref_typ char(3) not null , > > ref_no char(13) not null , > > pay_dt date not null , > > pay_typ char(3) not null , > > pay_chg_typ char(3) not null , > > trn_cd char(2), > > doc_series char(2), > > yymm char(4), > > doc_no integer, > > prio_no smallint, > > pay_amt decimal(12,2), > > pay_int_amt decimal(12,2), > > paid_amt decimal(12,2), > > adj_amt decimal(12,2), > > rel_amt decimal(12,2), > > bnk_cd char(4), > > bas_doc_typ char(3), > > bas_doc_no char(14), > > clt_cd char(8), > > surr_key char(8), > > lst_int_dt date, > > versn smallint > ); > > revoke all on "informix".acc_pay from "public" as "informix";> > create index "stjf".i05_acc_pay on "informix".acc_pay (ref_typ, > > trn_cd,pay_dt) using btree ; > create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) > > using btree ; > create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) > > using btree ; > create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, > > bas_doc_no,pay_typ,pay_dt) using btree ; > create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, > > yymm,doc_no) using btree ; > create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, > > pay_typ,pay_chg_typ,bas_doc_no) using btree ; > create unique index "informix".i_acc_pay on "informix".acc_pay > > (ref_typ,ref_no,pay_dt,pay_typ,pay_chg_typ,bas_doc_typ,bas_doc_no, > > clt_cd,trn_cd,doc_series,yymm,doc_no) using btree ; > create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) > > using btree ; > > And my syntax as below: > > select ref_no, pay_dt, surr_key, trn_cd, doc_series, yymm, doc_no, > bas_doc_no, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid from> acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and > ref_no = "T00001" > and ref_typ = "AGT" > and trn_cd is not null > and trn_cd != " " > and pay_dt <="31/03/2016" > order by pay_dt > > On ids 11.10, look i success run using index path as below: >@@N

I'm a little late to the party, but if the same query plan is being generated on both systems AND yet the performance on the new machine is so much worse, then that points to something different in either the environment or the instance configuration. It might help to first see the onconfig files for both the old instance and then the new instance. Also it might be worth it to examine the physical layout of the chunks on the old and new systems. It could be that the physical layout of the chunks on the new system is causing some type of disk contention. It might be worth it to compare iostats on the old and new system while the query is running. Madison Pruet Retired and Loving it On Thursday, April 7, 2016 1:35 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: Hi, That indicates that the data distributions in the v12.10 are way off, What do you means "way off"? need to change configuration to on this? Questions: 1.Was this an in-place upgrade or a new instance? I suspect a new instance. If so then how did you load the data? - I do in-place migration. At new server, i install ids version 11.10 first and than restore data using ontape command. After complete restored, i install ids 12.10 and bring up the instance using ids 12.10. After that before test application, we run updates statistics low, medium & high for all table. Any step i miss during migration? 2.. Is there a difference in the partitioning of the table in the two instances? - all table at same instance 3. Is the storage used by both instances comparable (ie both the same RAID level, same number of spindles or types of drives, same stripe block size, etc.)? A. ids 12.10 using better disk/storage compare with ids 11.10 4. If the upgrade was in-place did you drop all data distributions then recreate them from scratch after the upgrade? - Yes, we do in-place upgrade, how to drop all data distributions and recreate again? We need to do this? I try to point back my problem, same query return higher query cost on ids 12.10, if u see my first posting, on ids 11.10 this query successfully running using index patch, but on ids 12.10 it running using sequence scan, why it return different result, any wrong with my exiting index? Any parameter config on ids 12.10 need to enable to allow syntax using index. Here i share my table structure : $ dbschema -db life -t acc_pay DBSCHEMA Schema Utility INFORMIX-SQL Version 12.10.FC6AEE { TABLE "informix".acc_pay row size = 118 number of columns = 22 index size = 201 } create table "informix".acc_pay ( ref_typ char(3) not null , ref_no char(13) not null , pay_dt date not null , pay_typ char(3) not null , pay_chg_typ char(3) not null , trn_cd char(2), doc_series char(2), yymm char(4), doc_no integer, prio_no smallint, pay_amt decimal(12,2), pay_int_amt decimal(12,2), paid_amt decimal(12,2), adj_amt decimal(12,2), rel_amt decimal(12,2), bnk_cd char(4), bas_doc_typ char(3), bas_doc_no char(14), clt_cd char(8), surr_key char(8), lst_int_dt date, versn smallint ); revoke all on "informix".acc_pay from "public" as "informix"; create index "stjf".i05_acc_pay on "informix".acc_pay (ref_typ, trn_cd,pay_dt) using btree ; create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) using btree ; create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) using btree ; create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, bas_doc_no,pay_typ,pay_dt) using btree ; create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, yymm,doc_no) using btree ; create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, pay_typ,pay_chg_typ,bas_doc_no) using btree ; create unique index "informix".i_acc_pay on "informix".acc_pay (ref_typ,ref_no,pay_dt,pay_typ,pay_chg_typ,bas_doc_typ,bas_doc_no, clt_cd,trn_cd,doc_series,yymm,doc_no) using btree ; create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) using btree ; And my syntax as below: select ref_no, pay_dt, surr_key, trn_cd, doc_series, yymm, doc_no, bas_doc_no, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid fromacc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no = "T00001" and ref_typ = "AGT" and trn_cd is not null and trn_cd != " " and pay_dt <="31/03/2016" order by pay_dt On ids 11.10, look i success run using index path as below: 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn_cd IS N OT NULL ) (1) Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_doc_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) But when we run it at ids 12.10 after i drop & recreate again index, it give us that syntax not running using index path , but running using sequence scan as below: 1) informix.acc_pay: SEQUENTIAL SCAN Filters: (((((informix.acc_pay.ref_no = 'T00001' AND informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - informix.acc_pay.paid_amt - informix.acc_pay.adj_ amt > 0.00 ) AND informix.acc_pay.ref_typ = 'AGT' ) AND informix.acc_pay.trn_cd != ' ' ) AND informix.acc_pay.trn_cd IS NOT NULL ) AND informix.acc_pay.pay_dt <= 31/03/ 2016 ) What happen to current index? Based on my syntax, can u advice the right index we should created for this table? Thank You ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

not sure if you found a resolution to this by now but if you have not: 1. Bug. Just because the optimizer says it is choosing the correct index does not necessarily mean that the underlying scan of the btree nodes is being done in an optimal fashion . I ran into a very similar issue going from 11.50 to 11.70 where the correct index was chosen but the run time was much longer. The much higher cost also hints that the something in the catalogs may be out of whack which may be influencing how the scan is directed . You probably will need to open a support case. 2. Corrupted index (i.e. out of order keys). This can cause an index scan to take some goofy twists and turns. If you are able to, try comparing the reads (onstat -D) on the chunks the index resides while the query is running on both versions and see if v12 are much higher. You will have to do this after a restart (so the data is not cached) and be sure to run onstat -z before doing this to zero out the existing metrics. Migrations and Optimizer's .. perfect together. Mark

Mike, I would love to understand what caused a same query plan to be significant slower in an N+1 version compared to an N version. That's not neither normal or usual. I've seen a corrupted index cause the query to use a different query plan (if run on a secondary server) or cause an AF. Never saw one cause slowness. But the most important is that the query plans are not the same. V12 is doing a full table scan. The OP was already advised to drop distributions and run update statistics high. To avoid any confusion he should disable (set to 0) the AUTO_STAT_MODE in $ONCONFIG to make sure the statistics are really being calculated. We're waiting on further feedback after those tests. As for the migration and optimizer, although I can understand the point I could also mention a case where I've seen good DBAs spending around 1 month capturing the version N query plans to force version N+1 to run the same plans (using a somehow similar feature to our external directives). That was on big Oh! :) So you can imagine the confidence they have on the optimizer... Regards. On Fri, Apr 8, 2016 at 3:19 PM, MARK JALKIEWICZ <mark.jalkiewicz@verizon.net > wrote: > not sure if you found a resolution to this by now but if you have not: > > 1. Bug. Just because the optimizer says it is choosing the correct index > does > not necessarily mean that the underlying scan of the btree nodes is being > done > in an optimal fashion . I ran into a very similar issue going from 11.50 to > 11.70 where the correct index was chosen but the run time was much longer. > The > much higher cost also hints that the something in the catalogs may be out > of > whack which may be influencing how the scan is directed . You probably will > need to open a support case. > > 2. Corrupted index (i.e. out of order keys). This can cause an index scan > to > take some goofy twists and turns. > > If you are able to, try comparing the reads (onstat -D) on the chunks the > index resides while the query is running on both versions and see if v12 > are > much higher. You will have to do this after a restart (so the data is not > cached) and be sure to run onstat -z before doing this to zero out the > existing metrics. > > Migrations and Optimizer's .. perfect together. > > Mark > > > > ******************************************************************************* > 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... --089e01229f743c9910052ffa5f09

Hi, "corrupted index cause", i already look for this point. Today after i drop table & recreate again table & index, that run the Syntax again. Now i get much-much better result as below: QUERY: (OPTIMIZATION TIMESTAMP: 04-01-2016 11:41:27) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 2080537 Estimated # of Rows Returned: 246454 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn _cd IS NOT NULL ) (1) Index Name: informix.i_acc_pay Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_do c_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL ) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc _pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acc_pay type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 246454 827409 00:04.91 2080538 Estimated cost still higher ( table have 22 milion record) but time a lot of improved. Because we will do in-place migration from 11.10 to 12.10, look like some table not working good after migration that i noticed, index maybe corrupted. How to avoid this? Thank You

Hi, "corrupted index cause", i already look for this point. Today after i drop table & recreate again table & index, that run the Syntax again. Now i get much-much better result as below: QUERY: (OPTIMIZATION TIMESTAMP: 04-01-2016 11:41:27) ------ select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 and ref_no ="T00001" and ref_typ = "AGT" and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" order by pay_dt Estimated Cost: 2080537 Estimated # of Rows Returned: 246454 1) informix.acc_pay: INDEX PATH Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND informix.acc_pay.trn _cd IS NOT NULL ) (1) Index Name: informix.i_acc_pay Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_do c_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: ALL ) Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc _pay.ref_typ = 'AGT' ) Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 acc_pay type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 0 246454 827409 00:04.91 2080538 Estimated cost still higher ( table have 22 milion record) but time a lot of improved. Because we will do in-place migration from 11.10 to 12.10, look like some table not working good after migration that i noticed, index maybe corrupted. How to avoid this? Do your have any noted/document about best practices for in-place migration to ids 12.10. Thank You

What you did in practice made the engine refresh the stats which everybody was suspecting had not happen before... Regards. On Fri, Apr 8, 2016 at 4:19 PM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > "corrupted index cause", i already look for this point. Today after i drop > table & recreate again table & index, that run the Syntax again. Now i get > much-much better result as below: > > QUERY: (OPTIMIZATION TIMESTAMP: 04-01-2016 11:41:27) > ------ > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - adj_amt), > rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, doc_no, bas_doc_no > from acc_pay where ( pay_amt + pay_int_amt - paid_amt - adj_amt) > 0 > and ref_no ="T00001" > and ref_typ = "AGT" > and ( trn_cd is not null and trn_cd != " ") > and pay_dt <="31/03/2016" > order by pay_dt> > Estimated Cost: 2080537 > Estimated # of Rows Returned: 246454 > > 1) informix.acc_pay: INDEX PATH > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - info > rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > informix.acc_pay.trn > _cd IS NOT NULL ) > > (1) Index Name: informix.i_acc_pay > > Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ bas_do > c_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, fragments: > ALL > ) > > Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND informix.acc > _pay.ref_typ = 'AGT' ) > > Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 > > Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) > > Query statistics: > ----------------- > > Table map : > ---------------------------- > Internal name Table name > ---------------------------- > t1 acc_pay > > type table rows_prod est_rows rows_scan time est_cost > ------------------------------------------------------------------- > scan t1 0 246454 827409 00:04.91 2080538 > > Estimated cost still higher ( table have 22 milion record) but time a lot > of > improved. Because we will do in-place migration from 11.10 to 12.10, look > like > some table not working good after migration that i noticed, index maybe > corrupted. How to avoid this? Do your have any noted/document about best > practices for in-place migration to ids 12.10. > > Thank You > > > > ******************************************************************************* > 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... --001a113ee8685c0374052ffae052

Mohd, It sounds to me that some underlying corruption existed although oncheck did not detect it for whatever reason. It happens and the table/index rebuild has taken care of that. I still do not like the wide disparity in costs.. I would log a PMR over that. Support may ask you to upload your catalog data related to the distributions for analysis. Since the migration will be in place its essentially an upgrade. In that case follow "upgrade/migration best practices" in which there are various documents that exist on what to do beforehand such as sanity checks (oncheck) and checking for outstanding in-place table alters, running update statistics afterwards etc. and obtaining a good sound benchmark on your mission critical application run times. Be sure to familiarize yourself with the new features/parameters (and deprecrated) parameters in v12 that may affect performance. Mark Good luck, Mark

Rebuilding that one index would only fix the statistics for that index. If he didn't drop the old distributions, then all of the other stats are still unusable. He still needs to do it. Mohd: Did you issue the command UPDATE STATISTICS LOW DROP DISTRIBUTIONS before your UPDATE STATISTICS MEDIUM, etc.? --EEM > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando Nunes > Sent: Friday, April 08, 2016 10:31 AM > To: ids@iiug.org > Subject: Re: IDS 12.1- Query cost High [36938] > > What you did in practice made the engine refresh the stats which > everybody was suspecting had not happen before... > Regards. > > On Fri, Apr 8, 2016 at 4:19 PM, MOHD FADZIL JUSOH > <fadzil@isianpadu.com> > wrote: > > > Hi, > > > > "corrupted index cause", i already look for this point. Today after i > > drop table & recreate again table & index, that run the Syntax again. > > Now i get much-much better result as below: > > > > QUERY: (OPTIMIZATION TIMESTAMP: 04-01-2016 11:41:27) > > ------ > > select surr_key, pay_dt, ( pay_amt + pay_int_amt - paid_amt - > > adj_amt), rowid, pay_typ, pay_chg_typ, trn_cd, doc_series, yymm, > > doc_no, bas_doc_no from acc_pay where ( pay_amt + pay_int_amt -> > paid_amt - adj_amt) > 0 and ref_no ="T00001" > > and ref_typ = "AGT" > > and ( trn_cd is not null and trn_cd != " ") and pay_dt <="31/03/2016" > > order by pay_dt > > > > Estimated Cost: 2080537 > > Estimated # of Rows Returned: 246454 > > > > 1) informix.acc_pay: INDEX PATH > > > > Filters: (informix.acc_pay.pay_amt + informix.acc_pay.pay_int_amt - > > info rmix.acc_pay.paid_amt - informix.acc_pay.adj_amt > 0.00 AND > > informix.acc_pay.trn _cd IS NOT NULL ) > > > > (1) Index Name: informix.i_acc_pay > > > > Index Keys: ref_typ ref_no pay_dt pay_typ pay_chg_typ bas_doc_typ > > bas_do c_no clt_cd trn_cd doc_series yymm doc_no (Key-First) (Serial, > fragments: > > ALL > > ) > > > > Lower Index Filter: (informix.acc_pay.ref_no = 'T00001' AND > > informix.acc _pay.ref_typ = 'AGT' ) > > > > Upper Index Filter: informix.acc_pay.pay_dt <= 31/03/2016 > > > > Index Key Filters: (informix.acc_pay.trn_cd != ' ' ) > > > > Query statistics: > > ----------------- > > > > Table map : > > ---------------------------- > > Internal name Table name > > ---------------------------- > > t1 acc_pay > > > > type table rows_prod est_rows rows_scan time est_cost > > ------------------------------------------------------------------- > > scan t1 0 246454 827409 00:04.91 2080538 > > > > Estimated cost still higher ( table have 22 milion record) but time a > > lot of improved. Because we will do in-place migration from 11.10 to > > 12.10, look like some table not working good after migration that i > > noticed, index maybe corrupted. How to avoid this? Do your have any > > noted/document about best practices for in-place migration to ids > > 12.10. > > > > Thank You > > > > > > > > > *********************************************************************** > ******** > > 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... > > --001a113ee8685c0374052ffae052 > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.