Oracle Scratchpad

October 24, 2006

Optimizer_index_cost_adj

Filed under: CBO,Infrastructure,Oracle,Performance,Site History,System Stats — Jonathan Lewis @ 2:44 pm BST Oct 24,2006

[Updated 28th November 2011] – just after the fifth anniversary – to mark this as the first post of the Oracle Scratchpad.

A recent post on one of the OTN Database General forum pages asked about the effect of having the parameter optimizer_index_cost_adj set when you enable system statistics (also known as CPU costing).

I was quite surprised to see that I hadn’t mentioned this in my book (Cost Based Oracle Fundamentals) – even though there was a note in Chapter 4 comparing the effects of system statistics with the use of this parameter.  So here’s a brief answer.

Remember that optimizer_index_cost_adj is just a number that gets used in the arithmetic for calculating a cost. It still gets used (apparently in exactly the same way) even if you have enabled system statistics.

Historically people used to give an informal description of the parameter as “this is the percentage of the cost of a multi-block read that should be given to a single block read when reading by an indexed access path”. But when you enable system statistics, it no longer makes sense to use this informal description, since part of the purpose of the system statistics is to show Oracle exactly how much longer a multi-block read takes compared to a single block read. So you probably need a new informal description – which is this:

When system statistics are enabled, for values up to 100 the parameter optimizer_index_cost_adj indicates the percentage of table blocks that are NOT likely to be in the cache when read through an indexed access path.

Like the earlier informal definition, this one suffers from a number of inaccuracies when you get into the details – but it is a reasonable statement that gives the right impression of the impact on the optimizer’s cost calculations of setting the parameter.

7 Comments »

  1. Hmmm, so in a sense it becomes complementary to the optimizer_index_caching parameter, being inversely related to table block caching rather than directly related to index caching?

    Comment by David Aldridge — October 30, 2006 @ 3:28 am BST Oct 30,2006 | Reply

  2. RE: David Aldridge / Optimizer_index_caching.

    Correct – optimizer_index_caching indicates the percentage of index blocks that WILL be cached; optimizer_index_cost_adj indicates the percentage of table blocks that will NOT be cached.

    Of course, optimizer_index_caching applies only to branch blocks and only for nested loop joins and in list iterators and, at the end of the day, it’s just a number for which we invent a meaning.

    Comment by Jonathan Lewis — October 30, 2006 @ 7:12 am BST Oct 30,2006 | Reply

  3. I’m an Oracle newbie, but I’m so excited you have a blog now. I read TKyte and Oracle Sponge all the time. I just picked up your CBO Fundementals book and have found it very useful for the work I’m doing.

    Thanks!

    Comment by mapgirl — October 30, 2006 @ 3:49 pm BST Oct 30,2006 | Reply

  4. I’ve just been browsing my website, and realised that there was a relevant note about CPU costing that could be read in conjunction with the above, see:
    http://www.jlcomp.demon.co.uk/system_stats.html

    Comment by Jonathan Lewis — January 28, 2007 @ 1:42 pm BST Jan 28,2007 | Reply

  5. Hello Jonathan:

    So if I have a lot of SQL queries that are using correlated subqueries with many table joins that also have IN conditions, would it benefit me to tune this parameter to optimize my SQL query performance? I read about tuning this parameter per Metalink Note 243269.1 and was wondering how high of a value I would want to set this to with these type of poorly tuned complex queries.

    Thanks
    Ben Prusinski

    Comment by Ben Prusinski — April 30, 2008 @ 5:25 pm BST Apr 30,2008 | Reply

  6. Ben,

    I’ve just read the note – and didn’t really like it. What it demonstrates isn’t really “tuning” a parameter, it’s fiddlnig with it until one particulary query does what you want.

    Admittedly, it’s quite simple in their example to calculate a suitable value – but as the final comment states, they’re just demonstrating the impact of fixing one query using one particular index.

    I think the note is more about demonstrating the impact of the parameter, rather than trying to tell you how to pick a value.

    If you’re not using system statistics (CPU costing) then the numbers you need should be designed to help the optimizer understand the speed at which Oracle can get data off disk.

    Ideally you need to use something like Orion (the Oracle tool) or iozone to find out the relative speeds of (the equivalent of) your single block and multiblock I/O read sizes, then set optimizer_index_cost_adj to 100 * “single block read time” / “multiblock read time”.

    For a typical OLTP setup, a value in the region of 30 is likely to be fairly reasonable – though a full regression test is needed to discover if you’re going to get any catastrophic side effects.

    In your case the problem is much more likely to relate to bad cardinality estimates, particularly caused by correlated subqueries and IN list. See for example this note on the “5%” effect of certain types of subquery. Another source of problems is mixing subqueries with ORs.

    Comment by Jonathan Lewis — May 2, 2008 @ 1:36 pm BST May 2,2008 | Reply

  7. Jonathan,

    Thank you for the insight into tuning this parameter- I agree and Metalink is not always 100% correct.
    Like you said in your new book never believe everything that you read. Alas many folks take a so called Oracle expert as gospel without testing things out.

    Right now, I am testing out values with this and a few other settings for test and development systems first to see if any side effects occur. By the way, I am enjoying your Oracle Cost Optimizer book- very mathematical but interesting insights. I can tell that you have a solid math background :-)

    And yes- the SQL queries have a lot of poorly written subqueries with IN and OR lists as well as poorly designed join conditions.

    By the way, I am writing a book as well but on database migrations to Oracle. Even though I am more a DBA than a developer, I enjoy learning the ins and outs on database design and how the optimizer really works.

    I can appreciate the time and effort required to write a quality technology book.

    Cheers,
    Ben Prusinski

    Comment by Ben Prusinski — May 12, 2008 @ 1:21 am BST May 12,2008 | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,507 other followers