Oracle Stored Procedures Tips
Oracle Tips by Burleson Consulting
Burleson Oracle Consulting
provides world-class Oracle stored procedure PL/SQL tuning and PLSQL
optimization at great rates. Just call for expert Oracle stored procedure
By Don Burleson
stored procedures and triggers
are faster than traditional code, which means they are becoming increasingly
popular. As application code moves away from external programs and into the
database engine, DBAs need to understand the related memory requirements for
Oracle stored procedures and
know how to manage Oracle stored procedures for optimal database performance.
For complete details on tuning Oracle stored procedures, see my book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle stored procedure scripts.
stored procedures and triggers
are becoming more popular, and more
application code will move away from external programs and into the database
engine. However, Oracle DBAs must be conscious of the increasing memory demands
of Oracle stored procedures and carefully plan for the days when all of the database
access code (PL/SQL) resides within the database.
Today, most Oracle Server databases have only a small amount of code in Oracle stored
procedures, but this is rapidly changing. There are many compelling benefits to
putting all Oracle SQL inside Oracle stored procedures, including:
- Better performance.
Oracle stored procedures load once into the shared pool and remain there unless
they become paged out. Subsequent executions of the Oracle stored procedure are far
faster than executions of external code.
- Coupling of data with
behavior. DBAs can use naming conventions to couple relational tables
with the behaviors associated with a table by using Oracle stored procedures
as "methods". If all behaviors associated with
the employee table are prefixed with the table name--employee.hire,
employee.give_raise, for example--the data dictionary can be queries to list
all behaviors associated with a table (select * from dba_objects where owner
= 'EMPLOYEE'), and it's easy to identify and reuse code via stored
- Isolation of code.
Since all SQL is moved out of the external programs and into the Oracle stored
procedures, the application programs become nothing more than calls to
Oracle stored procedures. As such, it becomes very simple to swap out one database
and swap in another one.
Thus a benefit of using stored procedures is being able to
move all SQL into the data dictionary allowing you to
tune SQL (re-writing the SQL, adding hints), without directly touching the
One of the foremost reasons
stored procedures and triggers function faster than traditional code is related
to caching in the Oracle SGA. After an Oracle stored procedure has been loaded into the shared pool of the
SGA, it remains there until it is paged out of memory to make room for other
stored procedures. Items, such as stored procedures, are paged out based on a least recently used (LRU)
Once loaded into the RAM memory of the shared pool, stored procedures will
execute very quickly. The trick to the proper use of stored procedures is to prevent pool thrashing as many
procedures compete for a limited amount of shared-pool memory.
The db_cache_size and shared_pool_size parameters define most of
the size of the in-memory region that Oracle consumes on startup and determine
the amount of storage available to cache data blocks, SQL, and Oracle stored
Also see these notes on Oracle stored procedures
Oracle also provides a construct called a package. Essentially, a package is a
collection of functions and Oracle stored procedures. DBAs can organize packages in a
variety of ways. For example, you can group functions and Oracle stored procedures for
employees logically together in an employee package:
This code encapsulates all employee behaviors into a single package
of stored procedures, which will
be added to Oracle Server's data dictionary. If DBAs force their programmers to
use Oracle stored procedures, the SQL moves out of the external programs, and the
application programs become nothing more than a series of calls to Oracle stored
procedures. This makes the application programs completely portable, while at
the same time offering the benefit of using the Oracle dictionary as a ventral
repository for all SQL.
the SGA for
Oracle Stored Procedures
- Private SQL area (exists during
cursor open/cursor close). Within the private SQL area are the persistent area
and the runtime area.
How To Pin Oracle Packages
To prevent paging, you can mark
packages containing Oracle stored procedures as non-swappable, telling the database that after their initial load
they must always remain in memory. This is called pinning or memory fencing.
Oracle provides the procedure dbms_shared_pool.keep for pinning a package. You
can unpin packages by using dbms_shared_pool.unkeep. In addition to pinning
packages at database startup time, you can call the dbmspool.keep procedure at
runtime to pin a package of stand-alone Oracle stored procedures.
The choice of whether to pin a procedure in memory is a function of the size of
the object and the frequency in which it is used.
Very large Oracle stored procedures that are
called frequently might benefit from pinning, but you might never notice any
difference in that case, because the frequent calls to the stored procedure will have
kept it loaded into memory anyway.
In an ideal world, the init.ora shared_pool_size parameter would be large enough to
accept every package, Oracle stored procedure and trigger your applications might
invoke. Reality, however, dictates that the shared pool cannot grow
indefinitely, and you need to make wise choices regarding which objects you pin.
You can query the sharable_mem column of the v$db_object_cache table to see how
much memory each package consumes in the library cache.
Automatic Re-pinning of Oracle Stored Procedures
There is a easy way to tell the number of times a nonpinned
Oracle stored procedure was
swapped out of memory and needed to be reloaded. One method of effectively
measuring memory is to regularly run the estat/bstat utility (usually located in
~/rdbms/admin/utlbstat.sql and utlestat.sql) for measuring SGA memory
consumption over a range of time (the range of time is the interval between
running the bstat utility and running the estat utility).
Also, be aware that Oracle
Server uses the relevant parameter, shared_pool_size, for other objects besides
Oracle stored procedures. This means that one parameter fits all, and Oracle Server
offers no method for isolating the amount of storage allocated to any subset of
the shared pool, including the library cache where packages and Oracle stored
As memory becomes cheaper, it
will eventually become desirable to have all of an application's SQL and code
loaded into the Oracle library cache, where the code will be quickly available
for execution by any external application regardless of platform or host
language. The most compelling reasons for putting all SQL within packages are
portability and code management. If all applications become "SQL-less," with
calls to Oracle stored procedures, then DBAs will be able to port entire applications
to other platforms without touching a line of the application code.
Don Burleson is a recognized expert in PL/SQL tuning and PLSQL optimization.
A special thanks to Gita Gupta,
of Oracle Corporation, for help with this article.
For more Oracle stored procedure scripts, see "Oracle
Tuning: The Definitive Reference".