Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Different execution plans for same query with same cost
Could you snip out the lines from your trace file and post them showing
the query (must show up multiple times in there, right?) along with the
STAT-lines? =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
Received on Thu Mar 03 2005 - 13:19:02 CST