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

From: John-X Garner <john-x.garner_at_db.com>
Date: Wed, 07 May 2003 03:51:58 -0800
Message-ID: <F001.005920BD.20030507035158@fatcity.com>

Or you can use ORADEBUG to attach to the OSPID and get a level 12 trace which shows all bind variables.......

Use TKPROF in the same way.......

                                                                                                                                                                          
                      "Hallas, John, Tech                                                                                                                                 
                      Dev"                        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                                                   
                      <John.Hallas_at_gb.voda        cc:                                                                                                                     
                      fone.co.uk>                 Subject:  RE: explain plans for PL/SQL and transactions                                                                 
                      Sent by:                                                                                                                                            
                      root_at_fatcity.com                                                                                                                                    
                                                                                                                                                                          
                                                                                                                                                                          
                      07/05/2003 12:11                                                                                                                                    
                      Please respond to                                                                                                                                   
                      ORACLE-L

You can also use
DBMS_TRACE.SET_PLSQL_TRACE (trace_level INTEGER) within your code

John

-----Original Message-----

Sent: 06 May 2003 16:07
To: Multiple recipients of list ORACLE-L

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


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

--

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

Author: Hallas, John, Tech Dev
INET: John.Hallas_at_gb.vodafone.co.uk

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

This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.

--

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

Author: John-X Garner
INET: john-x.garner_at_db.com

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