Re: Different execution plans for same query with same cost

I've posted the stat-lines in a reply to Wolfgang. On this same db, I've also had a case in the past were repeatedly issuing an 'explain plan for <statement>' would cycle between different execution plans with the same cost (although I know 'explain plan for' and the CBO can generate different plans so this is not really relevant).

mvg/regards

Jo

"Anjo Kolk" <anjo.kolk_at_oraperf.com>
03/03/2005 18:54
Please respond to "Anjo Kolk"  

        To:     <jo_holvoet_at_amis.com>, <oracle-l_at_freelists.org>
        cc: 
        Subject:        Re: Different execution plans for same query with same cost

Could you show the different execution plans? Or are you assuming a difference because the elapse time is different? May be you are running into
the skewed data problem. Quick access on a very small set of the data (index
scan), very slow access with index scan on the large set of the data (should
use full table scan instead).

Anjo.

• Original Message ----- From: <jo_holvoet_at_amis.com> To: <oracle-l_at_freelists.org> Sent: Thursday, March 03, 2005 6:38 PM Subject: RE: Different execution plans for same query with same cost

> Actually CURSOR_SHARING=FORCE. Long story, crappy third-party app (no
> binds, very little normalization, ...).
>
> mvg/regards
>
> Jo
>
>
>
>
>
>
> "Dirschel, Steve" <Steve.Dirschel_at_bestbuy.com>
> Sent by: oracle-l-bounce_at_freelists.org
> 03/03/2005 18:21
> Please respond to Steve.Dirschel
>
>
> To: jo_holvoet_at_amis.com, oracle-l_at_freelists.org
> cc:
> Subject: RE: Different execution plans for same query with

> same cost
>
>
> Same query, same session- oracle should not reparse it- the plan should
> not change. Are you using CURSOR_SHARING=3DSIMILAR? =20
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of jo_holvoet_at_amis.com
> Sent: Thursday, March 03, 2005 10:57 AM
> To: oracle-l_at_freelists.org
> Subject: Different execution plans for same query with same cost
>
> Hi all,
>
> Oracle 8.1.7.4 on 64-bit Solaris 8.
> I've searched Metalink but I'm having a hard time finding search
terms=20
> that don't return about 2 million hits :)
>
> I've run into a situation where the same query run on the same
instance=20
> from the same session gets a different execution plan from one run to
> the=20
> next (confirmed this with the STAT-lines in a 10046 trace). Both
> execution=20
> plans apparently get the same cost from CBO. Am I right in thinking that
>
> in such a case the CBO is not "deterministic" (i.e. it will not arrive
> at=20
> the same plan every time) but randomly arrive at one of the plans with
> the=20
> same lowest cost ?
>
> Needless to say, one of the plans is quite a bit faster than the other
> or=20
> nobody would have noticed; now the same query run a couple of seconds=20
> apart can range from subsecond response time to more than a minute.=20
> Anything I can do to avoid this apart from using hints ? Stats are up to
>
> date and were gathered with :
> dbms_stats.gather_schema_stats(owner,method_opt =3D> 'for all indexed=20
> columns',degree =3D> 4,cascade =3D> true).
>
>
> mvg/regards
>
> Jo
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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