Tag Archives: SQL plan management

Hints , Outlines, SQL Profiles & SQL Plan Management Compared

My last blog was comparison of DataGuard, GoldenGate, Streams and RAC.  While writing my previous blog , the idea of comparing Hints , Outlines, SQL Profiles &  SQL Plan Management stuck me.  I am kind of in a compare-maniac mode and here   goes some comparison of some key features.  I also think  you can understand technology better when you compare with something you know.

I will add more content to this blog as I get more time. The table format had to be changed to fit the data.

Hints Optimizer is not making decisions regarding the execution plan, Instead you are. The pros are that it provides stable execution plan and also lets you tune vendor client applications without rewriting the SQL. The cons being that you might end up with sub-optimal execution plan. You also may not be able to take advantage of newer optimizer logic as you upgrade.
Outlines Collection of hints stored in OL$ , OL$HINTS and OL$NODES tables. Oracle uses this information in the table to come up with the execution plan.
SQL Profiles Conceptually SQL profiles and outlines are same. Just like outlines, SQL profiles are collection of hints. The main difference between  outlines and  SQL profiles is Outlines have limited  artificial intelligence embed into the feature compared to SQL profiles which means SQL profile may wander  around from the original  purpose  of  “providing  stable plan”  based on additional  data collected.  As always data distribution is the key culprit (generally).
SPM SPM or SQL Plan management support plan stability by kind of guarantying that your query performance will not degrade because of change in execution plan SPM’s also support evolution of execution plan.
Oracle Version
Hints Since Oracle 7
Outlines Since Oracle 9i
SQL Profiles Since Oracle 10g
SPM Since Oracle 11g
Lock Execution Plan
Hints Varies, Oracle can ignore hints
Outlines Yes, Most of the time
SQL Profiles No,  May deviate from  original intended plan based on OPT_ESTIMATE or  COLUMN_STATS or TABLE_STATS
How to create?
Hints Not Applicable
SQL Profiles DBMS_SQLTUNE, SQL  Tuning Advisor, AWR
How to Use?
Hints /*+ hints */
Outlines Set USE_STORED_OUTLINE at session or system level.This  is not an init.ora parameter
SQL Profiles Enable/Disable the SQL profile
SPM Set OPTIMIZER_USE_SQL_PLAN_BASELINES at system or session level
Support  for Private plans
Hints N/A
Outlines YES
SQL Profiles N/A
Hints None
Outlines YES, Additional checks for every statement Oracle parses.
SQL Profiles YES, Minimal
Support for Multiple Executions at any point in time
Hints NO
Outlines NO
SQL Profiles NO
Use unstable execution plans.
Hints YES
Outlines YES
SQL Profiles YES


Find if SPMs are being used

Oracle provides multiple  ways  to implement plan stability  starting with Outlines(8i)  followed by SQL profiles (10g).  As of 11g,  plan stability is implemented with SPM’s or SQL Plan Management. Off course you can use  hints to implement plan stability.  Some DBA’s swear by hints where as some of them are opposed to hints as they do not let the optimizer to choose  most effective execution plan.  Without going into the merits of one feature over the other, let me discuss about the main purpose of this blog.

If you implement outlines or SQL Profiles or SPM’s, you  really want a way to ensure that  optimizer is behaving as it is supposed to.  Unfortunately with, there is no  easy way to find if SPMs are being used or not. The  LAST_EXECUTE column of  DBA_SQL_PLAN_BASELINES  view does not get updated always as documented.  As per documentation LAST_EXECUTED column provides the timestamp when the plan baseline was last executed.

Here is a specific case scenario where  time stamp of LAST_ACTIVE_TIME column from V$SQL  is 11-AUG-2011 14:36:09 where as LAST_EXECUTE column of  DBA_SQL_PLAN_BASELINES  is 08-AUG-11

SQL> select sql_id , SQL_PLAN_BASELINE,LAST_LOAD_TIME,LAST_ACTIVE_TIME from v$sql where sql_id =’fmg67gm9j2skh’;

————- —————————— ——————- ——————–
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:32:18 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 14:36:09
fmg67gm9j2skh SQL_PLAN_7xaxz3w5hd4fbd6e420b9 2011-08-11/14:36:09 11-AUG-2011 15:17:40

SQL> select LAST_EXECUTED , plan_name from dba_sql_plan_baselines where plan_name=’SQL_PLAN_7xaxz3w5hd4fbd6e420b9′;

————————————————————————— ——————————
08-AUG-11 PM SQL_PLAN_7xaxz3w5hd4fbd6e420b9

We  opened an SR with Oracle. Oracle got back to us  stating that we are hitting Bug 8864741 and  provided some workaround to query SQLOBJ$. However even this table was NOT getting  updated.  So until this bug is fixed, the only fool proof way is tracing.

SPM(SQL plan management) Purging

Oracle11g has introduced a lot of very good features.  It is very important to understand the default purging policies s of these features.  I will discuss  SPM purging policies in the blog

SPM Purging Policy

1. Plan Retention

SPM purging Policy for plan retention:  The default is 53 weeks. The default unused plan retention period is one year and one week, which means a plan will be automatically purged if it has not been used for more than a year and maximum supported is 10 years.

Example:  BEGIN  DBMS_SPM.CONFIGURE(    ‘plan_retention_weeks’,53); END;

2. Space Usage

SPM purging  for space usage:  The default space for  SPM is no more than 10% of the size of SYSAUX tablespace.   You can allocate up to 50% of the space for SPM in SYSAUX tablespace.

Example: BEGIN  DBMS_SPM.CONFIGURE(‘space_budget_percent’,30);END;