Tips for Using Oracle Stored Procedures Oracle Tips by Burleson Consulting

 

Burleson Oracle Consulting provides world-class stored procedure PL/SQL tuning and PLSQL optimization at great rates. Just call for expert stored procedure support:

---

Tips for Using Oracle Stored Procedures
By Don Burleson

Oracle 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 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.

Oracle stored procedures and triggers are becoming more popular, and more application code will move away from external programs and into the database engine. Oracle DBAs must be conscious of the increasing memory demands of Oracle stored procedures, however, 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 stored procedures, but this is rapidly changing. There are many compelling benefits to putting all Oracle SQL inside 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 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 (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.
   
• 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 stored procedures. As such, it becomes very simple to swap out one database and swap in another one.

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 are paged out based on a least recently used (LRU) algorithm. Once loaded into the RAM memory of the shared pool, procedures will execute very quickly, and the trick is to prevent pool thrashing as many procedures compete for a limited amount of shared-pool memory.

When tuning Oracle, there are two init.ora parameters that are more important than all of the others combined: db_cache_size, and shared_pool_size parameters.

The db_cache_size parameter controls the number of blocks that are allocated within the SGA for holding database blocks. The db_cache_size parameter is the major determining factor in the buffer hit ratio. Of course, the larger the number of database buffers, the more likely it is that a database block will remain in the buffer, thereby reducing I/Os for subsequent requests against the block.

When Oracle receives a request to retrieve data, it will first check the internal memory structures to see if the data is already in the buffer. This practice allows to server to avoid unnecessary I/O. In an ideal world, DBAs would be able to create one buffer for each database page, thereby ensuring that Oracle Server would read each block only once. The real-world cost of memory, however, makes this prohibitive. At best, you can allocate only a small number of real-memory buffers, and Oracle Server will manage this memory for you. Oracle Server utilizes an LRU algorithm to determine which database pages to flush from 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 stored procedures. You can also reserve memory in the shared pool to satisfy large allocations. You can set the size of the reserved space as well as the size of the allocations from the reserved space by using the init.ora parameters shared_pool_reserved_size and shared_pool_reserved_min_alloc. Setting aside space from which only allocations larger than shared_pool_reserved_min_alloc are made ensures that small allocations do not fragment the reserved space.

Oracle also provides a construct called a package. Essentially, a package is a collection of functions and stored procedures. DBAs can organize packages in a variety of ways. For example, you can group functions and stored procedures for employees logically together in an employee package:

CREATE PACKAGE employee AS

FUNCTION compute_raise_amount (percentage NUMBER);
PROCEDURE hire_employee();
PROCEDURE fire_employee();
PROCEDURE list_employee_details();

END employee;

This code encapsulates all employee behaviors into a single package, which will be added to Oracle Server's data dictionary. If DBAs force their programmers to use 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.

• Preparing the SGA for Oracle Stored Procedures
• Dictionary cache.
• Library cache.
• Shared SQL areas.
• Private SQL area (exists during cursor open/cursor close). Within the private SQL area are the persistent area and the runtime area.

The shared pool uses an LRU algorithm to determine which objects get paged out of the shared pool. As this paging occurs, fragments, or discontiguous chunks of memory, are created within the shared pool.

Imagine the shared_pool_size RAM region as being similar to a tablespace. Just as you may get an ORA-1547 error message when you can't get sufficient contiguous free space in the tablespace, similarly, you will get ORA-4031 when you can't get contiguous free space in the shared_pool_size (SGA region). You no longer need a contiguous piece of memory to load a package, procedure, or function, so the chances of getting this message are reduced.

This means that a large procedure that fit into memory initially may not fit into contiguous memory when it is reloaded after paging out. Consider a problem that occurs when the body of a package has been aged out of the instance's SGA because of other more recent or frequent activity. Fragmentation then occurs, and Oracle Server cannot find enough contiguous memory to reload the package body, resulting in an ORA-4031 error.

How To Pin Oracle Packages

To prevent paging, you can mark packages 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 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 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, 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.

Oracle Corporation recommends that you always pin the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE, and DBMS_OUTPUT packages in the shared pool. You can use the following code:

connect internal;
@/usr/oracle/rdbms/admin/dbmspool.sql
execute dbms_shared_pool.keep('sys.standard');

You can write a standard procedure to pin all of the recommended Oracle packages into the shared pool. Here is the script:

execute dbms_shared_pool.keep('DBMS_ALERT');
execute dbms_shared_pool.keep('DBMS_DDL');
execute dbms_shared_pool.keep('DBMS_DESCRIBE');
execute dbms_shared_pool.keep('DBMS_LOCK');
execute dbms_shared_pool.keep('DBMS_OUTPUT');
execute dbms_shared_pool.keep('DBMS_PIPE');
execute dbms_shared_pool.keep('DBMS_SESSION');
execute dbms_shared_pool.keep('DBMS_SHARED_POOL');
execute dbms_shared_pool.keep('DBMS_STANDARD');
execute dbms_shared_pool.keep('DBMS_UTILITY');
execute dbms_shared_pool.keep('STANDARD');
 

Automatic Re-pinning of Oracle Stored Procedures

UNIX and Linux users may want to add code to the /etc/rc file to ensure that the packages are re-pinned after each database startup. A script might look like this:

[root]: more pin
#! /bin/ksh
ORACLE_SID=mydata
# run this script as root
# now, sign on as ORACLE, using the SU command
export ORACLE_SID
su oracle -c "/oramag/usr/oracle/bin/sqldba mode=line /<<!
connect internal;
select * from db;
@/usr/local/dba/sql/pin.sql
exit;
!"

You also need to remember to run pin.sql whenever you must bounce a database from SQL*Plus.

Monitoring Pinned Stored Procedures

Here's a handy memory.sql script for looking at pinned packages in the SGA:

set pagesize 60;
column executions format 999,999,999;
column Mem_used format 999,999,999;

SELECT SUBSTR(owner,1,10) Owner,
SUBSTR(type,1,12) Type,
SUBSTR(name,1,20) Name,
executions,
sharable_mem Mem_used,
SUBSTR(kept||' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY executions desc;

Here's the output of the memory.sql script:

SQL> @memory

OWNER TYPE NAME EXECUTIONS MEM_USED Kept
----- ---- ---- ---------- -------- ----
SYS PACKAGE STANDARD 867,600 151,963 YES
SYS PACKAGE BODY STANDARD 867,275 30,739 YES
SYS PACKAGE DBMS_ALERT 502,126 3,637 NO
SYS PACKAGE BODY DBMS_ALERT 433,607 20,389 NO
SYS PACKAGE DBMS_LOCK 432,137 3,140 YES
SYS PACKAGE BODY DBMS_LOCK 432,137 10,780 YES
SYS PACKAGE DBMS_PIPE 397,466 3,412 NO
SYS PACKAGE BODY DBMS_PIPE 397,466 5,292 NO
HRIS PACKAGE     S3425_PACKAGE 285,700 3,776 YES
SYS PACKAGE DBMS_UTILITY 284,694     3,311 NO
SYS PACKAGE BODY DBMS_UTILITY 284,694 6,159 NO
HRIS PACKAGE HRS_COMN_PACKAGE 258,657 3,382 NO
HRIS PACKAGE BODY S125_PACKAGE 248,857 30,928 NO
HRIS PACKAGE BODY HRS_COM_PACKAGE 242,155 8,638 NO
HRIS PACKAGE GTS_SNAP_UTILITY 168,978 11,056 NO
HRIS PACKAGE BODY GTS_SNAP_UTILITY 89,623 3,232 NO
SYS PACKAGE DBMS_STANDARD 18,953 14,696 NO
SYS PACKAGE BODY DBMS_STANDARD 18,872 3,432 NO
KIS PROCEDURE     RKA_INSERT 7,067 4,949 NO
HRIS PACKAGE HRS_PACKAGE 5,175 3,831 NO
HRIS PACKAGE BODY HRS_PACKAGE 5,157 36,455 NO
SYS PACKAGE DBMS_DESCRIBE 718 12,800 NO
HRIS PROCEDURE   CHECK_APP_ALERT 683 3,763 NO
SYS PACKAGE BODY DBMS_DESCRIBE 350 9,880 NO
SYS PACKAGE DBMS_SESSION 234 3,351 NO
SYS PACKAGE BODY DBMS_SESSION 165 4,543 NO
GIANT PROCEDURE  CREATE_SESS_RE 62 7,147 NO
HRIS PROCEDURE   INIT_APP_ALERT 6 10,802 NO

There is a easy way to tell the number of times a nonpinned 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).

Check v$sgastat and make sure "free memory" is low, and make sure the library cache, sql area, and dictionary cache don't show much fluctuation over time and that they also have high hit ratios in v$rowcache and v$librarycache.

Another handy method is to write a snapdump utility to interrogate the SGA and note any exceptional information relating to the library cache. This would include the following measurements:

• Data-dictionary hit ratio.
• Library-cache miss ratio.
• Individual hit ratios for all namespaces.

Also, be aware that Oracle Server uses the relevant parameter, shared_pool_size, for other objects besides 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 stored procedures reside.

The following is a sample report for gathering information relating to shared_pool_size. As you can see, the data-dictionary hit ratio is above 95 percent, and the library-cache miss ratio is very low. You can see more than 125,000 reloads in the SQL area namespace, however, and in this case you may want to increase the shared_pool_size.


Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO

------------- ---------- ------------
41,750,549 407,609 99
=========================
LIBRARY CACHE MISS RATIO
=========================
(If > 1 then increase the shared_pool_size in init.ora)

executions Cache misses while executing LIBRARY CACHE MISS RATIO
------------- ---------- ------------
22,909,643 171,127 .0075

=========================
Library Cache Section
=========================
hit ratio should be > 70, and pin ratio > 70 . . .

NAMESPACE Hit ratio pin hit ratio reloads
--------- -------- ----------- -------
SQL AREA 84 94 125,885
TABLE/PROCEDURE 98 99 43,559
BODY 98 84 486
TRIGGER 98 97 1,145
INDEX 0 0
CLUSTER 31 33
OBJECT 100 100
PIPE 99 99 52

The following is the SQL*Plus script that generated the report:

prompt
prompt
prompt =========================
prompt DATA DICT HIT RATIO
prompt =========================
prompt (should be higher than 90 else increase shared_pool_size in init.ora) prompt

column "Data Dict. Gets" format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",
sum(getmisses) "Data Dict. cache misses",
trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO"
from v$rowcache;

prompt
prompt =========================
prompt LIBRARY CACHE MISS RATIO
prompt =========================
prompt (If > 1 then increase the shared_pool_size in init.ora)
prompt
column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
(((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"
from v$librarycache;

prompt
prompt =========================
prompt Library Cache Section
prompt =========================
prompt hit ratio should be > 70, and pin ratio > 70 . . .
prompt

column "reloads" format 999,999,999
select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;

Always remember when running this type of report that the statistics are gathered since startup, so the numbers may be skewed. In an Oracle database that has been running for six months, for example, the data-dictionary hit ratio will be a running average over those six months--meaningless information if what you want to measure is today's statistics.

Some DBAs will run STATSPACK or AWR reports which produces a report showing the statistics over the elapsed time interval.

As memory becomes less expensive, 500-megabyte Oracle regions will not be uncommon, but until that time, DBAs must carefully consider the trade-offs involved in pinning a package in the SGA.

Just as the wisdom of the 1980s dictated that data should be centralized, the 1990s began an era in which SQL is also centralized and managed. With centralization of SQL, many tasks that were previously impossible become trivial:
You can easily identify and reuse SQL.
You can extract SQL and run EXPLAIN PLAN utilities, to determine the proper placement of table indexes.
You can search SQL, allowing for fast identification of "where used" information. For example, when a column changes definition, you can quickly identify all SQL that references that column.

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 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 stored procedure scripts, see "Oracle Tuning: The Definitive Reference".