RE: Getting multiple explain plans for 1 sql statement

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" <Brandon.Allen_at_OneNeck.com>
>
> 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