RE: Stored outline not being used all the times...

  • From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
  • To: <binhpham15@xxxxxxxxxxx>, <Rich.Jesse@xxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 8 Dec 2006 11:41:33 -0700

Just a guess here:  A "Stored Outline" isn't really a fixed execution
plan as some may think it to be.  It is really just a set of hints that
are automatically applied to the CBO when it parses the query.  So, the
plan could very easily change if there was some change to the
environment that made the hints invalid.  For example, if there is a
stored outline hint to use a certain index, but then that index gets
dropped - I believe the query would still run but obviously would have
to use a new explain plan that didn't include the missing index.
 
If the v$sql.outline_category shows your outline category as you said it
does, then it's probably true that the CBO did in fact apply the stored
outline, but for some reason, the resulting plan just wasn't the same.
Could you post the old & new plans?  I'm curious if a 10053 trace would
shed some light on it.  I've never run one on a stored outline query
before.

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Binh Pham
Sent: Friday, December 08, 2006 10:45 AM
To: Rich.Jesse@xxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Stored outline not being used all the times...



Jesse is correct.  I've seen multiple rows frequently with no mis-match.
A mystery. It is surprising that I've not seen any reply from the guru's
on this topic that I've posted now and before on stored outlines.  



Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

Other related posts: