Introduction to PL/SQL
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:
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
The amount of CPU used by a specific
The amount of I/O used by a specific
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
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
Start of the procedure, along with incoming
Major logic related to process flow during
the procedure execution.
Finish of the procedure, along with return
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