Different execution plans for same query with same cost
- From: jo_holvoet@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 03 Mar 2005 17:56:58 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
