Re: Enforcing plan via SQL plan baseline
Date: Thu, 2 Feb 2012 19:51:48 -0600
Message-Id: <6FFBC1D7-D4C5-41A7-9FEE-4AFFAD10CE51_at_enkitec.com>
By the way, I did a blog post some time ago which shows the 10053 output (it's a bit dated as it was from an 11gR1 database, but 11gR2 is still basically the same). Here's the url for the post. Hope that helps.
http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Feb 2, 2012, at 7:41 PM, Kerry Osborne wrote:
> Try generating a 10053 trace (you'll have to flush the statement so that it will get re-parsed). Then check the trace file to see if the statement is recognized as being in SPM (just search for SPM). You should see a section where is says whether the statement is found in SPM or not. If the statement is found the trace will list the hints applied and the plan it came up with - then it will tell you if the plan matched what it was expecting. It does not use the normal plan_hash_value listed in V$SQL to make this determination by the way. If the plan doesn't match (which is possible) then it will ignore the baseline. Hopefully this will give you enough info to figure out what's going on.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 02 2012 - 19:51:48 CST