Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: explain plans for PL/SQL and transactions
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
-----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).
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