SQL Plan history Analysis is not an easy chore.
As the world’s most robust and complex database, Oracle offers something
for everyone, and it can be confusing to optimize Oracle because of the
plethora of features.
One wonderful feature of Oracle is the ability to
have SQL execution plans change when the characteristics of the
underlying table changes. For
shops where a table is huge one day and small the next, of shops where
column distribution vary wildly, dynamic SQL is a Godsend.
However, dynamic SQL changes are not so great for
stable shops where there exists one, and only one optimal execution plan
for any given SQL statement.
To learn more, see my notes on
identifying your SQL philosophy.
Because SQL plans will change whenever we
make changes to the instance, any SQL statement will have a history
of execution plans. Here are some common acts that will change
execution plans:
-
Enabling dynamic sampling
-
Re-analyze the schema statistics (done
automatically, starting in 10g)
-
Changing an optimizer parameter
(optimizer_mode, optimizer_index_cost_adj)
-
Enabling parallelism
This execution plan history can be seen by running
scripts against the STATSPACK (stats$sql_plan and
stats$sql_plan_usage) or the AWR (dba_hist_sql_plan)
tables.
Once you have acquired the SQL_ID for a statement, you can use the
built-in
dbms_xplan.display_awr procedure to see all the
different execution plans for the query.
The dbms_xplan.display_awr allows you to input only a
SQL_ID, and Oracle will show you the of the explain plans for that
SQL_ID, as recorded in the AWR. This
simple query will show changes to SQL explain plan history, if you have
purchased the extra-cost licenses for AWR.
select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
For more detailed scripts,
and free STATSPACK script to monitor SQL execution patterns with details on historical SQL plan analysis, see my book “Oracle
Tuning: The Definitive Reference”.
Oracle 11g has introduced a wealth of new tools to
help us "freeze" optimal SQL execution plan, so that they do not change when the data
changes, tools such as the 11g SQL Performance Analyzer which uses a
scientific approach to run real-world workloads and only change the SQL
execution plans when they are at least 3x faster.
For complete details, see the
11g New Features book.
Also, see these notes from the Oracle CBO group on
SQL plan history analysis., where they coined the term "plan
regression" for changes that result in a sub-optimal execution plan.
While Oracle 11g has tools for tracking explain plans, you can still
choose manual methods, gathering changes to execution plans from
STATSPACK or the extra-cost AWR tables.
