OPTIMIZER_INDEX_CACHING & OPTIMIZER_INDEX_COST_ADJ

One thing is sure that the default value
0 and 100 for OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ respectively are set
for Data Warehouse System.

If your system is OLTP, then these 2 parameters values should be changed.  I would test the
system with different values before choosing the correct values.

Setting 90 to OPTIMIZER_INDEX_CACHING and 10 (or 15) to OPTIMIZER_INDEX_COST_ADJ will
perform good for OLTP system.

If you collect system statistics, then these 2 parameters play less importance role in deciding the
execution plan.

http://tamilselvang.blogspot.in/2011/11/optimizerindexcaching.html
https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams159.htm#REFRN10142

Advertisements

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