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:

V$DB_OBJECT_CACHE
V$SQLAREA
V$SQLTEXT
DBA_SOURCE, ALL_SOURCE or USER_SOURCE

V$DB_OBJECT_CACHE

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.

V$SQLAREA

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.

SELECT * FROM(
SELECT
sql_text,
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
FROM
v$sqlarea
WHERE
Disk_reads/greatest(executions,1)>&&disk_reads
OR
Cpu_time/greatest(executions,1)>&&cpu_time
OR
Elapsed_time/greatest(executions,1)>&&elapsed_time
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.

SELECT
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
FROM
v$sqlarea
WHERE
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.

DBA_SOURCE

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
from
dba_source
where
owner = upper('&&owner') and
type = upper('&&obj_type') and
name = upper('&&obj_name') and
line = 1
UNION
select text,line
from
dba_source s2
where
s2.name = upper('&&3') and
s2.owner = upper('&&1') and
s2.type = upper('&&2') and
s2.line > 1
order by 2;