In traditional regression analysis, past points are
used in a predictive model to forecast future performance.
In Oracle11g, SQL plan regression refers to the process of
tracking changed to execution plans and ensuring that the SQL does not
"regress" into a sub-optimal execution plan when changes are made to
optimizer parameters of CBO statistics.
One perplexing issue with Oracle is the dynamic
nature of Oracle SQL, and the optimizer's propensity to change execution
plans whenever CBO statistics change.
This has been dubbed "Monday Morning Mayhem" because some SQL
explain plans will change for the worst.
Inside Oracle SQL plan regression
CBO group notes that SQL plan regression of the processes of
"reverting" to a sub-optimal execution plan.
"this best-cost plan will not be used but instead
added to the statement's plan history for later verification.
In other words, the optimizer will use a known plan
from the SQL plan baseline instead of a new and hitherto unknown plan."
The SQL plan regression feature operates on the
processes of historical testing of SQL execution plans.
For details see my notes
on SQL history
analysis in 11g.
When invoking dbms_xplan.display_cursor, you can
tell when SQL plan regression is being used by this message:
- SQL plan baseline SYS_SQL_PLAN_fcc170b0a62d0f4d used for this
If you purchased the extra-cost AWR, you can use
the dbms_xplan.display_awr procedure allows you to input only a
SQL_ID, and Oracle will show you the of the explain plans for that
SQL_ID. This simple query will show changes to SQL explain plan history,
if you have purchased the extra-cost licenses for AWR.
select * from
In sum, Oracle SQL plan regression is a method that uses real-world
empirical data to ensure that the optimal SQL execution plan is always
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.