Faster Performance using Oracle Stored Procedures Oracle Tips by Burleson Consulting October 2000 - Updated March 2007

As objects such as stored procedures and triggers become more popular, more application code will move away from external programs and into the database engine. Oracle is committed to providing technology an in Oracle 10g we can store and deploy applications with all of the process logic stored (either Java or PL/SQL), all stored inside the database. However, the Oracle DBA must be conscious of the increasing memory demands of stored procedures, and carefully plan for the days when all of the database access code resides within the database.

For more details on optimizing Oracle stored procedure performance see  my book "Oracle Tuning: The Definitive Reference".

Today, most Oracle 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. These include:

• Better performance - Stored procedures are loaded once into the SGA and remain there unless they become paged out. Subsequent executions of the stored procedure are far faster than external code.
   
• Coupling of data with behavior - Relational tables can be coupled with the behaviors that are associated with them by using naming conventions. For example, if all behaviors associated with the employee table are prefixed with the table name (i.e. employee.hire, employee.give_raise), then the data dictionary can be queried to list all behaviors associated with a table (i.e. select * from dba_objects where owner = ‘EMPLOYEE'), and code can be readily identified and re-used.
   
• Isolation of code - Since all SQL is moved out of the external programs and into 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 of the foremost reasons why stored procedures and triggers function faster than traditional code is related to the Oracle System Global Area (SGA). After a procedure has been loaded into the SGA, it will remain there until it is paged-out of memory. Items are paged-out based on a least-recently-used algorithm. Once loaded into the RAM memory of the shared pool, the procedure will execute very quickly, and the trick is to prevent pool-thrashing while 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. These are the db_cache_size and the shared_pool_size parameters. These two parameters define 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.

Oracle also provides a construct called a "package." Essentially, a package is a collection of functions and stored procedures and can be organized in a variety of ways. For example, functions and stored procedures for employees can be logically grouped 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;

Here we have encapsulated all employee "behaviors" into a single package that will be added into Oracle's data dictionary. If we force our 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.

Preparing the SGA for Packages and Stored Procedures

As systems evolve and the majority of process code resides in stored procedures, Oracle?s shared pool becomes very important. The shared pool consists of the following sub-pools:

1.  Dictionary cache
2.  Library cache
3.  Shared SQL areas
4. Private SQL area (exists during cursor open-cursor close)
   1. persistent area
   2. runtime area

The shared pool utilizes a "least recently used" 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 being similar to a tablespace. While you may get ora-1547 when you cannot get sufficient contiguous free space in the tablespace, similarly, you will get ora 4031 when you cannot get contiguous free space in the SHARED_POOL(sga).

This means that a large procedure that initially fit into memory, 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 paged out of the instance?s SGA because of other more recent/frequent activity. Fragmentation then occurs, and the server cannot find enough contiguous memory to re-load the package body, resulting in an ORA-4031 error.

How to Pin Oracle Packages

To prevent paging, packages can be marked 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 a procedure dbms_shared_pool.keep to pin a package. Packages can be unpinned with dbms_shared_pool.unkeep. NOTE: Only packages can be pinned. Stored procedures should be placed into a package if they are to be pinned.

The choice of whether to "pin" a procedure in memory is a function of the size of the object and the frequency that it is used. Very large procedures that are called frequently might benefit from pinning, but you might never notice any difference because the frequent calls to the procedure have kept it loaded into memory. Therefore, since the object never pages-out, the pinning has no effect.

In an ideal world, the shared_pool parameter of the init.ora should be large enough to accept every package, stored procedure and trigger that may be invoked by the applications. Reality, however, dictates that the shared pool cannot grow indefinitely, and wise choices must be made regarding which objects are fenced.

Oracle recommends that the STANDARD, DBMS_STANDARD, DBMS_UTILITY, DBMS_DESCRIBE and DBMS_OUTPUT packages always be pinned in the shared pool.

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

A standard procedure can be written 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');

OWNER	TYPE	NAME	EXECUTIONS	MEM_USED	Kept
-----	--------	----------------	----------	--------	---
SYS	PACKAGE	STANDARD	867,600	151,963	YES
SYS	PACKAGE BODY	STANDARD	867,275	0,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	S125_PACKAGE	285,700	3,776	NO
SYS	PACKAGE	DBMS_UTILITY	284,694	3,311	NO
SYS	PACKAGE BODY	DBMS_UTILITY	284,694	6,159	NO
HRIS	PACKAGE	HRS_COMMON_PACKAGE	258,657	3,382	NO
HRIS	PACKAGE BODY	S125_PACKAGE	248,857	30,928	NO
HRIS	PACKAGE BODY	HRS_COMMON_PACKAGE	242,155	8,638	NO
HRIS	PACKAGE	GTS_SNAPSHOT_UTILITY	168,978	11,056	NO
HRIS	PACKAGE BODY	GTS_SNAPSHOT_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_SESSION_RECOR	62	7,147	NO
HRIS	PROCEDURE	INIT_APP_ALERT	6	10,802	NO

1. Data dictionary hit ratio
2. Library cache miss ratio
3. Individual hit ratios for all namespaces

Also, be aware that the relevant parameter, shared_pool_size, is used for other objects besides stored procedures. This means that one parameter fits all, and Oracle offers no method for isolating the amount of storage allocated to any subset of the shared pool.

Here is a sample report for gathering information relating to shared_pool_size. As you can see, the data dictionary hit ratio is more than 95 percent, and the library cache miss ratio is very low. However, we see more than 125,000 reloads in the SQL area namespace and may want to increase the shared_pool_size. Always remember when running this type of report that the statistics are gathered since startup, and the numbers may be skewed. For example, for a system that has been running for six months, the data dictionary hit ratio will be a running average over six months. Of course, this is meaningless if you want to measure today's statistics.

Some DBA's will run utlbstat.sql, wait one hour, and run utlestat.sql. This produces a report that shows the statistics over the elapsed time interval.

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


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

Here 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;

As memory becomes cheaper, 500 MB Oracle regions will not be uncommon, but until that time, the DBA must carefully consider the ramifications of pinning a package in the SGA.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.