| |
 |
|
11g SQL Plan baseline tips
Oracle Tips by Burleson Consulting
January 16, 2008
|
Question: How is the new 11g SQL plan baseline
feature different from the old way to freeze SQL execution plans with stored
outlines?
Answer: Oracle 11g greatly improved the old
stored outline approach (optimizer plan stability) in Oracle 11g. Using
the 11g SQL Plan baselines, you can allow for the automatic capture of
execution plans. You simply set the parameter
optimizer_capture_sql_plan_baselines=true and Oracle will begin collecting
your baselines automatically.
Then, using the 11g SQL Plan Management tool (implemented
via dbms_spm), you can run several workloads (SQL tuning sets), and
compare the before-and-after SQL performance, choosing only to change execution
plans for SQL that runs at least 3x faster.
 |
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts. |
|
