Re: How "deterministic" are EXPLAIN PLAN results?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 9 Apr 2003 12:57:12 +0100
Message-ID: <b711q0$rph$1$830fa795@news.demon.co.uk>

I think Vladimir's comment about being
"FULLY hinted" is the most critical one.

Consider, for example:

    select

        /*+ index(t1,t1_abc) index(t2,t2_abc) */
        count(*)

    from t1, t2
    where t1.col1 = t2.col1

For weeks, this may give you the plan:

    NESTED LOOP

        table access by rowid t1
            index range scan t1_abc
        table access by rowid t2
            index range scan t2_abc

Then, because of changes in statistics,
or init.ora parameters, or nullity of a column, or a few other situations that may have slipped my mind at the moment, this might change to

        HASH JOIN
            table access by rowid t2
                index range scan t2_abc
            table access by rowid t1
                index range scan t1_abc

Your hints are still obeyed, the plan has changed. On the other hand, if you had specified

    /*+

            no_parallel(t1)
            no_parallel(t2)
            no_parallel_index(t1)
            no_parallel_index(t2)
            ordered
            use_nl(t2)
            index(t1,t1_abc)
            index(t2,t2_abc)

    */

Then I think you could be fairly confident that there was no way that Oracle could obey the hints whilst changing the access path.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message
news:b70v7p$hfk$1_at_babylon.agtel.net...


> > My question is this.  If the "explain plan" for these two queries

is


> > *exactly* the same, does that mean that Oracle will *definitely*

use


> > the same "statement execution plan" for them?  Or are there ever

> > circumstances in which two SELECT statements with identical

"explain


> > plans" will be executed with different "statement execution

plans"?
>
> Well, the CBO is intelligent enough to adapt to the changes in the
> environment, to a degree, and it may produce a different plan for
> the same query if the environment changes alter some input values
> for the costing engine. However, once the query is parsed and
> while it's still in the shared pool, its plan won't change. Hints
also
> reduce CBO's freedom because they specifically exclude some
> access paths from consideration, and chances for CBO to come up
> with a different plan decrease considerably. Fully hinted query has
> almost no chance to receive a different plan because you explicitly
> instructed the optimizer how you want this query to be executed
> (the only case when it may receive a different plan is when you
alter
> the environment in a way, which invalidates some of your hints -
> drop an index, for example.)
>
> Corrections and additions welcome.
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based
internet applications.
> All opinions are mine and do not necessarily go in line with those
of my employer.
>
>

Received on Wed Apr 09 2003 - 06:57:12 CDT