About Oracle Stored Procedures 

Using Oracle8 Stored Procedures

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 has been encouraging this approach in anticipation of the object-oriented features of Oracle Version 8. 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.

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 = ‘EMNPLOYEE’), 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.  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_block_buffers 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 that 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:

Dictionary cache
Library cache
Shared SQL areas
Private SQL area (exists during cursor open-cursor close)
persistent area
runtime area

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.

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.

To address these issues and simplify Oracle security, Oracle introduced the grant execute method. Using the grant execute method, all data access code is encapsulated into Oracle stored procedures, and the end users are granted the ability to execute the code.