DB2 Performance is slow /spooky even with Index usage - help 3

[Db2curiosity]

Hi,

1> i have a composite index I1 on column A,B,C,D,E out of which A,B is present in where clause and column D is also present in where clause but with a <> operator.

The query plan is showing INDEX I is being used still the query is kind of slow ... any tips for improving?

by the way ,i have another index I2 on that same table say
on column entry_dt only..

2>When i add a REDUNDANT where clause "and entry_dt= entry_dt" the query starts using I2 and gets processed much faster ( almost 5 times ) ...

can anyone explain wy this is happening ? though finally i cant use I2 because column entry_dt has null values so that redundant clause will hamper my query results

Need advise and help...!!

 

[gregsimpson]

Db2curiosity,

Often when you're getting strange results the optimiser doesn't have up to date stats on your tables and indexes. In which case check when you last ran a runstats command.

Have you tried running your query through db2advis. This will usually recommend the best indexes possible for the query you are running. Here's a good link to db2advis. I've had excellent results from it.

http://publib.boulder.ibm.com/infoc...?topic=/com.ibm.db2.udb.doc/core/r0002452.htm

Cheers
Greg

 

[Db2curiosity]

Greg,
First thx for ur reply... i did do the RUNSTATS...

The spooky thing is that the query is taking 20 mins though the db2expln is showing that its using INDEX !!

is it like i have to find a better index through db2advis?

the tables are having 16 MM and 200k rows respectively so there is no point taking 20 mins...

I have tried inline views,arranging the where clauses... nothing is really working...

any more tips ?

 

[blom0344]

Did you check the fragmentation state of the tables with 'reorgchk'? Perhaps reorganizing the table based on the composite index will make a difference...

What happens with performance if you test query with the negative condition on column D..?

Ties Blom
Information analyst

 

[gregsimpson]

I completely agree with Ties. I should have mentioned a reorg followed by a runstats may have had the biggest impact. If the query you're running is greatly fragmented it can take longer to read the index followed by the table than simply sweeping through the table alone, due to the extra reads and effectively no valuable prefetch type action occurring. Try the reorg and the runstats and try again.

In the meantime if you have time, can you paste your query to the forum.

Cheers
Greg

 

[blom0344]

Correction:

Where I wrote:

What happens with performance if you test query with the negative condition on column D..?

I meant:

What happens with performance if you test query [b]without [/b] the negative condition on column D..?

Ties Blom
Information analyst

 

[Db2curiosity]

The query plan(COST) is same as earlier even after removing "NOT EQUAL" clause.

In the Production environment they have RUN STATS scheduled but REORG is done in certain intervals and its for sure the tables are monitored for Fragmentation etc.

Let me send you the Tables,Query INDEX its using and the Plan. Please suggest if you feel anything missing.

Table X has 20 MM rows
Table Y has 75k
Table Z has 2 MM

select a.SOURCE_SYSTEM,a.COMPANY_CODE,a.SEPARATE_ACCT,a.MARGIN_CODE,a.FUND_NUMBER,a.VALUATION_DT
,a.PROCESS_DT,a.AGREEMENT_ID,a.FINCL_ASSET_ID,b.ACCOUNT_ID,'7',a.PLAN_NUM,a.PART_NUM
,a.FUND_SRC,a.FUND_IV,a.CHAR_AGMT_ID,a.CHAR_FINCL_ID,a.HIDDEN_FL,CHAR(a.VALUATION_DT)
from Y a ,db2dba.X b
where b.source_system_nm='DOUG'
and b.LOAD_PROCESS_KEY_PART_01 = a.CHAR_AGMT_ID
and b.BUSINESS_KEY_PART_04 > a.FUND_IV
and b.BUSINESS_KEY_PART_08 > char(a.VALUATION_DT)
and b.LOAD_PROCESS_KEY_PART_02<>a.CHAR_FINCL_ID
and not exists(select '7' from Z d
where b.ACCOUNT_ID=d.LEFT_ACCOUNT_ID
and d.VALID_TO_DT='9999-12-31')

DB2DBA.IX08ACCRRE
ON DB2DBA.X
(SOURCE_SYSTEM_NM ASC,
LOAD_PROCESS_KEY_PART_01 ASC,
LOAD_PROCESS_KEY_PART_02 ASC,
BUSINESS_KEY_PART_06 ASC,
BUSINESS_KEY_PART_09 ASC,
DELETE_DT ASC
)

Step Operation Object name Object type Cost
---- ----------------------- ------------------------------- --------
1 Index Scan DB2DBA.IX08ACCRRE Index 221040.78
2 Fetch Table Data DB2DBA.X Table 1106767.25
3 Table Queue 1110893.75
4 SHIP TERA.Y NK 4008.26
5 Sort 4156.59
6 Sorted Table Data Scan 4165.79
7 Filter Rows 4238.90
8 Merge Scan Join 1131433.75
9 Index Scan DB2DBA.IX07ACRL2 Index 34.09
10 Fetch Table Data DB2DBA.Z Table 36.41
11 Table Queue 36.51
12 Sort 36.51
13 Sorted Table Data Scan 36.51
14 Filter Rows 36.52
15 Nested Loop Join 1131856.00
16 Data Operation Complete 1131856.25

 

[Db2curiosity]

By the way, we dont have Db2advis,so i cant really check that tools advise.. But i'm thinking of a different INDEX ...

let me know if creating a different index instead of the one its using can make a difference...

Thanks

 

[blom0344]

Looks like a query from hell . Are you sure this is okay?

In particular this:

b.BUSINESS_KEY_PART_04 [COLOR=red] > [/color] a.FUND_IV
  and b.BUSINESS_KEY_PART_08 [COLOR=red] > [/color] char(a.VALUATION_DT)

and

[b]not[/b] exists(select '7' from Z d 
              where b.ACCOUNT_ID=d.LEFT_ACCOUNT_ID 
        and d.VALID_TO_DT='9999-12-31')

Ties Blom
Information analyst

 

[Db2curiosity]

Unfortunately that's how the current business logic is built..!!

do you have any alternatives in mind for those clauses,
of course the result should not alter.

 

[MarcLodge]

Db2Curiosity,
I would try adding an index based on the fields in your select:
b.source_system_nm
b.LOAD_PROCESS_KEY_PART_01
b.BUSINESS_KEY_PART_04
b.BUSINESS_KEY_PART_08
b.LOAD_PROCESS_KEY_PART_02

I notice also that you have a DELETE_DT in the original index for the table. Is this a nullable field? Can this be used to further cut down the number of rows? I'm thinking along the lines that if a delete date is present, you don't wish to return the row.

Marc

 

[Db2curiosity]

Hi Marc,

Yep,DELETE_DT is Nullable and its used in some other
queries like " and b.DELETE_DT is null "...

I was thinking of a index on the following..The reason i excluded LOAD_PROCESS_KEY_PART_02 is because in most of the queries its used as <> , so would it help if we add that in the INDEX ?

SOURCE_SYSTEM_NM
LOAD_PROCESS_KEY_PART_01
BUSINESS_KEY_PART_04
BUSINESS_KEY_PART_08
ACCOUNT_ID
DELETE_DT

Let me know what you think.... Thanks again !!

 

[MarcLodge]

If DELETE_DT is applicable, from a business perspective, I would consider adding it to the index.

On the <> issue, I'm not 100% sure. When I first used DB2 (version 2.1 if I recall rightly) we were always told that <> would not use an index, but I'm fairly sure that as the versions have gone up, this restriction has receded. I am now under the impression that <> will use an index if there is one. I guess the best way is to add the index and explain the query to see what happens.

If Greg or Ties can clarify, I'd be delighted to hear from them.

Regards,

Marc

 

[blom0344]

My 7.1 version of UDB will perform a full table access when a condition like '<>' is used on the column that is indexed. If the access plan does not change when you skip the negative condition, then the index is not used in the first place (?)

What would be the percentage of records fetched through the first index on the 20mm table?

Ties Blom
Information analyst

 

[Db2curiosity]

Marc/Ties,

You may be right,it looks like Db2 8 versions support indexing even on <> columns.I mean if you include the column in the index get picked and helps cutting costs.

Earlier i made the new index as follows :-

SOURCE_SYSTEM_NM
LOAD_PROCESS_KEY_PART_01
BUSINESS_KEY_PART_04
BUSINESS_KEY_PART_08
ACCOUNT_ID
DELETE_DT

But DB2 optimizer is choosing another index(that is of course NOT the best index for my queries) which is having LOAD_PROCESS_KEY_PART_01 and LOAD_PROCESS_KEY_PART_02
both the keys...

when i comment b.LOAD_PROCESS_KEY_PART_02<>a.CHAR_FINCL_ID
from the above query its picking up the New index as above and showing less cost...

so i'm planning to have the new INDEX like following and then re-check the Db2explain...

SOURCE_SYSTEM_NM
LOAD_PROCESS_KEY_PART_01
LOAD_PROCESS_KEY_PART_02
BUSINESS_KEY_PART_04
BUSINESS_KEY_PART_08
ACCOUNT_ID
DELETE_DT

let me know if i'm missing anything... looks like we are getting there...

thank you all

 

[gregsimpson]

Db2curiosity,

are you sure you don't have db2advis. It ships with Version 8 for sure. Only used it on Windows and AIX though, what platform are you on.

Regardless I reckon it would come up with something along the lines of where we are now. LEt us know how you get on.

Cheers
Greg

 

[Db2curiosity]

Greg and others,

I'm on UNIX... I tried looking for Db2advis -help like you find the commands for Db2expln -help.. But its not coming up.. U are rgt..we would have been here without much effort on last Thursday itself if Db2advis were there..

Yep,the queries are are showing decent PLAN costs in QA now with this new index.. I'll have to do some volume testing to find out if they are really fine while actually loading data..

it looks like the table has 8-9 different composite index with different combination of the above 7 fields.I'm just thinking if i can drop all of them...and have just this NEW INDEX..

QQ> Please advise if the leading column of the Index is not present in the WHERE clause can we force DB2 optimizer to use that INDEX only by any chance...??

using hints kind a thing or other techniques ??