SQL directives beat SPM (or not?)

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[1]: 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)

Dynamic Statistics



SQL Profiles

SQL Patches


SQL Directives

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.







This entry was posted in Uncategorized. Bookmark the permalink.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s