RE: Getting multiple explain plans for 1 sql statement
- From: ryan_gaffuri@xxxxxxxxxxx
- To: Nirmalya Das <nirmalya@xxxxxxx>
- Date: Sun, 01 Oct 2006 16:52:39 +0000
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@xxxxxxx>
> 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@xxxxxxxxxxx:
>
> > 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@xxxxxxxxxxx'; oracle-l@xxxxxxxxxxxxx
> > 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.
>
>
Other related posts:
