Oracle Scratchpad

October 24, 2016

Anniversary OICA

Filed under: CBO,Oracle,Performance,Statistics,Troubleshooting — Jonathan Lewis @ 1:00 pm GMT Oct 24,2016

Happy anniversary to me!

On this day 10 years ago I published the first article in my blog. It was about the parameter optimizer_index_cost_adj (hence OICA), a parameter that has been a  source of many performance problems and baffled DBAs over the years and, if you read my first blog posting and follow the links, a parameter that should almost certainly be left untouched.

It seems appropriate to mention it today because I recently found a blog posting (dated 3rd May 2013) on the official Oracle Blogs where the director for Primavera advises setting this parameter to 1 (and the optimizer_index_caching parameter to 90) for the Primavera P6 OLTP (PMDB) database. The recommendation is followed by a fairly typical “don’t blame me” warning, viz: “As with any changes that affect query optimization, it is paramount to TEST, TEST and TEST again. At least these settings are easily adjusted or change back to the original value”.

Here’s a thought, though: setting the optimizer_index_cost_adj to the extreme value 1 is a catastrophic change so don’t suggest it unless you are extremely confident that it’s almost certain to be the right thing to do. If you’re confident that it’s a good idea to reduce the parameter to a much smaller value than the default then suggest a range of values that varies from “ideal if it works, but high risk” to “low risk and mostly helpful”. Maybe a suggestion like: “Primavera P6 OLTP (PMDB) tends to work best with this parameter set to a value in the range of 1 to 15” would be a more appropriate comment from someone in a position of authority.

Here’s another thought: if you work for Oracle you could always contact the optimizer group to present them with your argument for the strategy and see what they think about it. Then you can include their opinion when you offer your suggestion.

For what it’s worth, here’s my opinion: as a general rule you shouldn’t be working around performance issues by fiddling with the optimizer_index_cost_adj; as a specific directive do not set it to 1. If you want to encourage Oracle to be enthusiastic about indexes in general then adjust the system statistics (preferably with a degree of truth). If you need to persuade Oracle that particular indexes are highly desirable than you can use dbms_stats.set_index_stats() to adjust the clustering_factor (and avg_data_blocks_per_key) of those indexes. If you are running 11.2.0.4 or later then you can use dbms_stats.set_table_prefs() to set the “table_cached_blocks” parameter for tables where you think Oracle should be particularly keen on using indexes but isn’t; and if your queries are suffering from bad cardinality estimates because of a pattern of multi-column filter predicates create some column group (extended) statistics.

Why am I so firmly set against setting the optimizer_index_cost_adj to 1 ? Because it doesn’t tell Oracle to “use indexes instead of doing tablescans”, it tells Oracle that every index is just about as good as every other index for almost any query. Here’s a pdf file of an article (formerly published on DBAZine and then on my old website) I wrote over twelve years ago explaining the issue. Various links in the article no longer work, and the data pattern was generated to display the problem in 8i and 9i and you would need to modify the data to display the same effect in newer versions of Oracle – but the principle remains the same.

If you would like to see a slightly newer example of how the parameter causes problems. Here’s a thread dated April 2012 from the OTN database forum where a SYS-recursive query caused a performance problem because the parameter was set 1.

 

7 Comments »

  1. Happy anniversary Jonathan! I’ve learned a lot about CBO from your blog posts.

    Comment by Nenad Noveljic (@NenadNoveljic) — October 24, 2016 @ 1:36 pm GMT Oct 24,2016 | Reply

  2. Happy anniversary to you :-)

    Comment by Ugurcan — October 24, 2016 @ 4:42 pm GMT Oct 24,2016 | Reply

  3. Happy happy anniversary – Keep up the good work!

    Comment by Marco Gralike — October 24, 2016 @ 10:49 pm GMT Oct 24,2016 | Reply

  4. Happy anniversary and many, many more such tens of years to come :):)

    I enjoy a lot reading your posts as well as your books
    and I will never forget the opportunity to have enjoyed your presentations live at ILOUG Technology Days in June 2015.

    Thanks a lot for all what you are doing for the Oracle community :)

    Comment by Iudith Mentzel — October 24, 2016 @ 11:52 pm GMT Oct 24,2016 | Reply

  5. Happy anniversary and thank you for tons of helpful notes!

    Comment by Alberto Gonzalez — October 25, 2016 @ 3:11 am GMT Oct 25,2016 | Reply

  6. Happy Anniversary Jonathan. One of the best Oracle information. I’m a regular visitor to your blog that helps me to educate myself on all new functionalities that Oracle has to offer. Thanks for all the hard work.

    Comment by Aswath Rao — October 26, 2016 @ 6:40 pm GMT Oct 26,2016 | Reply

  7. Thanks for the responses. Much appreciated.

    Comment by Jonathan Lewis — October 27, 2016 @ 10:49 am GMT Oct 27,2016 | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.