Estimating effectivity of performance improvement measures

From: Robert Klemme <>
Date: Wed, 26 Nov 2008 02:45:32 -0800 (PST)
Message-ID: <>

Today I have another question regarding estimating effectivity of changes.

Situation: we have a query that accesses four tables, which result in 18 buffer gets per execution and 3 physical reads. The plan looks pretty good given the schema and query. This query is responsible for more than 30% of IO of this instance ( (I'll leave out details for now because my main point is independent of the specific SQL statement and schema and the posting is going to be lengthy anyway.)

Obviously I would like to try to optimize this if possible. I took a copy of this production database and tested different approaches
(adding an index which will make one table superfluous, partitioning,
creating a smaller copy of the table as IOT which contains only data relevant for this query). Plans that came out of this looked pretty good as well - and quite similar to the original. Most significant improvement I could get is to reduce the # of consistent gets to 15
(down from 18).

I am figuring, this will _only_ improve the situation in the production database if the set of blocks that this query potentially accesses will be reduced significantly (leading to less physical IO on average because of better cache utilization). Now the tricky part: what would you do to estimate the effects of those different approaches listed above on the production database?

My idea so far is this: on the test database recompile all indexes and maybe reorganize all tables to get all into a defined shape. Then look at the sum of the number of blocks all involved objects use up per solution implemented. Pick the one with the smallest number.

The drawback of this is of course that production activity will change the block count of different objects in different ways (e.g. block usage patterns of tables differ significantly from those of BTree indexes) and so the block counts after reorganization will only give a weak indication of the real numbers after a while of activity.

This is even more the case as part of these tables involve historic data, i.e. multiple values with different time ranges of validity, which makes a certain amount of data dead - from _this_ query's point of view. So the way how this "dead wood" is distributed across objects may have a significant impact (for example when using timestamps as leading columns for an index vs. trailing columns or having them in the table only).

All this does not become easier through the effects of _other_ DML running in parallel which compete for buffer cache space. For a realistic test 11g's Real Application Testing would come in handy where you can capture a particular load and replay it against the test database. Unfortunately I cannot simulate the load right now because 11g is not installed and is not an option, load is highly client application and traffic dependent, both things outside of my control and creating a load capturing and replay framework would take a significant amount of time.

Now, what do you think? What other options do I have? Did I overlook something? Thanks in advance!

Kind regards

        robert Received on Wed Nov 26 2008 - 04:45:32 CST

Original text of this message