Oracle Scratchpad

October 17, 2007

System Statistics 3

Filed under: CBO,System Stats,Troubleshooting,Tuning — Jonathan Lewis @ 7:35 pm BST Oct 17,2007

In a recent thread on one of the Oracle Forums, someone asked the question:

So, in general, if systems statistics are in effect, would you or would you not make any adjustments to the optimizer_index_cost_adj and optimizer_index_caching parameters?

Under what circumstance?

This is my reply:

With system statistics in place I would generally expect to leave those parameters to default.

After serious consideration of individual cases I would still expect to leave the optimizer_index_cost_adj to default in almost all cases, but I could be persuaded that the optimizer_index_caching needed to be changed. [Update: The only alternative is to modify the blevel on individual indexes – and I’m not very keen on doing that either]. [Updated again – many years later: introduced (as a backport from 12c) the table_cached_blocks preference as a way of getting better costing for using indexes.]

Both parameters can be given a realistic “physical” meaning, and therefore are valid in some circumstances. The trouble is that they apply at a system level when you really need them to apply at the individual segment level (or even at the “segment when used in specific statement” level).

The optimum case for fiddling with the optimizer_index_caching is where you have some important large-scale queries that the optimizer sees as border-line between nested loop joins and other join mechanisms and therefore switches in an apparently random way between the options. If the nested loop option is obviously (to you) the better choice then setting the index caching parameter may be enough to stabilise plans for these queries without introducing changes, or instability, to the execution plans for other queries.

It is possible to change these parameters at the session level, by the way, so you can introduce variations on a production system by selective use of logon triggers if you really think it might help.

[More on System Statistics]

1 Comment »

  1. Funny you posted this today… I used these two parameters in a demo the other week, since they come up so often. I was giving a talk on 11g New Features, so I took a SQL Tuning Set (a couple of minutes running an APEX app), and started up the SQL Performance Analyzer. It allows you to re-run the tuning set under 1 set of conditions (I chose the default values for those 2 parameters), then change some conditions and re-run the sql in the tuning set. It then compares the results of every statement, including sql that improved ore regressed, and plans that changed. I (and hopefully some people in the audience) found it to be a very effective tool to measure the results of a change, rather than just guessing.

    Sorry to spiral down into an 11g pitch, but the SQL Performance Analyzer rocks!


    Comment by tylermuth — October 18, 2007 @ 4:00 am BST Oct 18,2007 | Reply

RSS feed for comments on this post.

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: