Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Trace output

RE: Trace output

From: Joze Senegacnik <JozeS_at_hermes-plus.si>
Date: Fri, 23 Jan 2004 00:39:27 -0800
Message-ID: <F001.005DDED5.20040123003927@fatcity.com>






RE: Trace output



Satheesh,

"Row source operation" is the run time execution plan. The "execution plan" is added when you run tkprof and you use parameter "explain=user/password    Connect to ORACLE and issue EXPLAIN PLAN" and this is "theoretical plan". In version 9.2 those two plans can differ. The reason for that is how CBO deals with bind variables in explain plan and when statement is optimized for real execution. For explain plan you don't need to supply any values for bind variables. But when CBO prepares the runtime execution plan (here comes the quote from Database Performance Tuning Guide and Reference 9.2)

<quote> it peeks at the values of user-defined bind variables on the first invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE clause condition, as well as if literals have been used instead of bind variables. On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.

</quote>
So in "Row source operation" you see the actual plan while in explain plan you see only theoretical plan that doesn't take into account the values for bind variables. That's the reason why those two plans can differ.

There are many cases when this can cause problems because the existing plan is not optimal for subsequent execution.

Regards, Joze

-----Original Message-----
From: Satheesh.Babu@iflexsolutions.com
[mailto:Satheesh.Babu@iflexsolutions.com]
Sent: Friday, January 23, 2004 5:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Trace output


Hi,
  I am generating the trace using the event 10046, level 12. In the
trace file I am seeing "Row source operation" following by "execution
plan". What is the different between these 2, as I am seeing diffent
execution plan for both of them?

Thanks and Regards,
Satheesh Babu.S


DISCLAIMER:
This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted,corrupted,lost,destroyed,arrive late or incomplete or contain viruses.The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <Satheesh.Babu@iflexsolutions.com
  INET: Satheesh.Babu@iflexsolutions.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@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.net
-- 
Author: Joze Senegacnik
  INET: JozeS_at_hermes-plus.si

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).
Received on Fri Jan 23 2004 - 02:39:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US