RE: Different execution plans for same query with same cost

Depending on exactly what you mean by the same query since you give no details on the query itself could the results you are seeing be the results of bind variable peeking? Is RLS in use on this DB?

HTH -- Mark D Powell --

-----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 11: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 that don't return about 2 million hits :)

I've run into a situation where the same query run on the same instance from the same session gets a different execution plan from one run to the next (confirmed this with the STAT-lines in a 10046 trace). Both execution 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 the same plan every time) but randomly arrive at one of the plans with the same lowest cost ?

Needless to say, one of the plans is quite a bit faster than the other or nobody would have noticed; now the same query run a couple of seconds apart can range from subsecond response time to more than a minute. 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 => 'for all indexed columns',degree => 4,cascade => true).

mvg/regards

Jo

--

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

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 03 2005 - 12:33:26 CST