FreeLists / oracle-l / RE: Getting multiple explain plans for 1 sql statement

[ryan_gaffuri@xxxxxxxxxxx]

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