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 







Introduction to PL/SQL Monitoring

Oracle Tips by Burleson Consulting


The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

This chapter will focus on two distinct areas of identifying performance bottlenecks in PL/SQL code such as:

  • Identification of problem areas within a known piece of code.

  • Identification of code causing the biggest impact on the performance of a database instance.

Developers are typically concerned with the performance of their code in isolation, so the first section of this chapter is focused on their needs.  However, DBAs and more experienced developers are typically concerned with the database as a whole, so the second section is focused on their needs.

Before launching into the detail of this chapter, it is worth mentioning performance baselines.

Producing Performance Baselines

As a developer or DBA, you often come across questions such as these:

  • Is the application a little slower since the last release went into production?

  • The application seems slower today, is something going on?

When these questions are proposed, instantly performance baselines come to mind.

A baseline is a measurement of performance for a specific process under a specific circumstance at a specific point in time.  The list of metrics to baseline is almost endless, but the most common metrics include:

  • The amount of time it takes to run a process in a specific environment.

  • The number of rows processed per unit time by a specific process.

  • The amount of memory consumed by a specific process.

  • The amount of CPU used by a specific process.

  • The amount of I/O used by a specific process.

Recording this type of information in all environments during periods of “normal” running allows for comparisons between environments and between time periods within a single environment.

If a performance baseline was recorded before and after an application release, accurate information would then be available showing the impact the release had on the performance of the application.  Likewise, if a baseline was recorded during the “normal” running of the system, the results could be compared to those results produced during the perceived slow period.  Thus, an accurate judgment could be made.

In addition, performance baselines provide a certain amount of predictive ability.  Imagine a procedure that can process 100 rows per second in a development environment and 500 rows per second in a production environment.  Now assume a program change causes the procedure to only process 50 rows per second in development.  Based on the previous baselines, the impact the change had in the production environment can be predicted.

It is not important which metrics are baselined or how the measurements are taken.  The important point is that the baselines representative of a particular application are recorded so that when questions arise about comparative performance, accurate answers will be available.

The following sections in this chapter present a number of approaches to monitoring specific code and database sessions.  The information from most of these methods can be recorded and used to produce performance baselines.

Monitoring Specific PL/SQL Code

A developer will often be required to analyze the performance of code in different environments and identify the areas of concern.  In an ideal world, all performance tuning would be completed before the code has progressed to production, but sometimes production environments throw up unexpected challenges.  This section provides methods for identifying performance bottlenecks in specific PL/SQL and SQL code.

First a look at code instrumentation, which should form the core of your performance analysis as a developer, will be covered in the next section.

Code Instrumentation (application tracing)

You may never have heard of the term “code instrumentation”, but you are probably implementing it anyway.  In essence, it means adding tracing code to programs to display information about the program execution.  This allows for the quick identification of logic and performance problems.

The exact method chosen to implement code instrumentation is not important, and because the volume of data produced can be modified over time, it is not a disaster if the code is a little light on tracing at the moment.  The main point here is to start identifying the key information that enables an understanding of the process flow and the data being processed.  A good place to begin is to trace the following information for each procedure:

  • Start of the procedure, along with incoming parameter values.

  • Major logic related to process flow during the procedure execution.

  • Finish of the procedure, along with return parameter values.

  • Exception handling.

Over time, the level of instrumentation will evolve to suit individual needs.  For instance, you may choose to reduce the amount of trace in very simple functions and procedures, while at the same time increasing the trace in complex routines. 

The important thing to remember is that instrumentation should supply enough information to answer the “What? Where? When? Why?” of every situation in both development and production environments.  If these questions cannot be answered, adding more tracing is a good idea.

As always, the subject of performance must be considered with regards to instrumentation.  As stated earlier, even an empty procedure call entails a small processing overhead, so any trace functionality added in the program will increase execution time, but the following points should be considered:

  • The instrumentation should not be running in production environments, except for specific instances when it is necessary to identify an issue.  As a result, its impact during normal running is very low.

  • Adding instrumentation is an important part of making code easily maintainable.  Imagine how difficult it would be to diagnose problems in Oracle if they had not included all the event instrumentation in the kernel.  Practically every issue would require a kernel developer to diagnose the problem, rather than a member of the support team.

With this in mind, the inclusion of instrumentation should be considered the norm and any associated overhead should be ignored as a necessary evil.

The next section presents one method of code instrumentation using the dsp package.


This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


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

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational