Oracle SQL Plan
Oracle Tips by Burleson Consulting
These tips on Oracle SQL Plan
Management are excerpted from
the book "Oracle
11g New Features" by Rampant TechPress.
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
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.
- 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
- 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
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
- 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.
- 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
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)
- 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
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
for 30% off directly from the publisher.