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.

Definition
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
SPM Yes
How to create?
Hints Not Applicable
Outlines CREATE_STORED_OUTLINE, DBMS_OUTLN.CREATE_OUTLINE AND CREATE OUTLINE
SQL Profiles DBMS_SQLTUNE, SQL  Tuning Advisor, AWR
SPM OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES , 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
SPM YES
Overhead
Hints None
Outlines YES, Additional checks for every statement Oracle parses.
SQL Profiles YES, Minimal
SPM No
Support for Multiple Executions at any point in time
Hints NO
Outlines NO
SQL Profiles NO
SPM YES
Use unstable execution plans.
Hints YES
Outlines YES
SQL Profiles YES
SPM NO

.

Advertisements

Tagged: , , , ,

%d bloggers like this: