Referential integrity has earned a bad reputation in Oracle because of the overhead that is created when enforcing the business rules. In almost every case, it will be faster and more efficient to write your own rules to enforce RI instead of having Oracle do it for you. Provided that your application does not allow ad hoc query, it is relatively easy to attach a trigger with a PL/SQL routine to enforce the RI on your behalf. In fact, this is one of the best uses of a trigger, since the DML DELETE event will not take place if the RI rules are invalid. For example, consider the foreign key constraint that protects a customer from being deleted if they have outstanding orders:

CREATE TABLE CUSTOMER (
    cust_id                NUMBER
    CONSTRAINT cust_ukey UNIQUE (cust_id),
    cust_name              VARCHAR(30),
    cust_address           VARCHAR(30);)
CREATE TABLE ORDER (
    order_id               NUMBER,
    order_date             DATE,
    cust_id                NUMBER
    CONSTRAINT cust_fk REFERENCES CUSTOMER ON DELETE RESTRICT,
);

To ensure that SQL*Plus has no ad hoc updates, it can be configured to disallow update operations. This is accomplished with the PRODUCT_USER_PROFILE table. Issuing the following row into this table will disable any ad hoc updates with SQL*Plus:

INSERT INTO PRODUCT_USER_PROFILE (product, user_id, attribute)
VALUES (
        'SQL*Plus',
        '%',
        'UPDATE');

Are you now free to write your own procedural RI without fear of accidental corruption? Actually, there is another way that users can access ad hoc query, thereby bypassing your business rules. A user on a PC (with SQL*Net installed) can access Oracle using ODBC without ever entering SQL*Plus. So beware, and be sure that all ad hoc holes have been plugged before attempting to write your own RI rules.

Another problem with RI occurs when two tablespaces each contain tables that have foreign key rules into the other tablespace. The DBA must commonly drop and rebuild the tablespaces as a part of routine database compression. For example, when trying to drop a tablespace (say TS1) that has RI into another tablespace, the DROP TABLESPACE CASCADE will fail because foreign key references are contained in table B in tablespace TS2. Conversely, the DBA cannot drop tablespace TS2 because it has references into tablespace TS1. This turns DBA maintenance into a nightmare, since all constraints must be identified and disabled from TS2 in order to drop tablespace TS1.

Oracle 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. Many compelling benefits can be derived by placing 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 (e.g., EMPLOYEE.hire, EMPLOYEE.give_raise), then the data dictionary can be queried to list all behaviors associated with a table (for instance, select * from dba_objects where owner = ‘EMPLOYEE’), and code can be readily identified and reused.

•  Isolation of Code—Since all SQL is moved out of the external programs and into stored procedures, the application programs are 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 Oracle System Global Area (SGA). After a procedure has been loaded into the SGA, it will remain in the library cache until it is paged out of memory. Items are paged out of memory based upon 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 during the period when many procedures are competing for a limited amount of library cache within the shared pool memory.

When tuning Oracle, two init.ora parameters emerge as more important than all of the other parameters 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 determines 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();1

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 into the database, reducing the application programs into nothing more than a series of calls to Oracle 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 subpools:

1.  Dictionary cache

2.  Library cache

3.  Shared SQL areas

4.  Private SQL area (these exist during cursor open–cursor close)

a)  persistent area

b)  runtime area

As we have mentioned, the shared pool utilizes a “least-recently-used” algorithm to determine which objects are paged out of the shared pool. As this paging occurs, “fragments” or discontiguous chunks of memory are created within the shared pool.