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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of jo_holvoet@xxxxxxxx Sent: Thursday, March 03, 2005 11:57 AM To: oracle-l@xxxxxxxxxxxxx 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