RE: Interpretation of TKPROF output

Actually two things can cause differences between tkprof output and "explain plan" output:

1. Schema changes between when the trace output was collected and when the tkprof was executed.
2. tkprof bugs. Tkprof often produces incorrect execution plans from the STAT lines in the trace file. You can tell by viewing the STAT lines yourself. A line's pid is that row source operation's parent row source id. Children at the same level are listed in id order.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:

- Hotsos Clinic, Oct 15-17 Dallas, Dec 9-11 Honolulu
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
- Jonathan Lewis' Optimising Oracle, Nov 19-21 Dallas

-----Original Message-----
Bobak
Sent: Sunday, October 13, 2002 7:53 PM
To: Multiple recipients of list ORACLE-L

Peter,

"Row Source Operation" is what Oracle actually did, or, at least what was recorded in the trace file. "Execution Plan" is what TkProf did in response to you specifying "explain=" option. They can differ if things changed from the time the trace was done to the time the explain was executed. Particularly, if stats changed, or session modifiable parameters differed between the session being traced and the session that was established at TkProf execution time.

When they differ, the "Row Source Operation" is a better bet as to what actually happened.

Hope that helps,

-Mark

On Sun, 2002-10-13 at 16:03, Schauss, Peter wrote:
> I am running tkprof on some trace files with the following options:
> (Oracle 8.1.7)
>
> explain=<user>/<password> sort=exeqry,fchqry,execu,fchcu
>
> Output after each query includes the following two tables:
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 36 INLIST ITERATOR
> 36 TABLE ACCESS BY INDEX ROWID PART_LIST
> 72 INDEX RANGE SCAN (object id 3451)
>
>
> Rows Execution Plan
> ------- ---------------------------------------------------
> 0 SELECT STATEMENT GOAL: CHOOSE
> 36 TABLE ACCESS GOAL: ANALYZED (FULL) OF 'PART_LIST'
>
> The "Execution Plan" table corresponds to the Explain Plan section
> of the Performance manual. There is no mention, however, of the
> "Row Source Operation" table. I note particularly, that in some cases
> the first table seems to agree with the second. In others, however,
such as
> the example here, the Row Source table shows use of an index while the
> Execution Plan shows a Table Access (Full table scan?).
>
> Am I interpreting this correctly? If so, what is going on?
>
> Thanks,
>
> Peter Schauss
> Northrop Grumman Corporation
> schaupe_at_mail.northgrum.com

-- 
--
Mark J. Bobak
Oracle DBA
mark_at_bobak.net
"It is not enough to have a good mind.  The main thing is to use it
well."
 						-- Rene Descartes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark J. Bobak
  INET: mark_at_bobak.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).