Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: tracing explaining PL/SQL

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

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US