RE: tracing explaining PL/SQL

From: David <thump_at_cosmiccooler.org>
Date: Thu, 2 Sep 2004 08:51:55 -0700 (PDT)
Message-ID: <2467.64.37.153.21.1094140315.squirrel@www.cosmiccooler.org>

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

Parse 2 0.00 0.00 0 0 0

0
Execute 2 0.02 0.02 0 123 0

2
Fetch 0 0.00 0.00 0 0 0

0
------- ------ -------- ---------- ---------- ---------- ----------

total 4 0.02 0.02 0 123 0

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

-- 
..
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=unsubscribe 
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

Received on Thu Sep 02 2004 - 18:39:12 CDT