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!

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.

OPTIMIZER_INDEX_CACHING = 50
OPTIMIZER_INDEX_COST_ADJ = 25
_optimizer_index_compute_stats=TRUE ( default FALSE)

So, before change, collect following statistics.

Here are all the columns for the two events form the v$system_event table:

SQL> select * from v$system_event
  2  where event in ('db file sequential read', 'db file scattered read');

EVENT                      TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
-------------------------- ----------- -------------- ----------- ------------
db file sequential read       92814138              0  3787666073   40,8091499
db file scattered read         4349122              0     2348427   ,539977264

Then i inspected database 2. Here, i found the other extreme:

SQL> select round((select average_wait
 from v$system_event
where event='db file sequential read')
/
(select average_wait
 from v$system_event
where event='db file scattered read')
 * 100) as starting_point
from dual;

STARTING_POINT
--------------
             0

Then i looked into the v$system_event table (i thought, perhaps timed_statistics is disabled), but:

SQL> select * from v$system_event
  2  where event in ('db file sequential read', 'db file scattered read');

EVENT                        TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
---------------------------- ----------- -------------- ----------- ------------
db file sequential read        119504401              0     7598589   ,063584177
db file scattered read         368384847              0  1,1029E+10   29,9391977

So the ratio is correct (0.063584177 / 29.9391977 = .0021).

Note: If "db file sequential read" events are more, then more index scans are happening. Sometimes FTS is better than index scan in some of the scenarios. In the above ratio, 0 means index scan and FTS are balanced.

Note: Test in Pre-prod before move to prod.

Here are changes before and after:

-- before ( heavy performance issues)
SQL> show parameter optimizer_index_cost_adj;
optimizer_index_cost_adj             integer                 100
SQL> show parameter optimizer_index_caching;
optimizer_index_caching              integer                  0
SQL> 

-- after ( performance issue resolved)
SQL> show parameter optimizer_index_cost_adj;
optimizer_index_cost_adj             integer                  10
SQL> 
SQL> show parameter optimizer_index_caching;
optimizer_index_caching              integer                  50
SQL> 

But, it may not help always. you should have a proper calculation to proceed.

-- Calculation

SQL> select average_wait from gv$system_event where event='db file sequential read';

AVERAGE_WAIT
------------
         .04
         .04
         .03

SQL> select average_wait from gv$system_event where event='db file scattered read';

AVERAGE_WAIT
------------
         .07
         .08
         .08

Here the formula is : 
OPTIMIZER_INDEX_COST_ADJ = 
select round((select average_wait 
from gv$system_event 
where event='db file sequential read') 
/ 
(select average_wait from gv$system_event where event='db file scattered read') * 100) 
from dual; 


SQL> select 
  c.t as index_block_cache_count,
  i.t as index_block_total_count,
  (c.t/i.t)*100 as start_optimizer_index_caching
from 
  (select sum(blocks) as t from dba_segments where segment_type = 'INDEX') i,
  (select count(*) as t from v$bh v, dba_objects o
  where v.objd = o.object_id and o.object_type = 'INDEX') c
;

INDEX_BLOCK_CACHE_COUNT INDEX_BLOCK_TOTAL_COUNT START_OPTIMIZER_INDEX_CACHING
----------------------- ----------------------- -----------------------------
                   7860                 3405720                    .230788203


SQL> 

Here I adjusted one of the production for "show parameter optimizer_index_cost_adj" as 1 and "optimizer_index_caching" as 50 and helped to improve the performance a lot.

But remember, you must check other below parameter's also:

cursor_sharing

open_cursors   

session_cached_cursors 

All the above parameters are based on the requirement and application prospective.

In one of my OLAP project, I used below setups for best of use:

cursor_sharing=EXACT (default)

open_cursors=500 (default=50) 

session_cached_cursors=2000 ( default=50) 

Here now we can see how we can find what exact usage of "open_cursor" and "session_cached_cursors" values:

-- View the existing value set

SQL> show parameter cursors;

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
open_cursors                         integer                          1000
session_cached_cursors               integer                          50
SQL> 



-- Find the current usage and value set for "open_cursors" and "session_cached_cursors":

select 'session_cached_cursors'  parameter,lpad(value, 10)  value, decode(value, 0, '  n/a', to_char(100 * used / value, '999990') || '%')  usage
from   (select max(s.value)  used
from   v$statname  n,v$sesstat  s
where  n.name = 'session cursor cache count'
and    s.statistic# = n.statistic#),
(select value from   v$parameter where  name = 'session_cached_cursors')
union all
select 'open_cursors',lpad(value, 10),to_char(100 * used / value,  '99990') || '%'
from   (select max(sum(s.value))  used
from   v$statname  n,
v$sesstat  s
where  n.name in ('opened cursors current', 'session cursor cache count')
and    s.statistic# = n.statistic#
group by s.sid),
(select value from   v$parameter where  name = 'open_cursors');


PARAMETER              VALUE                         USAGE
---------------------- ----------------------------- ----------
session_cached_cursors         50                    876444%
open_cursors                 1000                    43822%

Find percentage distribution of the total parse calls between hard and soft parses and also reports the percentage of total parse calls satisfied by the session cursor cache.

SQL> 
select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from   (select value calls from v$sysstat where name = 'parse count (total)'),
(select value hard  from v$sysstat where name = 'parse count (hard)'),
(select value sess  from v$sysstat where name = 'session cursor cache hits');


CURSOR_CACHE_HITS    SOFT_PARSES          HARD_PARSES
-------------------- -------------------- --------------------
          143.52%        -48.90%                5.38%

SQL> 

-- current session cursor cache count

select s.value used, count(1)
from  v$statname  n,
v$sesstat  s
where n.name = 'session cursor cache count'
and   s.statistic# = n.statistic#
group by s.value
order by s.value;

     USED   COUNT(1)
---------- ----------
         0         65
         1         10
         2         13
         3          3
         5          1
         7          2
         8          1
         9          4
        10          1
        12          1
        15          2
        17          3
        20          2
        21          1
        25          1
        29          1
        30          1
        69          1
        70          1
        90          1
       109          1
       110          1
       342          1
       362          1
       581          1
      7212          1
    437871          1

27 rows selected.

SQL> 

Note: increase of "session_cached_cursors" parameter value will increase size of share_pool.

To know more on "session_cached_cursors" Click here.