RE: tracing explaining PL/SQL

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Fri, 3 Sep 2004 08:43:50 -0500
Message-ID: <077d01c491bc$1043e260$6701a8c0@CVMLAP02>

David,

LIO ~=3D query + current. (The "~" here is in deference to a = presentation
Jonathan Lewis makes.)

The reason you don't have any statistics on your "Fetch" line is that = this
procedure executed no fetches (0). It did some sequence of INSERT, = UPDATE,
DELETE, or MERGE commands, but no SELECT command.

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: Thursday, September 02, 2004 10:52 AM To: oracle-l_at_freelists.org
Subject: RE: tracing explaining PL/SQL

Can the event method be used to trace PL/SQL for other session to be = more
precise with my question?
HEre is an example:
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 215 (TEST)

*************************************************************************=

***

begin test.persister.save_message_batch(:message_id, :object_id, = :method,
:data, :call_time, :guaranteed, :delivery_type, :chunk_size, :enable_db_logging); end;

call count cpu elapsed disk query current = =20
rows
------- ------ -------- ---------- ---------- ---------- ----------=20

Parse 2 0.00 0.00 0 0 0 = =20

0
Execute 2 0.02 0.02 0 123 0 = =20

2
Fetch 0 0.00 0.00 0 0 0 = =20

0
------- ------ -------- ---------- ---------- ---------- ----------=20

total 4 0.02 0.02 0 123 0 = =20

I'm confused why no disk or query fethes, which I equate to LIO's... --=20
..
David

> 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 *

---
To unsubscribe - =
mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Received on Fri Sep 03 2004 - 08:41:10 CDT