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 


 

 

 


 

 

 
 

Create SQL Plan Baseline Tips

Oracle Database Tips by Donald BurlesonJune 9, 2015

Question:  What is an Oracle SQL plan baseline, and how do I create a SQL plan baseline? Is a SQL baseline the same as swapping execution plans with SQL profiles and optimizer plan stability?

Answer:  An Oracle SQL plan baseline is a series of stored execution plans designed to make the database resilient against degraded performance caused by changes in execution plans.  This is achieved by only using execution plans which are accepted.

There are three techniques for changing SQL statements when is cannot touch the SQL, as is the case with vendor software:

1 - Oracle 11g onwards:  Create a SQL baseline for the correct plan

2 - Oracle10g onwards:  Swap execution plans for SQL Profiles See here.

3 - Oracle9i and earlier:  Swap plans using stored outlines.  See here

When an SQL statement is parsed, the cost-based optimizer produces a decision tree and chooses the execution plan with the lowest estimated cost.  If no matching plan is found within the SQL plan baseline, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest actual cost in terms of execution time.  If automatic capture is enabled, at this point the plan generated by the CBO will be added to the SQL plan baseline and flagged as non-accepted.  A non-accepted plan is a plan which is currently a part of the SQL plan baseline but is not used until it can be proven not to cause degraded performance.

There are two methods to create SQL plan baselines: automatic plan capture and manually loading existing execution plans.  When automatic plan capture is enabled, the plan history for SQL statements is created and maintained using information provided by the optimizer.  This plan history includes relevant information used by the optimizer to reproduce the execution plan, such as bind variables, compilation environment, outline and SQL text.   When an SQL statement is issued, Oracle will check for an SQL plan baseline.   If an SQL plan baseline does not exist, Oracle will create an SQL plan baseline and then execute the associated plan.  If an SQL plan baseline does exist, then it just executes a plan from the SQL plan baseline.   It is recommended that you perform a considerable amount of testing before implementing automatic plans in a production environment to ensure no negative impact on performance exists.

You can manually load plans in conjunction with, or as an alternative to, automatic plan capture.  The manually loaded plans are not verified for performance, but are added as accepted plans to existing or newly created SQL plan baselines.  Plans can be loaded from SQL tuning sets using load_plans_from_sqlset as follows:

declare
     l_plans_loaded pls_integer
begin
     l_plans_loaded := dbms_spm.load_plans_from_sqlset(
         sqlset_name => '
');
end;  

Plans may also be manually loaded from specific SQL statements in the cursor cache using load_plans_from_cursor_cache.  This function has four overloaded methods, allowing statements to be identified by sql_id, sql_text, parsing_schema_name, module and action.  The following identifies the statement using a sql_id:

declare
     l_plans_loaded pls_integer
begin
     l_plans_loaded := dbms_spm.load_plans_from_cursor_cache(
         sql_id => '
');
end;

The return value of each of these functions will indicate the number of the plan loaded by the function call. For more information on SQL plan baselines check out the following helpful links:

 
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.

 


 

 

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.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster