RE: Getting multiple explain plans for 1 sql statement

if its the same query hitting the same objects, then shouldn't the new plan replace the old plan?

I thought Oracle only peaks the first time when it compiles the query, then after that reuses the same plan? -------------- Original message -------------- From: Nirmalya Das <nirmalya_at_hln.com>

> I think this has to do with bind variable peeking.
> If you are collecting "histograms" as the default "gather_stats_job" does, it
> might produce different explain plans for the same query.
>
> Quoting ryan_gaffuri_at_comcast.net:
>
> > why would I spawn child cursors with different execution plans for
> > the exact same query with bind variables with the same
> > plan_hash_value?
> >
> > -------------- Original message --------------
> > From: "Allen, Brandon"
> >
> > I forgot to mention - there could also be different explain plans for
> > different child cursors of the same hash_value - check the
> > "child_number" column, and then check v$sql_shared_cursor to get an
> > idea of why there are multiple children.
> >
> >
> >
> >
> > From: Allen, Brandon
> > Sent: Friday, September 29, 2006 10:54 AM
> > To: 'ryan_gaffuri_at_comcast.net'; oracle-l_at_freelists.org
> > Subject: RE: Getting multiple explain plans for 1 sql statement
> >
> >
> >
> > It's normal to have multiple rows in v$sql_plan for a given
> > hash_value - each row represents a single step of the explain plan.
> > Do both rows have the same plan_hash_value?
> >
> > 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.
>
>

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