IDS Forum

Re: IDS 12.1- Query cost High

Posted By: Art Kagel
Date: Thursday, 7 April 2016, at 11:42 a.m.

In Response To: Re: IDS 12.1- Query cost High (MOHD FADZIL JUSOH)

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 have
been 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 update
statistics 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:
>
> 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:
>

In v12.10 building an index automatically runs an update statistics HIGH on
the index key columns only. Since, as I said, the insert, update, delete
statistics would all be zeros that update statistics was probably a no-op
but updated the low level stats for the index itself. That small change
probably changed the query plan from INDEX PATH to SEQUENTIAL SCAN. Again,
do the update statistics the way I have recommended above and see then.

>
> 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.
>
>

--089e0139fddcf1a67b052fe6ec44

Messages In This Thread

• IDS 12.1- Query cost High
  MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 4:52 a.m.
  • Re: IDS 12.1- Query cost High
    Art Kagel -- Thursday, 7 April 2016, at 6:40 a.m.
    • Re: IDS 12.1- Query cost High
      MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 7:04 a.m.
      • Re: IDS 12.1- Query cost High
        Fernando Nunes -- Thursday, 7 April 2016, at 7:32 a.m.
      • Re: IDS 12.1- Query cost High
        Art Kagel -- Thursday, 7 April 2016, at 7:58 a.m.
        • Re: IDS 12.1- Query cost High
          MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 11:07 a.m.
          • RE: IDS 12.1- Query cost High
            Everett Mills -- Thursday, 7 April 2016, at 11:19 a.m.
          • Re: IDS 12.1- Query cost High
            Art Kagel -- Thursday, 7 April 2016, at 11:42 a.m.
            • Re: IDS 12.1- Query cost High
              MOHD FADZIL JUSOH -- Friday, 8 April 2016, at 11:19 a.m.
              • Re: IDS 12.1- Query cost High
                Fernando Nunes -- Friday, 8 April 2016, at 11:30 a.m.
                • RE: IDS 12.1- Query cost High
                  Everett Mills -- Friday, 8 April 2016, at 1:32 p.m.
          • Re: IDS 12.1- Query cost High
            Madison Pruet -- Thursday, 7 April 2016, at 8:10 p.m.
      • Re: IDS 12.1- Query cost High
        Spokey Wheeler -- Thursday, 7 April 2016, at 8:04 a.m.
        • Re: IDS 12.1- Query cost High
          Fernando Nunes -- Thursday, 7 April 2016, at 10:12 a.m.
      • RE: IDS 12.1- Query cost High
        Mike Walker -- Thursday, 7 April 2016, at 8:58 a.m.
        • Re: RE: IDS 12.1- Query cost High
          MOHD FADZIL JUSOH -- Thursday, 7 April 2016, at 10:46 a.m.
          • Re: IDS 12.1- Query cost High
            Spokey Wheeler -- Thursday, 7 April 2016, at 10:54 a.m.
          • Re: RE: IDS 12.1- Query cost High
            Fernando Nunes -- Thursday, 7 April 2016, at 11:13 a.m.
          • RE: RE: IDS 12.1- Query cost High
            Mike Walker -- Thursday, 7 April 2016, at 11:18 a.m.
  • Re: IDS 12.1- Query cost High
    MARK JALKIEWICZ -- Friday, 8 April 2016, at 10:19 a.m.
    • Re: IDS 12.1- Query cost High
      Fernando Nunes -- Friday, 8 April 2016, at 10:54 a.m.
      • Re: IDS 12.1- Query cost High
        MOHD FADZIL JUSOH -- Friday, 8 April 2016, at 11:12 a.m.
        • Re: IDS 12.1- Query cost High
          MARK JALKIEWICZ -- Friday, 8 April 2016, at 11:55 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.