SQL return high query cost on ids 12.10

Hi, I do some testing & comparison between ids 12.10 with IDS 11.10 (OS for both version same) . I try to run SQL sytax for at both version,estimated cost return to very high on ids 12.10. Result as below: 1. result run on IDS 11.10 /home/stjf $ vi acc_pay.txt "acc_pay.txt" 38 lines, 1464 characters 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 run on IDS 12.10 /home/informix/task/april $ more acc_pay4.txt QUERY: (OPTIMIZATION TIMESTAMP: 04-29-2016 13:57:00) ------ 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: 2188546 Estimated # of Rows Returned: 250424 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 250424 829946 00:08.01 2188547 3. Table schema with index. /home/informix/task/april $ dbschema -db life -t acc_pay -ss 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 ) in datadb3 extent size 1769488 next size 221186 lock mode row; 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 in datadb3; create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) using btree in idxdbs3; create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) using btree in idxdbs3; create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, bas_doc_no,pay_typ,pay_dt) using btree in datadb3; create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, yymm,doc_no) using btree in datadb3; create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, pay_typ,pay_chg_typ,bas_doc_no) using btree in datadb3; 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 in idxdbs3; create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) using btree in datadb3; Summary #ids 11.10 Estimated Cost: 1344 Estimated # of Rows Returned: 903 #ids 12.10 Estimated Cost: 2188546 Estimated # of Rows Returned: 250424 Please advice, beside that, How to optimizer index for this table to get better performance. What are different between version ids 11.10 & IDS 12.10? Thank You

UPDATE STATISTICS? Also, does it actually run any slower? > On 16 May 2016, at 08:59, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > > Hi, > > I do some testing & comparison between ids 12.10 with IDS 11.10 (OS for both > version same) . I try to run SQL sytax for at both version,estimated cost > return to very high on ids 12.10. > > Result as below: > > 1. result run on IDS 11.10 > > /home/stjf $ vi acc_pay.txt > "acc_pay.txt" 38 lines, 1464 characters > > 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 run on IDS 12.10 > > /home/informix/task/april $ more acc_pay4.txt > > QUERY: (OPTIMIZATION TIMESTAMP: 04-29-2016 13:57:00) > ------ > 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: 2188546 > Estimated # of Rows Returned: 250424 > > 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 250424 829946 00:08.01 2188547 > > 3. Table schema with index. > > /home/informix/task/april $ dbschema -db life -t acc_pay -ss > > 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 > ) in datadb3 extent size 1769488 next size 221186 lock mode row; > > 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 in datadb3; > create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) > > using btree in idxdbs3; > create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) > > using btree in idxdbs3; > create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, > > bas_doc_no,pay_typ,pay_dt) using btree in datadb3; > create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, > > yymm,doc_no) using btree in datadb3; > create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, > > pay_typ,pay_chg_typ,bas_doc_no) using btree in datadb3; > 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 in idxdbs3; > > create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) > > using btree in datadb3; > > Summary > > #ids 11.10 > Estimated Cost: 1344 > Estimated # of Rows Returned: 903 > > #ids 12.10 > Estimated Cost: 2188546 > Estimated # of Rows Returned: 250424 > > Please advice, beside that, How to optimizer index for this table to get > better performance. What are different between version ids 11.10 & IDS 12.10? > > Thank You > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, Yes, i already run update statistics high for this table. Thank You

Are you sure these query plans are correct? Last time apparently they were mixed and the query plan on 12.10 wasactually different. If the query plan is in fact equal, and the query takeslonger on 12.10 please review you I/O system. Again, just as I answered last time, such a difference should not be explained by any engine issue. If you can't find any I/O bottleneck and you are sure the query plans are the same please open a PMR. Also, don't compare a query run on a running engine with one on an engine that just started.... The running engine can take advantage of an already populated bufer cache. You could try my "ixprofiling" script, but it was designed to test different plans on the same engine, and preferably without any further activity on the engine... I suppose you won't be able to get that on your V11.10 Don't give any credits to the estimated cost difference. As you were already told, the calculations may defer between different versions. The important thing is the query plan and the effective runtime Regards. On Mon, May 16, 2016 at 8:59 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > I do some testing & comparison between ids 12.10 with IDS 11.10 (OS for > both > version same) . I try to run SQL sytax for at both version,estimated cost > return to very high on ids 12.10. > > Result as below: > > 1. result run on IDS 11.10 > > /home/stjf $ vi acc_pay.txt > "acc_pay.txt" 38 lines, 1464 characters > > 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 run on IDS 12.10 > > /home/informix/task/april $ more acc_pay4.txt > > QUERY: (OPTIMIZATION TIMESTAMP: 04-29-2016 13:57:00) > ------ > 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: 2188546 > Estimated # of Rows Returned: 250424 > > 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 250424 829946 00:08.01 2188547 > > 3. Table schema with index. > > /home/informix/task/april $ dbschema -db life -t acc_pay -ss > > 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 > ) in datadb3 extent size 1769488 next size 221186 lock mode row; > > 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 in datadb3; > create index "informix".i1_acc_pay on "informix".acc_pay (clt_cd) > > using btree in idxdbs3; > create index "informix".i2_acc_pay on "informix".acc_pay (surr_key) > > using btree in idxdbs3; > create index "stjf".i3_acc_pay on "informix".acc_pay (bas_doc_typ, > > bas_doc_no,pay_typ,pay_dt) using btree in datadb3; > create index "stjf".i4_acc_pay on "informix".acc_pay (trn_cd,doc_series, > > yymm,doc_no) using btree in datadb3; > create index "stjf".i5_acc_pay on "informix".acc_pay (bas_doc_typ, > > pay_typ,pay_chg_typ,bas_doc_no) using btree in datadb3; > 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 in idxdbs3; > > create index "stjf".tmp_acc on "informix".acc_pay (bas_doc_no) > > using btree in datadb3; > > Summary > > #ids 11.10 > Estimated Cost: 1344 > Estimated # of Rows Returned: 903@@NL@

But, did you drop all distributions after the upgrade to v12.10 and THEN run update statistics HIGH? In versions 11.50 and later update statistics does nothing if the distributions are recent. Also after an in place major version upgrade the format of the data distributions on disk may change making them not function properly in the new release so you have to drop those older distributions and create new ones from scratch. Additionally, the runtime is significant 0.039s versus 8.01s is a big difference, you I agree that something is wrong, but the costs number has little to do with it. In large part this is due to v11.70 and later adding in the cost of index scans which were never included in the cost before that and that will make the costs higher than they were in v11.10 even if the query path were identical. If UPDATE STATISTICS HIGH ... DROP DISTRIBUTIONS; does not fix they query plan, try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in your ONCONFIG file. This controls the cost assigned to index scans and defaults to 6 (range 0-25). If setting it to zero helps you can try other settings below 6 since this costing change can improve performance for some queries. Turning it off by setting to zero returns the behavior you were seeing in 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 Mon, May 16, 2016 at 4:21 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > Yes, i already run update statistics high for this table. > > Thank You > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c05ff022652bc0532f615f1

Hi, did you drop all distributions after the upgrade to v12.10 and THEN run update statistics HIGH? yes, done for this step. "try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in your ONCONFIG file. This controls the cost assigned to index scans and defaults to 6 (range 0-25). If setting it to zero helps you can try other settings below 6 since this costing change can improve performance for some queries. Turning it off by setting to zero returns the behavior you were seeing in v11.10. " Will try this, need to restart database instance? Hope this parameter can improved our database peformance Thank you for your advice

You can set this dynamically with: onmode -wm OPT_SEEK_FACTOR=0 Not so much improve performance as restore the query plans to those that v11.10 produced by eliminating the cost values associated with index scan activity. The older v11.10 query plan used a single index while the v12.10 is using two indexes via the Multi-Index Scan technology introduced in v11.70. I didn't look in detail, but it may be that together the two indexes that v12.10 used included one or more filter columns from the WHERE clause that were not present in the single index that v11.10 used. So, with or without setting OPT_SEEK_FACTOR creating a new index that contains ALL of the filters will make a new, third, query plan which is better than either of the ones you have experienced. I had not thought of this when I originally posted, but it is worth exploring. 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, May 16, 2016 at 11:12 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> wrote: > Hi, > > did you drop all distributions after the upgrade to v12.10 and THEN > run update statistics HIGH? yes, done for this step. > > "try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in > your ONCONFIG file. This controls the cost assigned to index scans and > defaults to 6 (range 0-25). If setting it to zero helps you can try other > settings below 6 since this costing change can improve performance for some > queries. Turning it off by setting to zero returns the behavior you were > seeing in v11.10. " > > Will try this, need to restart database instance? Hope this parameter can > improved our database peformance > > Thank you for your advice > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93b57d626eaff0532f76ee9

We should not deviate from the main point. The OP stated that he gets the same query plans on 11.10 and 12.10. Ifthat's the case, the discussion about the new parameter is irrelevant and we need to understand why the same query plan runs so much faster on 11.10. As I tried to say before, the only options I can think are: - V11.10 already has the data in the cache - V11.10 has a much better throughput accessing the disks - Eventually the $ONCONFIG settings are dragging the performance on V12.10 down - A bug... which seems highly unlikely Regards On Mon, May 16, 2016 at 4:39 PM, Art Kagel <art.kagel@gmail.com> wrote: > You can set this dynamically with: > > onmode -wm OPT_SEEK_FACTOR=0> > Not so much improve performance as restore the query plans to those that > v11.10 produced by eliminating the cost values associated with index scan > activity. The older v11.10 query plan used a single index while the v12.10 > is using two indexes via the Multi-Index Scan technology introduced in > v11.70. I didn't look in detail, but it may be that together the two > indexes that v12.10 used included one or more filter columns from the WHERE > clause that were not present in the single index that v11.10 used. So, > with or without setting OPT_SEEK_FACTOR creating a new index that contains > ALL of the filters will make a new, third, query plan which is better than > either of the ones you have experienced. I had not thought of this when I > originally posted, but it is worth exploring. > > 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, May 16, 2016 at 11:12 AM, MOHD FADZIL JUSOH <fadzil@isianpadu.com> > wrote: > > > Hi, > > > > did you drop all distributions after the upgrade to v12.10 and THEN > > run update statistics HIGH? yes, done for this step. > > > > "try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in > > your ONCONFIG file. This controls the cost assigned to index scans and > > defaults to 6 (range 0-25). If setting it to zero helps you can try other > > settings below 6 since this costing change can improve performance for > some > > queries. Turning it off by setting to zero returns the behavior you were > > seeing in v11.10. " > > > > Will try this, need to restart database instance? Hope this parameter can > > improved our database peformance > > > > Thank you for your advice > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae93b57d626eaff0532f76ee9 > > > > ******************************************************************************* > 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... --001a1141bb9610fdd00532f78699

Fernando: You are right. The addition of the index name in the v12.10 output (which v11.10 did note suuply) threw me off. As I said, I hadn't looked carefully. The main difference between the two queries are the costs and the estimated rows. The former just reflects the latter (and the change in the calculation) and so the cost can be ignored. The difference in the estimated rows, however, cannot. That both queries returned zero rows but v11.10 estimated returning 903 rows while v12.10 estimated returning 250,424 rows indicates, as we all initially suggested, that the data distributions are not ideal. I suspect that increasing the resolution of the distributions will resolve that problem and that it is due to data skew. However, that said, since, as you correctly pointed out, the query plans are identical the performance of v12.10 should be at least as good as that of v11.10. I suspect something else is going on here. There is some difference between the two databases besides the version of the engine. So, questions for Mohd: - Are the two platforms the same (ie same OS and CPU hardware architecture and speed)? - Are the disk storage used by both systems the same as to - RAW -vs- COOKED, - RAID level, - Number of disks in the array, - Sharing of the disk structures by other applications, etc. - Are the ONCONFIG settings comparable? - Were the indexes on the v12.10 engine for this table build before the data was loaded or after? - Is the layout of the v12.10 server's tables different than the v11.10 layout (ie which tables are in the same dbspace as this acc_pay table? What about the index? (I see in the v12.10 dbschema output that the i_acc_pay index is in a different dbspace from the table itself, but I cannot tell what the layout is under v11.10.) - How many extents does the table have on the v11.10 engine versus the v12.10 engine? (It looks like the v12.10 table is a single large extent, but I cannot be certain of that from here.) - Is the runtime of the test repeatable or dependent on outside influences? 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, May 16, 2016 at 11:46 AM, Fernando Nunes <domusonline@gmail.com> wrote: > We should not deviate from the main point. > The OP stated that he gets the same query plans on 11.10 and 12.10. > Ifthat's the case, the discussion about the new parameter is irrelevant and > we need to understand why the same query plan runs so much faster on 11.10. > > As I tried to say before, the only options I can think are: > > - V11.10 already has the data in the cache > - V11.10 has a much better throughput accessing the disks > - Eventually the $ONCONFIG settings are dragging the performance on V12.10 > down > - A bug... which seems highly unlikely > > Regards > > On Mon, May 16, 2016 at 4:39 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > You can set this dynamically with: > > > > onmode -wm OPT_SEEK_FACTOR=0> > > > Not so much improve performance as restore the query plans to those that > > v11.10 produced by eliminating the cost values associated with index scan > > activity. The older v11.10 query plan used a single index while the > v12.10 > > is using two indexes via the Multi-Index Scan technology introduced in > > v11.70. I didn't look in detail, but it may be that together the two > > indexes that v12.10 used included one or more filter columns from the > WHERE > > clause that were not present in the single index that v11.10 used. So, > > with or without setting OPT_SEEK_FACTOR creating a new index that > contains > > ALL of the filters will make a new, third, query plan which is better > than > > either of the ones you have experienced. I had not thought of this when I > > originally posted, but it is worth exploring. > > > > 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, May 16, 2016 at 11:12 AM, MOHD FADZIL JUSOH < > fadzil@isianpadu.com> > > wrote: > > > > > Hi, > > > > > > did you drop all distributions after the upgrade to v12.10 and THEN > > > run update statistics HIGH? yes, done for this step. > > > > > > "try including the undocumented parameter OPT_SEEK_FACTOR set to 0 in > > > your ONCONFIG file. This controls the cost assigned to index scans and > > > defaults to 6 (range 0-25). If setting it to zero helps you can try > other > > > settings below 6 since this costing change can improve performance for > > some > > > queries. Turning it off by setting to zero returns the behavior you > were > > > seeing in v11.10. " > > > > > > Will try this, need to restart database instance? Hope this parameter > can > > > improved our database peformance > > > > > > Thank you for your advice > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > --14dae93b57d626eaff0532f76ee9 > > > > > > > > > > ******************************************************************************* > > 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... > > --001a1141bb9610fdd00532f78699 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bd75154ce84b10532f83c06