RE: tracing explaining PL/SQL
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 1 Sep 2004 16:47:26 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David
Sent: Wednesday, September 01, 2004 1:02 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
To unsubscribe send an email to: oracle-l-request@xxxxxxxxxxxxx
and put 'unsubscribe' in the subject line.
----------------------------------------------------------------
Other related posts:
