Oracle 11g SQL execution plan management tips

Oracle11g Tips by Burleson Consulting

This is a work in progress excerpt from the book "Oracle 11g New Features" by Rampant TechPress.

Inside 11g 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 versus those dynamic environments that want their execution plans to change as optimizer statistics and parameters change.

It all 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 on 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.

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 SQL philosophy

If your shop has relatively static tables and indexes, you may want to adopt the persistent SQL 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 execution plan management

If we examine the evolution of Oracle SQL execution plan (explain plan) management, see see 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.
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.
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.

Lets take a close look at 11g execution plan management and see how it helps lock-down critical SQL execution plans, and tests execution timings before implementing changes.

Oracle 11g enhancements to execution plan management

This new revolution in explain plan management, SPM creates static explain plans for all SQL, thereby removing the risk of changes to parameters or CBO statistics from effecting 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 effect SQL execution (e.g. optimizer_index_caching, db_file_multiblock_read_count)
Object parameters - Changing object parameters can dramatically effect SQL execution plans (e.g. alter table xxx parallel degree 63;".

One of the greatest challenges to the DBA is "freezing" SQL execution plans 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

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

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

Oracle notes that when SPM 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.

SPM 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 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 procedure will extract revised SQL explain plans directly from the library cache.
dbms_spm.load_plans_from_sqlset - This 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:

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.

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

Need an Oracle Health Check?

Do you have bad performance after an upgrade?
Need to certify that your database follows best practices?

BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.