Bind variables peeked on first parse, but not on subsequent hard parses

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: Oracle-L Freelists <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 24 Nov 2009 10:58:44 -0700

Anyone seen this before?


1)      The first time a query is parsed, the bind variables are peeked and the 
execution plan is good (nothing odd here).

2)      After some time passes, the plan ages out of v$sql_plan, but the cursor 
is still present in v$sql (still normal).

3)      (This is where it gets weird) The same query is executed again and 
reloaded (hard parsed) since the plan has aged out, but this time bind 
variables are not peeked so it uses default cardinality estimates and chooses a 
bad execution plan.  I can tell the binds aren't being peeked because they 
aren't present in v$sql_plan.other_xml.

I'm very familiar with the usual problem where the plan changes based on 
different bind variables being peeked, but this is the first time I've seen it 
not peeking at all on subsequent hard parses.  Any idea what would cause this 
behavior?  Is it a bug or a feature?

DB version  10.2.0.2

Thanks,
Brandon






________________________________
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.

Follow-Ups:
- Re: Bind variables peeked on first parse, but not on subsequent hard parses
  - From: Martin Ruthner
- Re: Bind variables peeked on first parse, but not on subsequent hard parses
  - From: Kerry Osborne

Bind variables peeked on first parse, but not on subsequent hard parses

Other related posts: