| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: explain plans for PL/SQL and transactions
What if I don't have SQL in my pl/sql procedure? OP was trying to find why
and where his code was looping. Where in pl/sql code there was wastage.
I have been using 10046 for a couple of years, I have done pl/sol for over 6 years, please show me how using 10046 you would find bad pl/sql when there is no single SQL statement in the code.
I am not doubting you, please educate me.
Raj
-----Original Message-----
Sent: Thursday, May 08, 2003 8:07 PM
To: Multiple recipients of list ORACLE-L
Sorry, but your statement that " for pl/sql you have no choice other
than dbms_profiler" is incorrect.
I have not challenged the usefulness of dbms_profiler. But I have now
been told twice that you can't use 10046 trace for pl/sql and if you
believe that you can't get useful information out of a 10046, level 8
trace for a pl/sql job then you are cheating yourself.
Let's look again at the original question.
For a batch job running pl/sql the user wanted to see:
-- the execution plans and costs for all the code it submits
I can't find the execution plans/costs using profiler. I could be in
for some education here, maybe it's there and I'm just not seeing it.
-- memory used
I can't find this using either tool.
-- cpu used
I can't find this in dbms_profiler output, but it is in 10046 trace.
-- time to complete.
Clearly available in both. However, a lot more detail of events
appears available in 10046.
I only continue to respond to this because I hate for someone who may have never used either tool to get the idea that if tasked with troubleshooting performance of a job that uses pl/sql they cannot use a 10046 trace. That would potentially be a huge part of a DBA's or a developer's world in which they would be limiting themselves. Is it the end all solution? Absolutely not. Is it going to help solve every pl/sql or sql problem? Absolutely not. Is it, in general, a useful tool to use with sql AND pl/sql? Absolutely!!
DL
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.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).
 |
 |