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: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 08 May 2003 20:21:38 -0800
Message-ID: <F001.00594D14.20030508202138@fatcity.com>


Absolutely!

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Hotsos Clinic 101 in London, Reykjavik, Ottawa, Dallas, Washington, Denver, Sydney
- Visit www.hotsos.com for schedule details...

-----Original Message-----
Landrum
Sent: Thursday, May 08, 2003 7: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

>>> Rajendra.Jamadagni_at_espn.com 05/08/03 04:37PM >>> Darrell,

I think you have to distinguish between pl/sql and sql. 10046 deals with
sql. for pl/sql you have no choice other than dbms_profiler (it used to be
10928/10938 but oracle discourages that) and soon to be discontinued dbms_trace.

So, if you ask me pl/sql I still stand by my answer and hopefully you can
show us how to trace pl/sql using 10046.

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

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: Cary Millsap
  INET: cary.millsap_at_hotsos.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 - 23:21:38 CDT

Original text of this message

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