Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Different execution plans for same query with same cost

RE: Different execution plans for same query with same cost

From: <jo_holvoet_at_amis.com>
Date: Thu, 03 Mar 2005 18:38:34 +0100
Message-id: <OF1C21F2C3.6FBC1C08-ONC1256FB9.0060CA08@eu.amis.com>


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 Received on Thu Mar 03 2005 - 12:52:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US