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

 
 Home
 E-mail Us
 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 


 

 

 


 

 

 
 

Oracle SQL Plan Management Tips


Oracle Tips by Burleson Consulting

These tips on Oracle SQL Plan Management are excerpted from the book "Oracle 11g New Features" by Rampant TechPress.


Inside SQL Plan Management (SPM)

Within the world of Oracle SQL optimization, we see two main optimizer philosophies: those who believe that there is one and only one optimal execution plan for their SQL, and those dynamic environments that want their execution plans to change as optimizer statistics and parameters change.  This has spawned different approaches to Oracle SQL plan management.

Successful Oracle SQL plan management depends on the volatility of your data, and the Oracle optimizer has always been sensitive to changes to alterations in metadata statistics (with dbms_stats).  Some scientific applications (e.g. Clintrial) have highly volatile data.  Tables are huge one minute, small the next, and the DBA wants the execution plans to change along with the data.  However, in my experience, over 70% of Oracle shops will not benefit from changes to SQL execution plans.  This is where the DBA has to execute carefully crafted Oracle SQL plan management.

Many shops make the mistake of scheduling a re-analyze of their schema every Sunday, leading to the phenomenon called ?Monday Morning Mayhem", as thousands of execution plans change.  Remember, the only reason to re-analyze CBO statistics is to alter SQL execution plans.

The Persistent Oracle SQL Plan Management Philosophy

If your shop has relatively static tables and indexes, you may want to adopt the persistent SQL plan management philosophy that states that there exists only one optimal execution plan for any SQL statement. Shops that subscribe to this philosophy are characterized by stable OLTP applications that have been tuned to use host variables (instead of literal values) in all SQL queries.

Persistent shops also have tables and indexes whose recomputed statistics rarely change the execution plan for their SQL queries, regardless of how often the statistics are recomputed.

Many persistent shops have all of their SQL embedded inside PL/SQL packages, and the applications will call their SQL using a standard PL/SQL function of a stored procedure call. This insulates all of the SQL from the application programs and ensures that all applications execute identical SQL. It also ensures that all of the SQL has been properly tuned.

The History of Oracle SQL Execution Plan Management

Oracle SQL plan management (also known as the execution plan or Oracle SQL Execution Plan Management) has evolved over time, as seen in these tools:

  • Optimizer plan stability (a.k.a. stored outlines): Stored outlines were cumbersome to manage, and it was very difficult to "swap" execution plans with plan stability.  Swap plans using stored outlines.  See here
  • 10g SQL Profiles: Starting in Oracle 10g, we see the SQL Profile approach, whereby a SQL tuning Set (STS) could be tested as a workload, and Oracle would allow the DBA to implement changes to execution plans.  Swap execution plans for SQL Profiles See here.
  • 11g SQL Plan management: Starting in 11g, we finally see an easy-to-use approach to locking-down SQL execution plans.  The 10g SQL profile approach is deprecated, and uses only two parameters, optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines.  Also see dba_sql_plan_baselines tips.

Let's take a close look at Oracle SQL plan management and see how it helps lock-down critical SQL execution plans, and tests execution timings before implementing changes.

Oracle Enhancements to SQL Plan Management

SPM creates static explain plans for all SQL, thereby removing the risk of changes to parameters or CBO statistics affecting SQL execution plans:

  • Re-analyze - The only purpose of re-analyzing CBO statistics with dbms_stats is to provide new metadata to change SQL execution plans.
     
  • Global parameters changes - Change to instance-wide parameters affect SQL execution (e.g. optimizer_index_caching, db_file_multiblock_read_count)
     
  • Object parameters - Changing object parameters can dramatically affect SQL execution plans (e.g. alter table xxx parallel degree 63;")

One of the greatest challenges to the DBA is "freezing" SQL plan management when migrating to a new release of Oracle, and SPM will help for those shops in 11g or later releases.

No more "Monday Morning Mayhem"

Oracle SQL plan management (SPM) relieves the problem of environmental changes causing thousands of SQL statements to change their explain plan steps. 

By default, the parameter optimizer_capture_sql_plan_baselines is set to FALSE and optimizer_use_sql_plan_baselines is set to TRUE.

Oracle notes that when SQL plan management is enabled, only known and verified plans are used, and all plan changes are automatically verified.  During execution plan verification, only "better" execution plans will be implemented. 

Oracle SQL plan management also offers a new package called dbms_spm and a new DBA view dba_sql_plan_baseline to allow the DBA to manage their SQL plans.

  • dbms_spm.evolve_sql_plan_baseline - This SPM procedure tests a new execution plan against a "verified" plan to determine if the new plan has comparable (or better) execution performance.
     
  • dbms_spm.load_plans_from_cursor_cache - This SPM procedure will extract revised SQL explain plans directly from the library cache.
     
  • dbms_spm.load_plans_from_sqlset - This SPM procedure allow the DBA to take pre-tested execution plans from a SQL tuning Set (STS), after running a workload test, and load them for production use. 

See these related notes on SQL Plan Management:

 
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 Excel
 
Oracle performance tuning software 
 

 

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 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.