Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Monitoring PL/SQL

Oracle Database Tips by Donald Burleson

PL/SQL can be problematic to monitor. As a procedural language it lacks interactive capabilities present in 3GL languages. Components of PL/SQL such as the DBMS_OUTPUT and UTL_FILE packages provided limited IO capabilities with DBMS_OUTPUT only delivering messages to the users screen after the completion of PL/SQL processing and UTL_FILE providing text output to files that then must be read or otherwise displayed via a secondary process. More complex instrumenting of PL/SQL can also be accomplished using DBMS_DEBUG.

More complex monitoring can be built in with the DBMS_PIPE and DBMS_ALERT type packages but again only by creating secondary processes that can intercept and process the resulting pipe messages and alerts.

Programs can also be written such that debug code is built into all packages and procedures allowing the developers to switch on and off the debug capabilities built into Oracle, limited as those may be, however, these debug options may involve recompilation of the code using the debug qualifier.

Third party programs provide the capability to execute PL/SQL in a stepwise manner and provide syntactical analysis to help ensure proper coding techniques are used, but the quality of these programs ranges from excellent and expensive to poor and cheap, but not all expensive programs are excellent and not all cheap programs are poor.

Oracle has also provided the internal views and tables that can be utilized to see the statistics involved with package and SQL execution, every PL/SQL developer needs to be aware of the tables and views to use to see PL/SQL related statistics.

Using V$ Tables and DBA Views

Generally speaking the PL/SQL developer may not have access to all of the internal tables and views required to monitor PL/SQL performance. As a minimum the developer needs SELECT access on:






This view provides execution information on PL/SQL objects that are currently in use, or have recently been used. The developer can use this view to determine the usage characteristics of the PL/SQL program units for an application. The V$DB_OBJECT_CACHE view has the structure shown in Figure 1 on Oracle9i.

Name                                      Null?    Type
----------------------------------------- -------- -------------- OWNER                                              VARCHAR2(64)
 NAME                                               VARCHAR2(1000)
 DB_LINK                                            VARCHAR2(64)
 NAMESPACE                                          VARCHAR2(28)
 TYPE                                               VARCHAR2(28)
 SHARABLE_MEM                                       NUMBER
 LOADS                                              NUMBER
 EXECUTIONS                                         NUMBER
 LOCKS                                              NUMBER
 PINS                                               NUMBER
 KEPT                                               VARCHAR2(3)

The important columns that a developer should pay attention to in the V$DB_OBJECT_CACHE internal view are: sharable_mem, loads, executions locks and pins. The sharable_mem column tells the developer the amount of sharable memory the current PL/SQL object occupies. Ideally this should be as small a value as possible. The loads column tells how many times this particular PL/SQL object has been reloaded from disk storage. The executions column tells the developer how many times this PL/SQL object has been executed. The locks column tells the developer how users are currently locking the PL/SQL object. The pins column tells you how many users are currently accessing the object.

If the DBA is doing their job properly any object that shows a large number of executions and has a large memory footprint will be kept. If an object is kept that means it is not subject to the LRU aging algorithm and cannot be removed from memory with normal operation of the database. Keeping an object prevents reloads and performance hits due to the required reloading and reparsing. Objects are kept using the DBMS_SHARED_POOL package.


The V$SQLAREA view provides information about the SQL that is being currently executed or has been executed for the database. The V$SQLAREA view shows all SQL, even if it has been embedded into PL/SQL code or is sent to the database via an external program.

The V$SQLAREA and its companion view V$SQLTEXT provide the text and statistics for each piece of SQL code. The contents of the V$SQLAREA view are shown in Figure 2.

Name                                      Null?    Type
----------------------------------------- -------- --------------
SQL_TEXT                                           VARCHAR2(1000)
SHARABLE_MEM                                       NUMBER
PERSISTENT_MEM                                     NUMBER
RUNTIME_MEM                                        NUMBER
SORTS                                              NUMBER
VERSION_COUNT                                      NUMBER
LOADED_VERSIONS                                    NUMBER
OPEN_VERSIONS                                      NUMBER
USERS_OPENING                                      NUMBER
EXECUTIONS                                         NUMBER
USERS_EXECUTING                                    NUMBER
LOADS                                              NUMBER
FIRST_LOAD_TIME                                    VARCHAR2(19)
INVALIDATIONS                                      NUMBER
PARSE_CALLS                                        NUMBER
DISK_READS                                         NUMBER
BUFFER_GETS                                        NUMBER
ROWS_PROCESSED                                     NUMBER
COMMAND_TYPE                                       NUMBER
OPTIMIZER_MODE                                     VARCHAR2(25)
PARSING_USER_ID                                    NUMBER
PARSING_SCHEMA_ID                                  NUMBER
KEPT_VERSIONS                                      NUMBER
ADDRESS                                            RAW(4)
HASH_VALUE                                         NUMBER
MODULE                                             VARCHAR2(64)
MODULE_HASH                                        NUMBER
ACTION                                             VARCHAR2(64)
ACTION_HASH                                        NUMBER
SERIALIZABLE_ABORTS                                NUMBER
CPU_TIME                                           NUMBER
ELAPSED_TIME                                       NUMBER
IS_OBSOLETE                                        VARCHAR2(1)

