Oracle-L: Re: Explain Plan Question

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Mon, 12 Aug 2002 20:28:21 -0800
Message-ID: <F001.004B2604.20020812202821@fatcity.com>

No, it isn't correct. Explain plan gives you precisely what it should, and that is the access plan. Here is an excerpt from the documentation:

The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. A statement's execution plan is the sequence of operations Oracle performs to run the statement.

The row source tree is the core of the execution plan. It shows the following information:

An ordering of the tables referenced by the statement
An access method for each table mentioned in the statement
A join method for tables affected by join operations in the statement
Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

Optimization, such as the cost and cardinality of each operation
Partitioning, such as the set of accessed partitions
Parallel execution, such as the distribution method of join inputs

So, running the explain plan takes into account nothing. It displays the chosen access path. If you're not satisfied, you can always use subtle hints. Oracle optimizer takes everything into account and always gives you the ideal execution plan (or at least, it will, starting with the version 99i).

On 2002.08.12 17:13 Jay Wade wrote:
> Hello:
> I have to looking through Metalink but am unable to find the following.
>
> Does running Explain Plan take into Account the Cost Generated By using
> PL/SQL Functions?
>
> For example Select * from EMP where EMPID=FUNCTION_GET_ID;
>
> I do not believe that it does since the Function SQL does not seem to be
> included in the Plan Output.
>
> Is this correct?
>
> Regards,
> Jay
>
> _________________________________________________________________
> Send and receive Hotmail on your mobile device: http://mobile.msn.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jay Wade
> INET: fish_dba_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Mladen Gogala
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Aug 12 2002 - 23:28:21 CDT