Re: Different execution plans for same query with same cost

On Thu, 03 Mar 2005 17:56:58 +0100, jo_holvoet_at_amis.com <jo_holvoet_at_amis.com> wrote:
> 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 :)

and how many of them relate to rdb :)

> 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 ?

I'm not sure that I believe that the query *is* the same. If it really is the same query then the second run should use the same execution plan as the first one this is rather the point of bind variables for example . Are there perhaps different literal values in the query? different case or capitalisation, FGAC going on if that was around in 8i (think it was).

That all said if when evaluating execution plans the cost the CBO arrives at is not better than the best it already has it will not choose the second plan to be evaluated. (not sure in the absence of the ordered or other hints what drives the order of evaluation - Wolfgang might know).

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l