RE: tracing explaining PL/SQL

Cary,

How is it possible to have 0 current mode gets and have done
INSERT, UPDATE, DELETE, or MERGE?

Seems to me that if you did any of those, you'd have some
current mode gets?

-Mark

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap
Sent: Friday, September 03, 2004 9:44 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: tracing explaining PL/SQL


David,

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

The reason you don't have any statistics on your "Fetch" line is that =
=3D
this
procedure executed no fetches (0). It did some sequence of INSERT, =3D
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@xxxxxxxxxxxxx =3D
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David
Sent: Thursday, September 02, 2004 10:52 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: tracing explaining PL/SQL

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

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


call     count       cpu    elapsed       disk      query    current     =
=3D
=3D20
 rows
------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
Parse        2      0.00       0.00          0          0          0     =
=3D
=3D20
    0
Execute      2      0.02       0.02          0        123          0     =
=3D
=3D20
    2
Fetch        0      0.00       0.00          0          0          0     =
=3D
=3D20
    0
------- ------  -------- ---------- ---------- ---------- =
----------=3D20
----------
total        4      0.02       0.02          0        123          0     =
=3D
=3D20
    2

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

> David,
>
> Extended SQL trace works great for PL/SQL blocks. The following =3D
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, =
=3D
then
> you should investigate the DBMS_PROFILER package.
>
> I believe you can use EXPLAIN PLAN only on an individual SQL =3D
statement,
> not
> a PL/SQL block.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *

---
To unsubscribe - =3D
mailto:oracle-l-request@xxxxxxxxxxxxx&subject=3D3Dunsubscribe=3D20
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

--
To unsubscribe - =
mailto:oracle-l-request@xxxxxxxxxxxxx&subject=3Dunsubscribe=20
To search the archives - http://www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/

Other related posts: