Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: explain plans for PL/SQL and transactions

RE: explain plans for PL/SQL and transactions

From: Darrell Landrum <DLANDRUM_at_zalecorp.com>
Date: Thu, 08 May 2003 12:24:23 -0800
Message-ID: <F001.005946D2.20030508122423@fatcity.com> 





No harm done, just the language of the question was initially striking.
Seriously, I'm not sure what more 'workload' information one would want
than what is available in a level 8 trace.  It appears to cover
everything that the job is doing.



>>> ineyman_at_perceptron.com 05/08/03 02:42PM >>>
Darrell,



Please don't take it personally; there was no intent to "insult".
It is known, that 10046 event traces SQL (standalone and inside
PL/SQL).


My understanding was, that Patrice asked, how to trace PL/SQL
constructs, not SQL statements inside PL/SQL.
Quoting original e-mail:


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



Igor Neyman, OCP DBA


ineyman_at_perceptron.com 





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


Landrum


Sent: Thursday, May 08, 2003 1:53 PM


To: Multiple recipients of list ORACLE-L



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






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:






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


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: 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
---------------------------------------------------------------------


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


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





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


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: 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
---------------------------------------------------------------------


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


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).
Received on Thu May 08 2003 - 15:24:23 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US