Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: explain plans for PL/SQL and transactions

RE: RE: explain plans for PL/SQL and transactions

From: Stephane Faroult <>
Date: Fri, 09 May 2003 05:51:43 -0800
Message-ID: <>

>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
>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
>Thanks for the help everyone, I appreciate the
>people on this list very
>Patrice Boivin
>Systems Analyst (Oracle Certified DBA)
>Systems Admin & Operations | Admin. et Exploit. des
>Technology Services | Services
>Informatics Branch | Direction de
>Maritimes Region, DFO | R駊on des Maritimes,


   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

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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 - 08:51:43 CDT

Original text of this message