RE: tracing explaining PL/SQL
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <cary.millsap@xxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 3 Sep 2004 09:50:00 -0400
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 - //freelists.org/archives/oracle-l/09-2004
--
To unsubscribe - =
mailto:oracle-l-request@xxxxxxxxxxxxx&subject=3Dunsubscribe=20
To search the archives - //www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request@xxxxxxxxxxxxx&subject=unsubscribe
To search the archives - //www.freelists.org/archives/oracle-l/
Other related posts:
