Outline, SQL profile and SQL Plan Management(SPM)

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.

Outline

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
http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/

SQL profile

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.
http://kerryosborne.oracle-guy.com/2009/04/oracle-sql-profiles/
http://kerryosborne.oracle-guy.com/2009/07/why-isnt-oracle-using-my-outline-profile-baseline/
“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
http://kerryosborne.oracle-guy.com/2011/01/licensing-requirements-for-sql-profiles/
http://kerryosborne.oracle-guy.com/2010/07/sqlt-coe_xfr_sql_profilesql/
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
http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/

SPM walks through
http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php
http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/
http://jonathanlewis.wordpress.com/2011/01/07/sql-plan-baselines/

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.
http://intermediatesql.com/oracle/oracle-11g-sql-plan-management-the-dark-side-of-spm-part-4/

SPM Bugs

Bug	      Fixed	    Description
11687175 	12.1.0.0 	High DFS lock handle waits in the database with SPM if FIXED_DATE is set
11719151 	12.1.0.0 	SQL Plan Management capture causes slowness
9910484 	12.1.0.0 	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:
http://orastory.wordpress.com/2011/03/01/sql-baseline-capture-bu/

I have a ppt about Oracle 11g SQL Plan Management

About these ads

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

2 Responses to Outline, SQL profile and SQL Plan Management(SPM)

  1. Pingback: SQL Plan Management handy commands « Oracle Explorer: Standing on the shoulders of giants

  2. Pingback: How to use hinted SQL to fix bad SQL plan | Alex Zeng's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: