RE: 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>s
>Technology Services | Services
>technologiques
>Informatics Branch | Direction de
>l'informatique
>Maritimes Region, DFO | R駩on des Maritimes,
>MPO
>
>E-Mail: boivinp_at_mar.dfo-mpo.gc.ca
>

Patrice,

   I have missed on your original question but here is a bit of information which I hope may prove useful to you (no DBMS_PROFILER nor 10046 here ... :-)).   Some information about the cost of PL/SQL calls can be found in V$SQL (command_type = 47), you have probably found it since you refer to the buffer reads. The snag is that it includes all the buffer reads of all the SQL statements it calls - which also appear elsewhere in V$SQL (and would appear as a number of distinct cursors in a trace file). I have posted some time ago a query allowing to relate a statement in V$SQL to the calling procedure. The problem is the frequent case when a PL/SQL function is itself called by a SQL statement (ugly functions more often than not). Two X$ tables help you relate one to each other, X$KGLRD and X$KGLDP. Unfortunately, to get the full picture you also need to get the dependencies of packages between themselves, which you will find in SYS.DEPENDENCY$; and you end up with a query in which a join is made through an intermediate CONNECT BY in a subquery, which is far from ideal. I have been working a bit on this before switching to something  else. I should add that within a PL/SQL procedure the flow path may be pretty complicated, and IFs may lead you to execute pretty different statements between successive executions, which blurs the 'average' picture.  I totally share your opinion about loops. Developers often loop needlessly, forgetting that SQL was designed to process sets, and not one-line-at-a-time like the Cobol programs of yore. You also often find 5 SQL statements where one could do the job.

HTH Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).