RE: tracing explaining PL/SQL

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Fri, 3 Sep 2004 09:50:00 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CF09660DCA@bosmail00.bos.il.pqe>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap Sent: Friday, September 03, 2004 9:44 AM To: oracle-l_at_freelists.org
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_at_freelists.org =3D
[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 =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_at_freelists.org&subject=3D3Dunsubscribe=3D20
To read recent messages - http://freelists.org/archives/oracle-l/09-2004

--
To unsubscribe - =
mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20
To search the archives - http://www.freelists.org/archives/oracle-l/
--
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:45:36 CDT