RE: tracing explaining PL/SQL
- From: "David" <thump@xxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 2 Sep 2004 08:51:55 -0700 (PDT)
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
2
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@xxxxxxxxxxxxx&subject=unsubscribe
To read recent messages - //freelists.org/archives/oracle-l/09-2004
Other related posts:
