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

With 10.2.0.2 I'd say it's probably a bug.

Martin

Allen, Brandon wrote:


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. 

--
Martin Ruthner
Database Administrator - The Pythian Group, Inc.
Email  : ruthner@xxxxxxxxxxx
Work   : 1.877.PYTHIAN x250
Mobile : 1.613.218.8402
IM     : pythianruthner

--
http://www.freelists.org/webpage/oracle-l

Other related posts: