Init paras

One of the biggest challenges I encountered during this research was generating conditions under which the Cost Based Optimizer (CBO) would behave in a consistent, predictable fashion. Stats played a major role here, but once I could eliminate them from the equation, I had particular problems persuading the CBO to use an index to perform a query (without the use of hints). The CBO would full-table-scan (FTS) at every opportunity, even though this was demonstrably a slower route. The answer lay in the init.paras which cause the CBO to FTS if the stats show the number of rows returned would be 5% (yes, only 5%) or more of the table.

The default init.paras for

optimizer_index_caching = 0

optimizer_index_cost_adj = 100

optimizer_mode = all_rows

are a set of ‘safe’ values and are adequate for most small/medium size database and designed for datawarehouse application but are ‘unsuitable for most OLTP applications’

By changing these values via

alter session set optimizer_index_caching = 90;

alter session set optimizer_index_cost_adj = 25;

alter session set optimizer_mode = first_rows_100;

The propensity for the CBO to FST so readily is lessened and the use of indexes increases without the need to resort to hints.

This proved to be one of the single most important factors in making queries against the view v_bet_union_vb perform in an acceptable manner.

ref : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:173561100346113797

http://www.dba-oracle.com/oracle_tips_cost_adj.htm

http://www.dba-oracle.com/art_builder_sql_parm.htm

I have considered proposing the option of changing the init.para of all of the OLTP database to these values but have rejected the idea (for the moment) on the basis that this will affect ALL database operations in an unpredictable way.

Ref http://www.praetoriate.com/t_optimizer_index_caching.htm