Faulty Quotes 1 – OPTIMIZER_INDEX_COST_ADJ

6 12 2009

December 5, 2009

(Forward to the Next Post in the Series)

There are several initialization parameters, some of which are hidden (all hidden parameters begin with an underscore character and should not be changed without authorization from Oracle support), which control memory utilization, execution plan costing, latch spin behavior, maximum I/O size for multiblock reads from disk, etc. It is easy to fall into a pattern of blindly changing the initialization parameter without identifying the actual source of the problem for which the initialization parameter change is expected to magically correct. The approach of blindly changing the initialization parameters without understanding the scope/purpose of the parameters nor the source of the problem is apparently encouraged by various Oracle books, official looking web pages, and web discussion threads.

One such parameter that is frequently abused is OPTIMIZER_INDEX_COST_ADJ.  This parameter specifies the percentage of the calculated index cost to retain.  A value of 1 for OPTIMIZER_INDEX_COST_ADJ is a bad idea, as it not only makes index access paths appear to be 1/100 times as expensive (1% of the original cost) which will drive the use of indexes, but also potentially causes the wrong index to be used if two or more indexes have the same (rounded) calculated cost.

Examples quotes recommending low values for this parameter, even in 10g R1 and above:

praetoriate.com/t_op_sql_index_behavior.htm and
http://books.google.com/books?id=4xEozkbvxboC&pg=RA1-PA359#v=onepage&q=&f=false

“The optimizer_index_cost_adj parameter is an initialization parameter that can be very useful for SQL tuning. It is a numeric parameter with values from zero to 1,000 and a default value of 1,000.”

praetoriate.com/t_op_sql_index_behavior.htm

“If you are having slow performance because the CBO first_rows mode is favoring too many full-table scans, you can reset the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans.”

http://books.google.com/books?id=TuzniwcsNtUC&pg=PT771#v=onepage&q=&f=false

“Unfortunately, the optimizer might do more full table scans as a result of setting DB_FILE_MULTIBLOCK_READ_COUNT (you don’t want this behavior), so you may also need to set the OPTIMIZER_INDEX_COST_ADJ to a number, such as 10, to eliminate this problem and drive the use of indexes.”

http://books.google.com/books?id=omq9aRx8s0EC&pg=PT171#v=onepage&q=&f=false

“If the use of DB_FILE_MULTIBLOCK_READ_COUNT starts to cause too many full table scans (since the optimizer now decides it can perform full table scans much faster and decides to do more of them) then set OPTIMIZER_INDEX_COST_ADJ between 1 and 10 (I usually use 10) to force index use more frequently.”

oracle-training.cc/oracle_tips_opq.htm

“The default value for optimizer_index_cost_adj is 1,000, and any value less than 1,000 makes the CBO view indexes less expensive. If you do not like the propensity of the CBO first_rows mode to favor full-table scans, you can lower the value of optimizer_index_cost_adj to 10, thereby telling the CBO to always favor index scans over full-table scans.”

http://books.google.com/books?id=bxHDtttb0ZAC&pg=PA566#v=onepage&q=&f=false

“The most important parameter is the optimizer_index_cost_adj, and the default setting of 100 is incorrect for most Oracle systems.  For OLTP systems, resetting the parameter to a smaller value (between 10 and 30) may result in huge performance gains as SQL statements change from large-table full-table scans to index range scans.”

http://willgreene.net/siebel/Siebel%20Knowledgebase/Performance%20Tuning%20Guide%20for%20Siebel%20on%20Oracle.pdf

“OPTIMIZER_INDEX_COST_ADJ – Controls the access path selection to be more or less index friendly.  Recommended Value = 1

