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: Boivin, Patrice J <BoivinP_at_mar.dfo-mpo.gc.ca>
Date: Fri, 09 May 2003 04:07:35 -0800
Message-ID: <F001.00594F96.20030509040735@fatcity.com>


I am going to try dbms_profiler this morning, I read in RevealNet that it tends to create a very large amount of rows, to accommodate I moved the plsql_% tables and their associated indexes to a separate tablespace.

We just want an overview of what is happening, the code is looping often, to the point where a simple query produces over a billion (yes, billion) buffer reads.

The developers just asked me to add memory to the server, I hope to show that perhaps their code could be tuned a little.

If dbms_profiler doesn't do it I will trace the session.

Thanks for the help everyone, I appreciate the people on this list very much.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services        | Services technologiques
Informatics Branch         | Direction de l'informatique 
Maritimes Region, DFO      | Région des Maritimes, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

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

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

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). Received on Fri May 09 2003 - 07:07:35 CDT

Original text of this message

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