25.3.1 Use Package Data to Minimize SQL Access

When you declare a variable in a package body or specification, its scope is not restricted to any particular procedure or function. As a result, the scope of package-level data is the entire Oracle session, and the value of that data persists for the entire session. Take advantage of this fact to minimize the times you have to access data from the SQL layer. Performing lookups against structures located in your own Program Global Area (PGA) is much, much faster than going through the SGA -- even if the data you want is resident in shared memory.

This tip will come in handy most when you find that your application needs to perform multiple lookups which do not change during your session. Suppose, for example, that one of your programs needs to obtain a unique session identifier to avoid overlaps with other sessions. One of the ways to do this is to call DBMS_LOCK.ALLOCATE_UNIQUE to retrieve a unique lockname. Here is the inefficient way to do this: I need the ID or lockname in the calc_totals procedure. So I make the call to the built-in package right inside the procedure:

PROCEDURE calc_totals
IS
   v_lockname VARCHAR2(100);
BEGIN
   v_lockname := DBMS_LOCK.ALLOCATE_UNIQUE;

   /* Use the lock name to issue a request for data. */
   send_request (v_lockname);

   . . .
END;

The problem with this approach is that every time calc_totals is called, the built-in function is also called to get the unique value -- a totally unnecessary action. After you get it the first time, you needn't get it again.

Packages provide a natural repository for these "session-level" pieces of data. The following sesspkg (session package) defines a variable to hold the lock name and then assigns it a value on initialization of the package:

PACKAGE sesspkg
IS
   lockname CONSTANT VARCHAR2(100) := DBMS_LOCK.ALLOCATE_UNIQUE;
END;

Now the calc_totals procedure can directly reference the package global in its call to send_request. The first time calc_totals is called, the sesspkg package is instantiated and the built-in function called. All subsequent calls to calc_totals within that session will not, however, result in any additional processing inside sesspkg. Instead, it simply returns the value resident in the constant.

PROCEDURE calc_totals
IS
BEGIN
   /* Use the lock name to issue a request for data. */
   send_request (sesspkg.lockname);
END;

You can apply a similar technique for lookups against database tables -- both persistent and session-based. In the following example, the SELECT from v$parameter will be executed only once per session, regardless of how many times the db_name function is executed. Since the database name will never change during the session, this is reasonable. Note also the judicious use of a function with side effects, where one side effect (modification of a persistent package variable) is used to gain the efficiency:

/* Filename on companion disk: dbdata.spp */

CREATE OR REPLACE PACKAGE db_data
IS
   /* function to return the name of the database */
   FUNCTION db_name RETURN VARCHAR2;
