Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Donald K. Burleson

Oracle Tips


Oracle SGA Instance Tuning


The increasing sophistication and flexibility of Oracle10g offers new challenges to the database administrator with regard to determining the optimal size of each System Global Area (SGA) region.  Making the proper decisions required to efficiently utilize RAM resources can mean the savings of millions of dollars.


This chapter will address the following areas of Oracle instance tuning:

§       Operating System configuration: There are many settings on the server that affect the performance of the Oracle database.

§       Oracle instance parameters: Oracle has over 250 documented and over one hundred undocumented instance configuration parameters.

§       Oracle data caching: Properly defining data buffer caches (KEEP pool, multiple data buffers) has a tremendous impact on reducing disk I/O.

Instance Tuning comes first!

Some beginners advocate tuning individual SQL statements before optimizing the instance parameters and this can be a huge waste of effort.  Changing instance parameters after SQL tuning can undo a lot of hard work.


Instance tuning is the process of determining the optimal settings for over 250 instance initialization parameters.  Some instance parameters (optimizer_mode, optimizer_index_caching) control how Oracle configures itself to process and optimize SQL statements, and these parameters must be pre-set to optimize the bulk of the SQL statements, before any individual SQL tuning takes place.


If we tuned the individual SQL statements before we determined the instance parameter settings, all of our hard work would have to be re-done.


For example, we can sample v$bh (and use AWR and STATSPACK tables) to tell us the average amount of index segments in the data buffers, and re-set optimizer_index_caching from it’s default value of 0.


From the Oracle 10g performance tuning guide we see advice on setting optimizer_index_caching:


“OPTIMIZER_INDEX_CACHING - This parameter controls the costing of an index probe in conjunction with a nested loop. The range of values 0 to 100 for OPTIMIZER_INDEX_CACHING indicates percentage of index blocks in the buffer cache, which modifies the optimizer's assumptions about index caching for nested loops and IN-list iterators. A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop accordingly. Use caution when using this parameter because execution plans can change in favor of index caching.”


Here, the Oracle 10g Reference suggests that the DBA should set this instance parameter according to average system load:


“The cost of executing an index using an IN-list iterator or of executing a nested loops join when an index is used to access the inner table depends on the caching of that index in the buffer cache. The amount of caching depends on factors that the optimizer cannot predict, such as the load on the system and the block access patterns of different users. 


You can modify the optimizer's assumptions about index caching for nested loops joins and IN-list iterators by setting this parameter to a value between 0 and 100 to indicate the percentage of the index blocks the optimizer should assume are in the cache.”


Assume for the moment that we took the default value for optimizer_index_caching, and spent 10 weeks tuning 5,000 SQL statements (using hints, adding histograms, etc.).  Later on, we noticed that the default value of optimizer_index_caching is zero, meaning that the CBO assumes that there are no index data blocks in the cache.  Inspections of x$bh confirm that an average of 80% of all indexes are in the data buffer, a factor that will greatly influence the CBO’s decision about the speed of index vs. table scan access.


We set optimizer_index_caching to our real-world value, and suddenly thousands of SQL execution plans change, undoing all of our hard work.


The moral:  Always set good baseline values for instance parameters BEFORE tuning any individual SQL statements.





This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

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




Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.