Oracle - Performance Goals:

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 devoted to providing technology as 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.

Oracle - Benefits of using stored procedures:

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:

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

Performance:

Stored procedures are loaded once into the SGA and remain there unless they become paged out. Successive executions of the stored procedure are far faster than external code.

Coupling:

Speed:

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 rapidly, and the trick is to prevent pool-thrashing while many procedures compete for a limited amount of shared-pool memory.

Storage available to Cache:

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.

Encapsulation with Packages:

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:

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.

Oracle shared pool:

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

Paging:

The shared pool utilizes a "least recently used" algorithm to determine which objects get paged-out of the shared pool. As this paging occurs, discontiguous chunks of memory are created within the shared pool.

Wait for my next article Oracle: Achieving performance goal - Part II in which I will discusss the oracle performance goals in more detail.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   The power of Oracle Diagnostic Tools!
   Great Tips on Tuning Database Materialized Views
   Efficient Tips for Renaming Oracle Instance!!
   Is your Data Warehouse Protected??