Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Oracle optimizer_index_cost_adj and SQL Performance

Oracle Tips by Burleson Consulting

Updated : 6 May 2013


As you may know, Oracle provides several parameters that can adjust the behavior of the CBO to change the internal costing for different types of operations: 

  • optimizer_index_caching

  • optimizer_index_cost_adj

  • optimizer_max_permutations

  • optimizer_search_limit

Important Note:  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats.  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 

Also, check out v$event_histogram tips  and a script to measure disk speed for sequential vs. scattered reads and estimate a starting value for optimizer_index_cost_adj.

Using optimizer_index_cost_adj

The optimizer_index_cost_adj parameter was created to allow use to change the relative costs of full-scan versus index operations.  This is the most important parameter of all, and the default setting of 100 is incorrect for most Oracle systems.  For some OLTP systems, re-setting this parameter to a smaller value (between 10- to 30) may result in huge performance gains!

10g Note:  In Oracle 10g, you can achieve a similar result to reducing the value of optimizer_index_cost_adj by analyzing your workload statistics (dbms_stats.gather_system_stats).  Also note that utilizing CPU costing (_optimizer_cost_model) may effect the efficiency of plans with lower values for optimizer_index_cost_adj.

Remember, the all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query:

Oracle full-table scan Illustration

Oracle Index access illustration


If you are having slow performance because the CBO
first_rows optimizer mode is favoring too many full-table scans, you can reduce the value of the optimizer_index_cost_adj parameter to immediately tune all of the SQL in your database to favor index scans over full-table scans. This is sometimes a "silver bullet" that can improve the performance of an entire database in cases where the database is OLTP and you have verified that the full-table scan costing is too low.
 

Is re-setting optimizer_index_cost_adj still required in 11g?

There is some debate on whether optimizer_index_cost_adj needs to be changed in 10g and 11g, with conflicting reports from the end-user community. 
 
Some claim that adding specialized CBO statistics (i.e. histograms) will alleviate the need to change the default values for optimizer_index_cost_adj, while others note that numerous bugs and other issues require that optimizer_index_cost_adj be changed in order for all relevant indexed to be invoked.
 
Oracle has created tools such as the 11g SQL Performance Analyzer solely for testing the values of different initialization parameters.
 
Ryan Gaffuri published these notes on  using Optimizer_index_cost_adj.
 
"Oracle support(without knowing anything about my system) is telling me to use the following settings:

OPTIMIZER_INDEX_CACHING = 50
OPTIMIZER_INDEX_COST_ADJ = 5

Tom Kyte's book effective Oracle by Design recommends starting
optimizer_index_caching at my cache/hit ratio and adjusting as needed."

Also, see these notes in Oracle 10g upgrade optimization. 

This document shows some parameters which relieved slow SQL performance after a 10g upgrade by George Johnson:

After our upgrade from 9206 to 10201, we ended up with these parameters making the biggest difference to our slow query performance. The optimizer_index_cost_adj figure was arrived at after about 2 days of testing various troublesome
queries.

optimizer_secure_view_merging = false
_gby_hash_aggregation_enabled = FALSE
optimizer_index_cost_adj = 50
optimizer_index_caching = 0
_optimizer_cost_based_transformation = OFF

We were told by one Oracle guy that if your DB is not a warehouse and it's used batch and OLTP, the bottom four parameters should be set in 10g, without question to ensure the Warehouse components do not affect OLTP type activity!

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 10,000 and a default value of 100. It can also be enabled at the session level by using the alter session set optimizer_index_cost_adj = nn syntax. This parameter lets you tune the optimizer behavior for access path selection to be more or less index friendly, and it is very useful when you feel that the default behavior for the CBO favors full-table scans over index scans.

If your response time is critical, you want to ensure that Oracle always uses index access to fetch rows as quickly as possible, but on some servers, a full-table scan may be faster than index access.  Essentially, the CBO's choice about index vs. full-scan access depends on the relative costs of each type of operation.

The default value for optimizer_index_cost_adj is 100, and any value less than 100 makes the CBO view indexes as less expensive. If you do not like the propensity of the CBO "choose" optimizer_mode parameter to favor full-table scans, you can lower the value of optimizer_index_cost_adj to 20, thereby telling the CBO to give a lower cost to index scans over full-table scans.

Even in Oracle, the CBO sometimes falsely determines that the cost of full-table scan is less than the cost of an index access. The optimizer_index_cost_adj parameter is a great approach to whole-system SQL tuning, but you will need to evaluate the overall effect by slowly resetting the value down from 100 and observing the percentage of full-tale scans.

You can also slowly bump down the value of optimizer_index_cost_adj when you bounce the database and then either use the access.sql or plan.sql scripts or reexamine SQL from the STATSPACK stats$sql_summary table to see the net effect of index scans on the whole database.

Determining a starting value for optimizer_index_cost_adj

We can see that the optimal setting for optimizer_index_cost_adj is partially a function of the I/O waits for sequential reads vs. scattered reads:

select
   a.average_wait                                  c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)*100  c3,
   b.total_waits /(a.total_waits + b.total_waits)*100  c4,
   (b.average_wait / a.average_wait)*100           c5
from
   v$system_event  a,
   v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read';
 

In Oracle 10g and beyond, you can use this script using the dba_hist_system_event table:

col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
 
select
   sum(a.time_waited_micro)/sum(a.total_waits)/1000000 c1,
   sum(b.time_waited_micro)/sum(b.total_waits)/1000000 c2,
   (
      sum(a.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c3,
   (
      sum(b.total_waits) /
      sum(a.total_waits + b.total_waits)
   ) * 100 c4,
  (
      sum(b.time_waited_micro) /
      sum(b.total_waits)) /
      (sum(a.time_waited_micro)/sum(a.total_waits)
   ) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where
   a.snap_id = b.snap_id
and
   a.event_name = 'db file scattered read'
and
   b.event_name = 'db file sequential read';



Here is sample output from a real system showing an empirical test of disk I/O speed.  We always expert scattered reads (full-table scans) to be far faster than sequential reads (index probes) because of Oracle sequential prefetch (see db_file_multiblock_read_count):
 
- scattered read (full table scans) are fast at 13ms (c3)
- sequential reads (index probes) take much longer 86ms (c4)
- starting setting for optimizer_index_cost_adj at 36:


C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
13,824     5,072      13         86         36

 
Here is another variant, showing changes to optimizer_index_cost_adj wait components over time:

set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format
999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format
999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999

select a.snap_id "Snap",
       sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
       sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
       (sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
       (sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
       (sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
from
   dba_hist_system_event a,
   dba_hist_system_event b
where a.snap_id = b.snap_id
and a.event_name = 'db file scattered read'
and b.event_name = 'db file sequential read'
See code depot for full script
/

 
  Snap       Full Scan Read I/O    Index Read I/O    Full Scans    Index Scans
 ---------- ------------------ ----------------- ------------- --------------
       5079               .936              .074         10.14          89.86  
       5080               .936              .074         10.14          89.86  
       5081               .936              .074         10.14          89.86  
       5082               .936              .074         10.14          89.86
       5083               .936              .074         10.13          89.87  
       5084               .936              .074         10.13          89.87  
       5085               .936              .074         10.13          89.87

Here is yet another script that aggregates the I/O wait times by day:

set pages 80
set lines 130
col c1 heading 'Average Waits for|Full Scan Read I/O' format 999999.999
col c2 heading 'Average Waits for|Index Read I/O' format 999999.999
col c3 heading 'Percent of| I/O Waits|for scattered|Full Scans' format
999.99
col c4 heading 'Percent of| I/O Waits|for sequential|Index Scans' format
999.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 99999

select to_char(end_interval_time, 'MM/DD/YYYY') "Date",
       sum(a.time_waited_micro)/sum(a.total_waits)/10000 c1,
       sum(b.time_waited_micro)/sum(b.total_waits)/10000 c2,
       (sum(a.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c3,
       (sum(b.total_waits) / sum(a.total_waits + b.total_waits)) * 100 c4,
       (sum(b.time_waited_micro)/sum(b.total_waits)) /
(sum(a.time_waited_micro)/sum(a.total_waits)) * 100 c5
from dba_hist_system_event a, dba_hist_system_event b, dba_hist_snapshot c
See code depot for full script
/

                                                   Percent of    Percent of optimizer
                                                   I/O Waits     I/O waits    index
           Average Waits for     Average Waits for for scattered for sequential cost
Date       Full Scan Read I/O    Index Read I/O    Full Scans    Index Scans    adj
---------- ------------------ ----------------- ------------- -------------- ---------
08/10/2006               .901              .119         15.63          84.37  13
08/11/2006               .900              .118         15.54          84.46  13
08/12/2006               .898              .113         14.96          85.04  13
08/13/2006               .910              .103         13.77          86.23  11
08/14/2006               .993              .076         10.64          89.36   8
08/15/2006               .991              .076         10.61          89.39   8

The plan.sql script (see code depot from book below) uses the v$sql_plan view and a quickly the reduction in sub-optimal, large-table full-table scans:

                         Full table scans and counts
          Note that "K" indicates in the table is in the KEEP pool.


OWNER          NAME                      NUM_ROWS  C K   BLOCKS  NBR_FTS
-------------- ------------------------  --------- - - -------- --------
SYS            DUAL                                N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE             N K        2   16,178
DONALD         PAGE                      3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                        434 N          8    7,355
DONALD         PAGE_IMAGE                   18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                    476 N K      192    2,087
DONALD         PRINT_PAGE_RANGE                 10 N K       32      874
ARM            JANET_BOOKS                      20 N          8       64

For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

My other notes on optimizer_index_cost_adj include:

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.