Last edited 1-Sept-2016
For quite a time I have been a happy user of SPM(SQL Plan Management) to resolve SQL performance issues by using a lazy man’s approach and Oracle capabilities:
in the latest versions Oracle will generate many SQL plans in it’s search of a better SQL plan. All you have to do is to select the one which makes sense and load it into SPM.
Until one day a valid and enabled SPM would stop to enforce the chosen plan. dbms_xplan.display_cursor would show “Sql Plan Directive used for this statement”
Deleting the Sql Plan Directive on the objects concerned has solved the issue.
Apparently, SQL Directives take precedence over SPM.
Here are some answers I’ve got from Oracle:
BEGIN EDIT 1-Sept-2016:
Here are quite interesting thoughts of Jonathan Lewis https://jonathanlewis.wordpress.com/2016/08/02/adaptive-mayhem/#comments
Analyzing 10053 does support his statement “Anything short of a completely specificied plan could be modified by dynamic sampling, so I think an outline would be much more likely to be affected by an SQL directive than an SQL Plan Baseline since outlines tend to be less well specified”.
10053 Trace logs SPD part at the very end of the trace file, meaning SPD takes precedence over – over what ? That’s a good question. SPD must be SQL Plan Directives. Looking into the SPD part of the trace one finds hinted SQL:
SPD: BEGIN context at statement level … ******* UNPARSED QUERY IS ******* SELECT /*+ LEADING (“T4” “T1” “T2” “T3”) FULL (“T4”) USE_HASH (“T3”) FULL (“T3”) USE_HASH (“T2”) FULL (“T2”) USE_HASH (“T1”) FULL (“T1”) */ … … Permutations for Starting Table :0 Join order: T4[T4]#0 T1[T1]#1 T2[T2]#2 T3[T3]#3
Those are SQL Plan Baseline hints. Still, SPD acronym looks suspicious. At the very least, this part of CBO probably considers both SQL Directives and SQL Plan Baselines.
END EDIT 1-Sept-2016
All that said, one thing is clear. IT guy must always ask one simple question: what is the precedence?
In Oracle CBO (Cots Based Optimizer) we have many factors:
Statistics (don’t forget System Statistics)
For quite a time the precedence of factors influencing CBO was rather self-evident and had no need to be documented explicitly. Not any longer.