IDS Forum

Re: IDS 12.1- Query cost High

Posted By: Madison Pruet
Date: Thursday, 7 April 2016, at 8:10 p.m.

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

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

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.