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

From: Regis Biassala <Regis.Biassala_at_datalex.ie>
Date: Tue, 06 May 2003 07:06:54 -0800
Message-ID: <F001.0059109C.20030506070654@fatcity.com>

Yes,
in code scheduled to run as batch job add this: execute immediate
'alter session set timed_statistics=true';

execute immediate
'alter session set sql_trace=true';

Then in the location specified by the parameter UDUMP, a sql trace will be generated then use TKPROF utility to view what you want...check metalink on how to use TKPROF..

Regis

-----Original Message-----
Sent: Tuesday, May 06, 2003 2:27 PM
To: Multiple recipients of list ORACLE-L

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).

This electronic transmission is strictly confidential and intended solely for the addressee. It may contain information which is covered by legal, professional or other privilege. If you are not the intended addressee, you must not disclose, copy or take any action in reliance of this transmission. If you have received this transmission in error, please notify the sender as soon as possible.

This footnote also confirms that this message has been swept for computer viruses.

--

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

Author: Regis Biassala
INET: Regis.Biassala_at_datalex.ie

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