Outline, SQL profile and SQL Plan Management(SPM)
July 22, 2011 2 Comments
Outline is the oldest way to fix SQL plan. It based on hints like use_nl etc. It’s a road map. It still can be used in 11.2 although oracle said it’s deprecated. Finally, it is free.
SQL profile is trying to impact CBO by adding cost estimation hints. You need pay extra money to use it.
SQL Plan Management/Baseline is new in 11g. It looks like based on hints too. It’s a big step toward “Prevent issues” but big bugs and overheads are expected. It looks good but cannot be too near.
This document will tell you how to use outline created by SQL with hints, etc. Recommended!
Note:730062.1 – How to Edit a Stored Outline to Use the Plan from Another Stored Outline
This document tell you how to fix the plan by outline if there are multipy plans for a SQL.
Note 445126.1 – How to manually store an outline using V$SQL and DBMS_OUTLN.CREATE_OUTLINE
If you want to migrate outlines,
Note 728647.1 – How to Transfer Stored Outlines from One Database to Another (9i and above)
By creating a startup trigger to set use_stored_outlines to true:
[ID 560331.1] – How to Enable USE_STORED_OUTLINES Permanently
Not recommended, only if you cannot do the tricky by Note:730062.1
Note 726802.1 – Editing Stored Outlines in Oracle10g and Oracle11g
Kerry Osborne blog explained more detail about outline
It is useful with “force_matching” option for SQL with bad plan & NOT using bind variables.
If you want to know it, read this blog. Be careful, profiles created by SQL Tuning Advisor may change plans when stats change. Also SQL profile requires a license.
“A profile and an outline are different things – roughly speaking an outline lists actions and strategies, a profile supplies statistical corrections to the optimizer.” Jonathan Lewis
SQLT (SQLTXPLAIN) – Tool that helps to diagnose SQL statements performing poorly [ID 215187.1]
SQL Plan Management
From Kerry Osborne blog, oracle will try to reproduce the plan in a separate session using the hints in SQL Plan Baselines. If it fails, it will ignore the baseline. This behavior is better than outlines.
The comments in the blogs also explained why alias plays a role in the stored SPB
SPM walks through
Below blog talks about “dark side” of SPM. It’s true. But I’d like to repeat the main ideas and add my comments 1) SPM is using text to match SQLs. That should not cause much issues 2) It’s a bad idea to turn on automatic SPM capture (optimizer_capture_sql_plan_baselines= true) for critical DB. 3) SPM introduced more DBA work although it is a good step toward “Prevent” issues.
SPM BugsBug Fixed Description 11687175 188.8.131.52 High DFS lock handle waits in the database with SPM if FIXED_DATE is set 11719151 184.108.40.206 SQL Plan Management capture causes slowness 9910484 220.127.116.11 SQL Plan Management Capture uses excessive space in SYSAUX Bug 9524143 - Execution plan not loaded into SPM for materialized WITH clause [ID 9524143.8] RECURSIVE MERGE WHEN optimizer_capture_sql_plan_baseline=true DOING FTS [ID 1295054.1] Init.ora Parameter "OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES" Reference Note [ID 567104.1] Init.ora Parameter "OPTIMIZER_USE_SQL_PLAN_BASELINES" Reference Note [ID 567107.1]
SPM run into bugs in a real cases:
I have a ppt about Oracle 11g SQL Plan Management