Oracle-L: RE: explain plans for PL/SQL and transactions

From: Darrell Landrum <DLANDRUM_at_zalecorp.com>
Date: Thu, 08 May 2003 10:52:31 -0800
Message-ID: <F001.0059430A.20030508105231@fatcity.com>

Let's set aside the insult of being questioned whether I have read the original e-mail.
I have to ask have either you or Mr. Neyman run a 10046, level 8 trace?

Patrice asked :
Is there a way to track the activity associated with a batch job, to see:
-- the execution plans and costs for all the code it submits

memory used
cpu used
time to complete.

A 10046 level 8 trace provides all of that information (plus much more) except memory.
One example of an analyzed 10046 level 8 trace file from a session in which I ran a simple block of PL/SQL code is attached. Guess what? It shows all of the recursive sql. It shows execution plans. It shows costs. It shows cpu time. It shows the overall time, etc., etc., etc.

Here is the script I ran...
alter session set tracefile_identifier='DWL'; set serveroutput on size 1000000
alter session set max_dump_file_size=unlimited; alter session set events '10046 trace name context forever, level 8'; alter session set events '10053 trace name context forever, level 1';

declare

v_var1 user_tables.table_name%type;
v_var2 date;

begin

select table_name into v_var1 from user_tables where rownum < 2;

select sysdate into v_var2 from dual;

dbms_output.put_line('The values are: '||v_var1||' and '||v_var2);

end;
/

>>> Rajendra.Jamadagni_at_espn.com 05/08/03 01:02PM >>> Darrell,

His point is that 10046 does SQL tracing, not pl/sql tracing. OP asked for
pl/sql tracing.

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Thursday, May 08, 2003 1:07 PM
To: Multiple recipients of list ORACLE-L

So, what's your point?

>>> ineyman_at_perceptron.com 05/08/03 11:37AM >>> Did you read the original e-mail?
Patrice asked about tracing PL/SQL, not SQL.

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Landrum
Sent: Tuesday, May 06, 2003 1:02 PM
To: Multiple recipients of list ORACLE-L

Sounds like a good use for a 10046 level 8 or 12 trace. Full and recommend use of 10046 trace is too much for one e-mail but I'd start researching on metaling and there are some excellent documents available
at www.hotsos.com.

>>> BoivinP_at_mar.dfo-mpo.gc.ca 05/06/03 08:26AM >>> Is there a way to track the activity associated with a batch job, to see:

the execution plans and costs for all the code it submits
memory used
cpu used
time to complete.

I like explain plan very much, but am looking for something similar that
covers PL/SQL as well.

A user here is submitting code that keeps looping. The SQL itself is not
the problem, I think the problem has to do with the execution path of the
PL/SQL code.

I am looking for something that provides the comprehensive info we get from
explain plan, but for a batch job.

Patrice.
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Boivin, Patrice J
INET: BoivinP_at_mar.dfo-mpo.gc.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Darrell Landrum
INET: DLANDRUM_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Igor Neyman
INET: ineyman_at_perceptron.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Darrell Landrum
INET: DLANDRUM_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Message containing double extensions/CLSID exploit. This message contained attachments that have been blocked by Guinevere. Please see your system administrator for more details

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Darrell Landrum
INET: DLANDRUM_at_zalecorp.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------