DB2 Performance problems after REORG

[kartic_s]

he brief explanation of the problem is,

After DB2 maintenance window performance has gone down, it takes some hours to retrieve the data.

We have provided some table spaces to Reorg/Runstat during the maintenance window .

After REORG some of the tablespaces, the performance is very slow .This is really strange because no code changes has been involved and also nothing was changed, it's just a Reorg of some tablespaces.

I think this problem might be with the Index space cluster ratio, because some of the tables cluster ratio is very low and also it is accessing the wrong cluster indexes.

Please let me know your comments

Regards,
karthik

[srobert]

hi,

I have some experience about this. Do you try db2 performance expert? it is very useful, it monitors, analyzes and tunes the performance of DB2 and DB2 applications, can help further optimize DB2 performance and maximize DBA productivity.

One book "DB2 High Performance Design and Tuning" should help you, you can search and find it.

--------------------------------------------------
software reviews

[Snib]

You may want to look into SYSPACKDEP and determine which packages are using any of the tables in the tablespaces you have re-org'd.

Are you running a rebind after the RE-ORG and RUNSTATS?

If not then DB2 may not be using the best path to access the data after the RE-ORG.

Also, did you check the runstats before with the runstasts after the "RE-ORG/RUNSTAT" activity to understand what changed?

If you can isolate the affected packages, from syspackdep and catalogue value changes applied by the RUNSTATs you may be able to isolate this down to some specific SQL. If you can get that far then you can run an EXPLAIN of the SQL with the new RUNSTATs catalogue value and check this against the current access path being used by the SQL in the pacakge. This will help to determine if a REBIND after the RE-ORG/RUNSTAT activity will resolve the problem.

The other possibility is that the organisation of the data on the table has "out grown" the current index organisation or primary key setup e.g. you have a "hotspot" in your data so any access into this part of your key range results in a performance isssue. This will not show up on an EXPLAIN as the SQL access path could be fine. You need to look at the column cardinately for the columns in the indexes accessed by the SQL. If combinations of columns in an index has a low cardinately then a large number of rows will need to be accessed. If the SQL has other predicates that act on columns not in the index pages then DB2 will retrieve the data page for each of these rows to resolve the predicates - this can result in a lot of I/O in the back ground for a piece of SQL that looks really good in the EXPLAIN. This would require you to review your data structure, column contents and index structure to resolve this kind of problem.

Regards

Snib

[Snib]

Another cause I have come across is when the NLEVELS (think this is the row name) on for the index exceeds 3 or 4. This values indicates the maximum (I think) number of index levels that DB2 has to navigate to get to a leaf page - which contains the the actual index data. If I remember correctly 3 is the most people expect to see on a well organised index, rarely 4. If the nlevels is more than this it can cause performance problems.

This is another value you should check out before and after the RE-ORG for any indexes affected by the activity.

When this occured for me I think it was related to low cardinately on the key and also a high free space/free page combination.

Something else to consider is the current growth rate and maturity of the data. If the database is "young" the growth rate may still be high. If the maturity of the data is low in relation to the diversity of values in each field then this can affect how well distributed the data is and how quickly an index can become declustered.

When re-org a database with these characteristic you need to consider do rebinds and then executing an analysis of the new access paths against the old to attempt to identify any that have degraded i.e. dropped to table space or index space scans.

Hope this helps,

Snib