Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

Tweaking Oracle SQL Performance Parameters

January 26, 2015 - Donald Burleson
 

Oracle Corporation has invested millions of dollars in making the cost-based SQL optimizer (CBO) one of the most sophisticated tools ever created. The job of the CBO is to always choose the most optimal execution plan for any SQL statement.

However, there are some things that the CBO cannot detect, which is where the DBA comes in. The types of SQL statements, the speed of the disks and the load on the CPUs, all affect the "best" execution plan for a SQL statement. For example, the best execution plan at 4:00 A.M. when 16 CPUs are idle may be quite different from the same query at 3:00 P.M. when the system is 90 percent utilized.

Despite the name "Oracle", the CBO is not psychic, and Oracle can never know, a priori, the exact load on the Oracle system. Hence the Oracle professional must adjust the CBO behavior periodically. Most Oracle professionals make these behavior adjustments using the instance-wide CBO behavior parameters such as optimizer_index_cost_adj and optimizer_index_caching.

However, Oracle does not recommend changing the default values for many of these CBO settings because the changes can affect the execution plans for thousands of SQL statements.

Here are some of the major adjustable parameters that influence the behavior of the CBO:
 

  • optimizer_index_cost_adj: This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the cheaper the cost of index access.
     
  • optimizer_index_caching: This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops), or to favor a full-table scan.
     
  • optimizer_max_permutations: This controls the maximum number of table join permutations allowed before the CBO is forced to pick a table join order. For a six-way table join, Oracle must evaluate 6-factorial, or 720, possible join orders for the tables.
     
  • db_file_multiblock_read_count: When set to a high value, the CBO recognizes that scattered (multi-block) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.
     
  • parallel_automatic_tuning: When set "on", full-table scans are parallelized. Because parallel full-table scans are very fast, the CBO will give a higher cost to index access, and be friendlier to full-table scans.
     
  • hash_area_size (if not using pga_aggregate_target): The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins.
     
  • sort_area_size (if not using pga_aggregate_target): The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over pre-sorted index retrieval.
Important Note:  Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats.  As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important.  Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms.  For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference". 


The parameter optimizer_index_cost_adj controls the CBO's propensity to favor index scans over full-table scans. As we will see, in a dynamic system, the "ideal" value for optimizer_index_cost_adj may change radically in just a few minutes, as the type of SQL and load on the database changes.

Using optimizer_index_cost_adj

The optimizer_index_cost_adj is the most important parameter of all, and the default setting of 100 is incorrect for most Oracle systems. However, for OLTP systems, resetting this parameter to a smaller value (between 10 and 30) may result in huge performance gains.

10g Note:  In Oracle 10g, you can achieve a similar result to reducing the value of optimizer_index_cost_adj by analyzing your workload statistics (dbms_stats.gather_system_stats).  Also note that utilizing CPU costing (_optimizer_cost_model) may effect the efficiency of plans with lower values for optimizer_index_cost_adj.

Is it possible to query the Oracle environment and intelligently determine the optimal setting for optimizer_index_cost_adj? Let's examine the issue.

The optimizer_index_cost_adj parameters default to a value of 100, and can range in value from 1 to 10,000. A value of 100 means that equal weight is given to index vs. multiblock reads. In other words, optimizer_index_cost_adj can be thought of as a "how much do I like full-table scans?" parameter.

With a value of 100, the CBO likes full-table scans and index scans equally, and a number lower than 100 tells the CBO that index scans are faster than full-table scans. However, even with a super-low setting (optimizer_index_cost_adj=1), the CBO will still choose full-table scans for no-brainers, like tiny tables that reside on two blocks.

optimizer_index_cost_adj.sql
 
 
col c1 heading 'Average Waits for|Full Scan Read I/O'        format 9999.999
col c2 heading 'Average Waits for|Index Read I/O'            format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
 
 
select
   a.average_wait                                 c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100           c5
from
   v$system_event  a,
   v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read'
;


Here is the output from the script in Listing A.

                                                                         Starting
                                                                          Value
                                                                           for
                                                                        optimizer
                                          Percent of      Percent of     index
 Average waits for   Average waits for     I/O waits       I/O waits      cost
 full scan read I/O   index read I/O   for full scans   for index scans   adj

 ------------------ ------------------- ---------------- --------------- -------
       1.473              .289                 .02             .98          20



As you can see, the suggested starting value for optimizer_index_cost_adj may be too high because 98 percent of the data waits are on index (sequential) block access. How we can "weight" this starting value for optimizer_index_cost_adj to reflect the reality that this system has only two percent waits on full-table scan reads (a typical OLTP system with few full-table scans). As a practical matter, we never want an automated value for optimizer_index_cost_adj to be less than one or more than 100.

Also, these values change constantly, As the I/O waits accumulate and access patterns change, this same script may give a very different result at a different time of the day.


Optimization

The Oracle Cost-based SQL optimizer is one of the world's most sophisticated software achievements, but it is the job of the Oracle professional to provide valid statistics for the schema and understand how the Oracle parameters affect the overall performance of the SQL optimizer. Remember, suboptimal SQL execution plans are a major reason for poorly performing Oracle databases, and because the CBO determines the execution plans, it is a critical component in Oracle optimization.


Additional Information
For more information, see Don Burleson's book Oracle Tuning: The Definitive Reference by Rampant TechPress. For SQL tuning scripts, Mike Ault, one of the world's most widely-read Oracle experts, has released his complete collection of more than 450 Oracle scripts, covering every possible area of Oracle administration and management.


This article shows you the dynamic nature of an active database and demonstrates the value of being able to dynamically change important parameters as the processing load on the system changes. 
 
 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.