END db_data;
/
CREATE OR REPLACE PACKAGE BODY db_data
IS
   /* DB_name variable only manipulated by this function */
   v_db_name  VARCHAR2(8) := NULL;

   /* function to return the name of the database */
   FUNCTION db_name RETURN VARCHAR2
   IS
   BEGIN
      IF v_db_name IS NULL
      THEN
         SELECT SUBSTR(value,1,8) INTO v_db_name
           FROM v$parameter
          WHERE name = `db_name';
      END IF;
      RETURN v_db_name;
   END db_name;
END db_data;

NOTE: You will need to have access to the v$parameter table granted to you from SYS if you want to compile and use this package outside of the SYS account.

For a final example of using package data to reduce SQL I/O and improve performance, Chapter 10, PL/SQL Tables , shows how you can use a PL/SQL table stored in a package to "remember" values which have already been queried in that session. If the value is in the PL/SQL table, the function returns that value. If not, it retrieves the value from the database table and, before returning the value, stores it in the PL/SQL table.

25.3.2 Call PL/SQL Functions in SQL to Reduce I/O

Chapter 17, Calling PL/SQL Functions in SQL , explains how to create and place functions inside SQL statements. There are many advantages to this approach, which can result in cleaner, easier to read SQL and performance improvements. The following example illustrates the use of packaged functions in SQL to minimize I/O in a SQL query (and thereby to improve performance); it also takes advantage of persistent package data to minimize data access.

Suppose we are given a table of address information, primary-keyed by a unique system-generated identifier as follows:

CREATE TABLE  addresses
   (id  NUMBER  NOT NULL PRIMARY KEY
   ,street VARCHAR2(200)
   ,city VARHAR2(200)
   ,state CHAR(2)
   ,ZIP VARCHAR2(20))
/

Suppose also that we have a complex view joining several tables and that one or more columns may contain address identifiers (or be NULL) and that we want to join in a single address according to some prioritization:

CREATE VIEW termination_notices
    (customer_id  NUMBER
    ,termination_reason VARCHAR2(10)
    ,customer_addr_id NUMBER
    ,employer_addr_id NUMBER)
AS
   SELECT ...
/

We want to assemble a new view (or PL/SQL cursor) which will de-reference one of the address ids as follows:

IF customer_addr_id IS NOT NULL
THEN
    use that address
ELSIF spouse_addr_id IS NOT NULL
THEN
    use that address
ELSE
    no address
END IF;

We can create a new view to meet these requirements using UNIONed SELECTs each joining in different addresses, but this will be very complex and could involve multiple scans through the termination_notices view. Another possibility is to use PL/SQL functions to provide address lookups and then include these functions directly in the new view or cursor SELECT statement. One possible problem with this approach is that since we will want to provide all elements of the address in multiple columns, we are in danger of doing address lookups four times per row (once for each column of the addresses table). This can be avoided by making use of the persistent state of package variables:

CREATE OR REPLACE PACKAGE address_info
IS
   /* these are the functions we will call in SQL */
   --
   FUNCTION streetaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.street%TYPE;
   PRAGMA RESTRICT_REFERENCES (streetaddr,WNDS);
   --
   FUNCTION cityaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.city%TYPE;
   PRAGMA RESTRICT_REFERENCES (cityaddr,WNDS);
   --
   FUNCTION stateaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.state%TYPE;
   PRAGMA RESTRICT_REFERENCES (stateaddr,WNDS);
   --
   FUNCTION zipaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.ZIP%TYPE;
   PRAGMA RESTRICT_REFERENCES (zipaddr,WNDS);
   --
END address_info;
/
CREATE OR REPLACE PACKAGE BODY address_info
IS
   /* curr_addr_rec is maintained by load_addr procedure */
   curr_addr_rec   address%ROWTYPE;
   --
   /* procedure to load curr_addr_rec by id            */
   PROCEDURE load_addr(addr_id_IN IN address.id%TYPE)
   IS
   null_addr_rec   address%ROWTYPE;
   BEGIN
      IF curr_addr_rec.id != addr_id_IN OR curr_addr_rec.id IS NULL
      THEN
         SELECT *
           INTO curr_addr_rec
           FROM address
          WHERE id = addr_id_IN;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN curr_addr_rec := null_addr_rec;
   END load_addr;
   --
   /* functions which return components of curr_addr_rec */
   FUNCTION streetaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.street%TYPE
   IS
   BEGIN
      load_addr(addr_id_IN);
      RETURN curr_addr_rec.street;
   END streetaddr;
   --
   FUNCTION cityaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.city%TYPE
   IS
   BEGIN
      load_addr(addr_id_IN);
      RETURN curr_addr_rec.city;
   END cityaddr;
   --
   FUNCTION stateaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.state%TYPE
   IS
   BEGIN
      load_addr(addr_id_IN);
      RETURN curr_addr_rec.state;
   END stateaddr;
   --
   FUNCTION zipaddr(addr_id_IN IN address.id%TYPE)
      RETURN address.ZIP%TYPE
   IS
   BEGIN
      load_addr(addr_id_IN);
      RETURN curr_addr_rec.ZIP;
   END zipaddr;
   --
END address_info;
/

Following is a version of the view built upon this package which gives us the addresses to which to mail termination notices.

CREATE VIEW termination_notice_mailing
IS
SELECT  customer_id
,DECODE(customer_addr_id,NULL,
                 DECODE(employer_addr_id,NULL,TO_CHAR(NULL)
                           ,address_info.streetaddr(employer_addr_id))
               ,address_info.streetaddr(customer_addr_id) )
        street
,DECODE(customer_addr_id,NULL,
                 DECODE(employer_addr_id,NULL,TO_CHAR(NULL)
                            ,address_info.cityaddr(employer_addr_id))
               ,address_info.cityaddr(customer_addr_id) )
        city
,DECODE(customer_addr_id,NULL,
                 DECODE(employer_addr_id,NULL,TO_CHAR(NULL)
                            ,address_info.stateaddr(employer_addr_id))
               ,address_info.stateaddr(customer_addr_id) )
        state
,DECODE(customer_addr_id,NULL,
                 DECODE(employer_addr_id,NULL,TO_CHAR(NULL)
                            ,address_info.zipaddr(employer_addr_id))
               ,address_info.zipaddr(customer_addr_id) )
        ZIP
 FROM termination_notices;

Notice that the DECODEs handle the prioritization of which address to use based on whether customer_addr_id and employer_addr_id are NULL. Notice also that the package ensures that we will hit the address table at most once per row selected. Certainly this is still a complex view, but we only have to scan the termination_notices view once, and we join in the correct address only when we need it.

As you can see, taking full advantage of all of this technology to improve performance doesn't always result in very attractive code. When you are faced with programs that do not meet minimal requirements, however, you must be ready to abandon elegance and even (as a last resort) simplicity to get the job done.

25.3.3 Avoid Client-Side SQL

Set as a goal for yourself or your entire development team that no one ever places a chunk of SQL code on the client side of the divide. Why? Because if you put all SQL or data access inside stored code in the database you get the following:

• You are much more likely to share that same SQL across multiple programs or even applications. This will result in a higher percentage of shared, preparsed SQL.

• Multiple SQL statements can be batched together into a single procedure or function, so that a single network access kicks off a sequence of SQL activity on the server without unnecessary network traffic.

Objections to this advice are often raised by Oracle Developer/2000 developers. When you create a form in Oracle Forms, the base table block takes care of an awful lot of the required SQL, including some very complex locking mechanisms. Am I saying that you should abandon all of that "automatic" stuff and write a large volume of code? Definitely not (though with Oracle Developer/2000 Release 2 you will be able to build "base table blocks" around stored procedures and functions instead of using tables). I would strongly recommend, though, that the only SQL you allow in your Oracle Forms modules is the stuff generated automatically for you.

You should most certainly never write POST-QUERY triggers (as an example) which contain multiple SELECT statements to do foreign key lookups. This is the most common place for additional SQL to show up in a form. Your tables are wonderfully normalized and you must, therefore, translate a key into a descriptor for display purposes. How do you do this? In the POST-QUERY trigger, you issue implicit SELECT after implict SELECT to get the data. Your trigger then looks like this:

BEGIN
   SELECT dept_name INTO :emp.dname
     FROM department
    WHERE dept_id = :emp.deptid;

   SELECT title_name INTO :emp.tname
     FROM titles
    WHERE title_id = :emp.title_id;

   /* and so on and so on... */
END;

This is very bad news for at least two reasons: First, chances are that you have this same or similar code in some other program. Surely this can't be the only place you look up a department name from a department ID. Second, you are forcing lots of unnecessary network activity.

Ideally, you should consolidate all the parts of your POST_QUERY triggers that contain SQL statements into a single procedure, preferably inside a package, as shown here:

BEGIN
   emppkg.lookups (:emp.deptid, :emp.dname, :emp.title_id, :emp.tname);
END;

The emppkg.lookups procedure bundles together each of the different foreign key lookups, which means that it requires just one network call to get the job done.

25.3.4 Take Advantage of DBMS_SQL Batch Processing

The PL/SQL8 version of DBMS_SQL allows you to perform bulk updates, inserts, deletes, and queries. The new version of this handy package (which allows for the execution of dynamic SQL and dynamic PL/SQL inside PL/SQL programs) offers the following programs to support "array processing":

DBMS_SQL. DEFINE_ARRAY

This procedure allows you to associate a column in a cursor with a PL/SQL table. When you fetch from the cursor, you will be able to dump a specified number of values from multiple rows into that PL/SQL table.

DMBS_SQL. BIND_ARRAY

This procedure allows you to bind multiple values from a PL/SQL table to a DML statement, so that you can, say, insert 100 rows in a single call to DBMS_SQL.EXECUTE, instead of having to make 100 calls to this function.

See Oracle Built-in Packages for more information on this feature. It is the closest you can get to true array processing for SQL inside a PL/SQL program.

25.3.5 Avoid Procedural Code When Possible

Compared to Oracle SQL, PL/SQL is a slow language when it comes to processing SQL statements. So whenever possible, you should take advantage of set-oriented SQL statements instead of row-at-a-time cursor processing in PL/SQL. Consider the following block of code:

DECLARE
   CURSOR table_X_cur
   IS
      SELECT column_A,column_B,column_C
        FROM table_X
         FOR UPDATE of column_A;
   table_X_rec table_X_cur%ROWTYPE;
BEGIN
   OPEN table_X_cur;
   FETCH table_X_cur INTO table_X_rec;
   WHILE table_X_cur%FOUND
   LOOP
      UPDATE table_X
         SET column_A = table_X_rec.column_B * table_X_rec.column_C
       WHERE CURRENT OF table_X_cur;
      FETCH table_X_cur INTO table_X_rec;
   END LOOP;
   CLOSE table_X_cur;
END;

You can replace all of that stuff with this single UPDATE statement:

UPDATE table_X  SET column_A = column_B * column_C;

I hope you will find this tip to be self-evident; you would never be tempted to write a whole bunch of PL/SQL code when a simple UPDATE statement would suffice! It is worth the reminder, however, because as we spend more and more of our time cranking out PL/SQL code, we can easily lose perspective. We might find ourselves trying to do everything in PL/SQL and forget about our other options.

25.3.6 Use PL/SQL to Improve Performance of IO-Intensive SQL

Now that I have finished telling you to use SQL whenever possible and avoid PL/SQL, it is time to urge you to use PL/SQL, particularly explicit cursors, to improve the performance of some types of SQL statements. Go figure!

Actually, it shouldn't be the least bit confusing. SQL is optimized for set-at-a-time processing. PL/SQL is designed for record-at-a-time logic. If your interaction with the database always occurs on an entire set, or between more than one set related through equi-joins, SQL is the obvious choice. If your requirements dictate that you must perform procedural-style logic on the data in order to produce the desired result, PL/SQL could have a dramatic impact on the performance of your program.

A good indicator of the potential for improvement with PL/SQL is the presence of a correlated sub-query. Consider the following query, which displays the department name followed by the name of the employee who receives the highest salary in that department:

SELECT 'Top employee in ' || D.name || ' is ' ||
         E.last_name || ', ' || E.first_name
  FROM department D, employee E
 WHERE D.department_id = E.department_id
   AND E.salary = (SELECT MAX (salary)
                     FROM employee E2
                    WHERE E2.department_id = E.department_id);

The records retrieved by the inner SELECT statement (which calculates the maximum salary for a department) is correlated to the department ID of the outer employee record. The maximum salary for a department is therefore calculated again and again for each employee record in the outer query. If the company has five departments and 1000 employees in those five departments, then the query will perform 1000 subqueries. Yet the maximum salary really needs to be computed only five times, once per department.

You can use PL/SQL and a cursor FOR loop to cut to a minimum the number of times the department maximum is calculated. The resulting execution time will be greatly reduced, especially for large numbers of records in the employee table:

PROCEDURE display_best_paid IS
/*
|| Display the name of the employee who makes the highest salary
|| in each department.
*/
   -- Cursor for each department (and its max salary) in the company.
   CURSOR dept_cur IS
      SELECT D.department_id, name, MAX (salary) max_salary
        FROM department D, employee E
       WHERE D.department_id = E.department_id
       GROUP BY D.department_id
       ORDER BY name;
BEGIN
   <<dept_loop>>
   FOR dept_rec IN dept_cur
   LOOP
      DECLARE
         CURSOR emp_cur IS
            SELECT last_name || ', ' || first_name emp_name
              FROM employee
             WHERE department_id = dept_rec.department_id
               AND salary = dept_rec.max_salary;
         emp_rec emp_cur%ROWTYPE;
      BEGIN
         OPEN emp_cur;
         FETCH emp_cur INTO emp_rec;
         DBMS_OUTPUT.PUT_LINE
            ('Top employee in ' || dept_rec.name || ' is ' ||
             emp_rec.emp_name);
         CLOSE emp_cur;
      END;
   END LOOP <<dept_loop>>;
END;

With the cursor FOR loop approach, the maximum salary is calculated once for each department through the use of a GROUP BY. This value is then used inside the loop to find the first employee whose salary matches the maximum. This program performs several hundred fewer logical reads than the correlated subquery version.

This technique also comes in handy when you have a very complex SQL statement involving a large number of tables. The rules-based optimizer -- still used widely in Oracle shops around the world -- does not use table size to determine which indexes should be applied to a query. So, for example, if you have a very small table in the query included to look up a description for a code, the optimizer might use the index on that table and ignore a much more useful index in a larger table. You can help avoid confusion and, at the same time, improve performance of the query by pulling that reference table out of the query and placing it in its own cursor. Your program then executes the cursor and fetches the description before the main query executes. You then reference the retrieved value directly in the query and simplify the main query.

25.3.7 Keep Database Triggers Small

Up until Oracle7 Server Release 7.1, database triggers are treated like anonymous blocks of code in the System Global Area (SGA). Compiled versions of triggers are not stored in the database. Instead, the source code is recompiled each time the trigger is referenced.

To minimize the overhead associated with these repeated compilations, you should keep your database triggers as small as possible. You should also tune your shared pool size so that it can accommodate most, if not all, of your compiled PL/SQL code without having to age out objects which are used repeatedly.

The best way to keep your triggers small is to transfer all of the procedural logic in the trigger to a procedure. This procedure's compiled version is stored in the database and can be managed more efficiently by the instance's SGA.

To change your trigger code to a procedure call, you move all of the code inside the procedure and create a parameter for each trigger-specific variable referenced in the code (such as :new and :old variables, which allow you to compare new and old values for columns in the affected row).

Let's convert a trigger's complex logic to a procedure. In the following trigger, I maintain a denormalized order total based on the line items for that order. This single trigger handles any changes to the line item amount, through an INSERT, UPDATE, or DELETE:

CREATE TRIGGER total_order
AFTER INSERT OR UPDATE OR DELETE OF line_amt ON line_item
FOR EACH ROW
BEGIN
   /*
   || If changed order for this line item or removing it,
   || then decrease the total in the old order.
   */
   IF (UPDATING AND :old.order_id != :new.order_id) OR DELETING
   THEN
      UPDATE orders
         SET order_total := order_total - :old.line_amt
       WHERE order_id = :old.order_id;
   END IF;
   /*
   || If a new line item or transferring line item to new order,
   || add the line amount to the new order.
   */
   IF (UPDATING AND :old.order_id != :new.order_id) OR INSERTING
   THEN
      UPDATE orders
         SET order_total := order_total + :new.line_amt
       WHERE order_id = :new.order_id;
   END IF;
   /*
   || If I have changed the amount of the line item and kept it
   || in the same order, adjust total by difference of old and
   || new line item amounts.
   */
   IF :old.order_id = :new.order_id AND
      :old.line_amt != :new.line_amt AND
      UPDATING
   THEN
      UPDATE orders
         SET order_total := order_total - :old.line_amt + :new.line_amt
       WHERE order_id = :new.order_id;
   END IF;
END;

Before I write the procedure, let's decide on the specification for the procedure -- that is, what the trigger should look like inside:

CREATE TRIGGER total_order
AFTER INSERT OR UPDATE OR DELETE OF line_amt ON line_item
FOR EACH ROW
BEGIN
   adjust_order_total
      (:old.order_id, :old.line_amt, :new.order_id, :new.line_amt,
       INSERTING, UPDATING, DELETING);
END;

The adjust_order_total contains many parameters, since it is a combined trigger (handling all three DML states: INSERT, UPDATE, and DELETE) and also references the new and old values of two different columns. With this specification, I can now create the procedure as a direct "translation" from the trigger:

PROCEDURE adjust_order_total
      (old_order_id_in IN orders.order_id%TYPE,
       old_line_amt_in IN line_item.line_amt%TYPE,
       new_order_id_in IN orders.order_id%TYPE,
       new_line_amt_in IN line_item.line_amt%TYPE,
       inserting_in IN BOOLEAN,
       updating_in IN BOOLEAN,
       deleting_in IN BOOLEAN)
BEGIN
   /*
   || If changed order for this line item or removing it,
   || then decrease the total in the old order.
   */
   IF (updating_in AND old_order_id_in != new_order_id_in)
        OR deleting_in
   THEN
      UPDATE orders
         SET order_total := order_total - old_line_amt_in
       WHERE order_id = old_order_id_in;
   END IF;
   /*
   || If a new line item or transferring line item to new order,
   || add the line amount to the new order.
   */
   IF (updating_in AND old_order_id_in != new_order_id_in) OR
        inserting_in
   THEN
      UPDATE orders
         SET order_total := order_total + new_line_amt_in
       WHERE order_id = new_order_id_in;
   END IF;
   /*
   || If I have changed the amount of the line item and kept it
   || in the same order, adjust total by difference of old and
   || new line item amounts.
   */
   IF old_order_id_in = new_order_id_in AND
      old_line_amt_in != new_line_amt_in AND
      updating_in
   THEN
      UPDATE orders
         SET order_total :=
               order_total - old_line_amt_in + new_line_amt_in
       WHERE order_id = new_order_id_in;
   END IF;
END;

By converting the trigger code to a procedure, I have not made the code more readable, nor is it more concise. The point of this conversion was, however, simply to improve transaction performance.

Finally, even if you are running Oracle Server 7.3 or above, you should still keep your trigger code to a minimum and instead place as much of your logic as possible in packages so that this code can be more easily reused throughout your application.