Notice that the V$SQLAREA only shows the first 1000 bytes of any SQL. If the SQL is longer than 1000 bytes the V$SQLTEXT view should be used to see the entire text. The critical statistics in this view for tuning code are

  • SHARABLE_MEM -- Amount o sharable memory used by the SQL statement

  • PERSISTENT_MEM -- Amount of persistent memory used by the statement

  • RUNTIME_MEM -- Amount of runtime memory used by the statement

  • SORTS -- Total sorts across all executions

  • EXECUTIONS -- Total executions

  • PARSE_CALLS -- Total parse calls across all executions

  • DISK_READS -- Total disk reads across all executions

  • BUFFER_GETS -- Total buffer gets across all executions

  • ROWS_PROCESSED -- Total rows processed over all executions

  • OPTIMIZER_MODE -- Optimizer mode used by statement

  • SERIALIZABLE_ABORTS -- Number of serializable aborts over all executions

  • CPU_TIME -- Total CPU time used for all executions

  • ELAPSED_TIME -- Total elapsed time for all executions

You should notice that most of the statistics are based on total number of executions. It will probably be more meaningful for you to generate a report based on average values for these statistics over all executions as shown in the code section in Figure 3.

ceil(cpu_time/greatest(executions,1)) ave_cpu_time,
ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,
ceil(disk_reads/greatest(executions,1)) ave_disk_reads,
persistent_mem per_mem, runtime_mem run_mem,
ceil(sorts/greatest(executions,1)) ave_sorts,
ceil(parse_calls/greatest(executions,1)) ave_parse_calls,ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,
ceil(rows_processed/greatest(executions,1)) ave_row_proc,
ceil(Serializable_aborts/greatest(executions,1)) ave_ser_aborts
order by elapsed_time, cpu_time, disk_reads)
where rownum<11

Of course if you aren't interested in all of the stats the list of columns can be pared down to only those with which you have a concern. One thing you will note about the above code is that it returns the top 10 statements (WHERE rownum<11) and provides for setting various thresholds. You may want to place identifying tags using comments inside your SQL statements in your PL/SQL so that you can just look at statements that apply to a specific package. For example:

SELECT /* DBA_UTIL.get_time */ startup_time INTO jdate FROM v$instance;

This will allow you to separate out your PL/SQL program SQL from other SQL that may be in the shared pool with a simple select against the SQL_TEXT column as shown in Figure 4.

sql_text, executions,
ceil(cpu_time/greatest(executions,1)) ave_cpu_time,
ceil(elapsed_time/greatest(executions,1)) ave_elapsed_time,
ceil(disk_reads/greatest(executions,1)) ave_disk_reads,
persistent_mem per_mem, runtime_mem run_mem,
ceil(sorts/greatest(executions,1)) ave_sorts,
ceil(parse_calls/greatest(executions,1)) ave_parse_calls,
ceil(Buffer_gets/greatest(executions,1)) ave_buffer_gets,
ceil(rows_processed/greatest(executions,1)) ave_row_proc
Sql_text like '%&sub_str%';


For example, in my test instance there were 265 total SQL areas, but only 25 had the DBA_UTIL comment line.


The DBA_SOURCE view is based on the SOURCE$ table that holds all of the source code for the stored objects (other than triggers and views) in the database. The DBA_SOURCE view should be used to view specific lines of source code based on output from the DBMS_PROFILER package and other monitoring tools.

The DBA_SOURCE view and underlying table can also be used to document existing stored objects. The DBA_SOURCE view has the following structure which mimics the structure of the SOURCE$ table as shown in Figure 4.

Name                                       Null?    Type
------------------------------------------------------------------OWNER                                               VARCHAR2(30)
NAME                                                VARCHAR2(30)
TYPE                                                VARCHAR2(12)
LINE                                                NUMBER
TEXT                                                VARCHAR2(4000)

Notice that the TEXT column is a VARCHAR2(4000) data type. This 4000 length indicates that to properly view any text lines you may need to set the LONG variable in SQL*Plus to 4000. A simple select will return all of the lines for a given stored object in order as shown in Figure 6.

select 'create or replace '||text,line
 owner = upper('&&owner') and
 type = upper('&&obj_type') and
 name = upper('&&obj_name') and
 line = 1
select text,line
 dba_source s2
where = upper('&&3') and
 s2.owner = upper('&&1') and
 s2.type = upper('&&2') and
 s2.line > 1
order by 2;

We will see in the section on DBMS_PROFILER how the LINE column is used to retrieve a problem line.

Learn More about Oracle Tuning:

This is an excerpt from the top selling book "Oracle PL/SQL Tuning" by Dr. Tim Hall.

You can buy it direct from the publisher for 30%-off and get instant access to the online  code depot of working PL/SQL examples.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.