For Oracle 9i CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is imperative. This will set the optimizer goal for best response time (versus best throughput). Incorrect setting may cause the optimizer to favor full-table scans instead of index access.
• For Oracle 10g/11g CBO setting OPTIMIZER_INDEX_COST_ADJ = 1 is also recommended, although the default setting of 100 will deliver good results in most cases. It is important to understand that in-house tuning of Siebel CRM application was performed with OPTIMIZER_INDEX_COST_ADJ = 1 setting. This means that customers who want to implement OPTIMIZER_INDEX_COST_ADJ = 100 on Oracle 10g/11g will need to allocate extra development time for additional tuning that may be necessary.
Under no circumstances OPTIMIZER_INDEX_COST_ADJ parameter should be set to values other than 1 (Siebel recommended) or 100 (Oracle default on 10g/11g).”

 Below is a link to a test case, which shows that even when retrieving 0.06% of the rows from a 100 million row table it is potentially faster when a full table scan is used, rather than an index range scan (the index clustering factor was very high):
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6c436cee329326ec

Before experimenting with this parameter take a look at the following:
http://books.google.com/books?id=b3DIkYO2gBQC&pg=PA185#v=onepage&q=&f=false

http://richardfoote.wordpress.com/2009/07/08/the-cbo-and-indexes-optimizer_index_cost_adj-part-i/

http://richardfoote.wordpress.com/2009/07/22/the-cbo-and-indexes-optimizer_index_cost_adj-part-ii/

http://richardfoote.wordpress.com/2009/08/20/the-cbo-and-indexes-optimizer_index_cost_adj-part-iii/

http://forums.oracle.com/forums/thread.jspa?messageID=3917291 (Joze Senegacnik)

“The parameter optimizer_index_caching is considered by CBO for IN list and NESTED LOOP operations and should be set. The optimizer_index_cost_adj could be very dangerous unless you really know what you are doing, especially when system statistics is used. It was introduced in 8i, but in later releases one should use system statistics to tell CBO what is the timing difference between single block and multiple block I/O. Jonathan Lewis has written about this on his site.

What I really hate is that someone says: set this parameter to a certain value without considering what kind of system is that and what is even more important: without considering what are the consequences of such setting. It is like taking a wrong medicine.

My personal opinion regarding optimizer_index_cost_adj is that one should leave it at 100 (default) and if you really understand the mechanism behind then you may experiment with it. Personally I would use it only at statement level by using OPT_PARAM hint if this would be really necessary. This way you don’t make a system wide change.”


Actions

Information

4 responses

17 12 2009
Joze Senegacnik

Charles,
Excellent idea to gather this kind of faulty “expert” advices in one place. I expect that I’ll frequently use a link to this post in my writings.

Have a nice time, Joze

17 12 2009
Charles Hooper

Joze,
Thanks for the compliment. There are a number faulty Oracle-related expert quotes floating around the Internet that undoubtedly cause a great deal of confusion. Please feel free to borrow the “Faulty Quotes” idea for your own blog posts.

20 02 2010
panu78

Hi Charles,

Excellent explanation.

One question on this – So as per this article its better to gather system statistics rather than setting the individual parameters for improving the database performance ?

Thanks
Girish

20 02 2010
Charles Hooper

Girish,

Thank you for the compliment.

I think that you will find that the majority of people who have performed testing regarding what happens to access path cost calculations (and the resulting execution plans) will recommend using the system (CPU) statistics to increase the calculated cost of various access paths, rather then using the OPTIMIZER_INDEX_COST_ADJ parameter to decrease (or increase if set to a value greater than 100) the calculated cost of index type accesses.

It is important to collect the system statistics when the server is under a moderate to heavy load in order to improve the accuracy of the statistics. After collecting the system statistics, verify that odd statistic values were not collected, such as MREADTIM set to a value that is lower than SREADTIM, or MBRC set to too high of a value.

See the following for an example of what might happen if MBRC is set to too high of a value:
High Value for MBRC Causes High BCHR, High CPU Usage, and Slow Performance

Jonathan Lewis has a couple of very good articles on his blog regarding system statistics:
System Statistics
System Stats Strategy
Upgrades

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




Follow

Get every new post delivered to your Inbox.

Join 142 other followers

%d bloggers like this: