Welcome to OracleGiants.com!     

Oracle 10g DBA & Business Intelligence Resources.

OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, and OPTIMIZER_INDEX_CACHING

Read this to learn about OPTIMIZER_MODE, OPTIMIZER_INDEX_COST_ADJ, and OPTIMIZER_INDEX_CACHING.

While its very common for DBAs to leave these parameters at their default settings, setting these to non-default values could have a great impact on query usage, specifically in areas where full table scans are occurring.

OPTIMIZER_MODE specifically is a key tuning parameter, as it tells the Oracle optimizer how to focus on tuning goals. The default of ALL_ROWS generally prefers full table scans with MERGE joins as opposed to index reads with NESTED LOOP joins.

OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ are ‘tuning knobs’ that allow a DBA to push Oracle toward index usage or full table scan usage. OPTIMIZER_INDEX_COST_ADJ, for instance, is a percentage based parameter with a default of 100. As it is decreased, the cost of index usage directly decreases. If OPTIMIZER_INDEX_COST_ADJ is set to a value of 80, index usage will ‘cost’ 80% of the normal cost when being evaluated by the optimizer.

 

Use Xattr CloudFiler™ to dedup, encrypt and move your RMAN backups to the cloud.

Recent Posts

This site recommends Burleson Consulting for DBA Support & Consulting.

We are currently paying for accepted Oracle 10g DBA & Business Intelligence articles, scripts, tips & tricks. Send yours to the editor and we will review within three weeks.

Learn more

Editorial submissions & questions: editor@oraclegiants.com

 

 

This site is moderated by an:
Oracle Certified Professional
Oracle ACE

 

  Classic Car Online - The #1 spot for classic and muscle car enthusiats
2010 OracleGiants.com