RE: tracing explaining PL/SQL

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Wed, 1 Sep 2004 16:47:26 -0500
Message-ID: <065501c4906d$48df1130$6701a8c0@CVMLAP02>

David,

Extended SQL trace works great for PL/SQL blocks. The following session generates a little more than 1MB of trace data on my laptop:

connect system/manager
exec sys.dbms_support.start_trace(true, true) /
declare

c number;
begin

	select count(*) into c from v$session;
	select count(*) into c from dba_source;

end;
/
disconnect
/

If you have a PL/SQL block for which the block's execution time is far greater than the sum of its interior SQL statements' execution times, then you should investigate the DBMS_PROFILER package.

I believe you can use EXPLAIN PLAN only on an individual SQL statement, not a PL/SQL block.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 9/20 Hartford, 10/18 New Orleans

Hotsos Symposium 2005: March 6-10 Dallas
Visit www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David
Sent: Wednesday, September 01, 2004 1:02 PM To: oracle-l_at_freelists.org
Subject: tracing explaining PL/SQL

Is'nt explain plan anmd extended SQL tracing limited to straight SQL? How does one accomplish the same when dealing with PL/SQL procedures to get LIO/Explain plan info?

-- 
..
David

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
To unsubscribe send an email to: oracle-l-request_at_freelists.org
and put 'unsubscribe' in the subject line.
----------------------------------------------------------------

Received on Wed Sep 01 2004 - 23:49:53 CDT