29-1.

The statements are:

29-2.

(c). You can often get a big performance boost by caching, or saving, frequently used values (e.g., a table of status codes) as PL/SQL variables.

29-3.

Check to see if Oracle has already written it! Becoming familiar with the host of procedures, functions, and packages built in to Oracle will make you a more efficient and productive developer. In addition, Oracle has tested and tuned these programs, giving you one less worry.

29-4.

An algorithm is a process; a program is an implementation of an algorithm in a particular language. The classic analogy is that an algorithm is like the recipe for a cake. It tells you the steps (the algorithm) you need to follow to make a cake (the program). Unless you’re very hungry, you don’t eat the recipe; you eat the cake it tells you how to create (whether or not the cake is edible is a function of the skill of the cook!).

29-5.

(b). Packages provide a great way to store frequently needed values. For example, you could load the contents of a lookup table into an index-by table to minimize the number of database fetches a program makes.

29-6.

The following list matches the tuning tool to its description:

29-7.

(d). Compiled code is loaded into the System Global Area (SGA) when a PL/SQL program executes.

29-8.

(b). Session data (e.g., packages, variables, cursors, etc.) are stored in the User Global Area (UGA).

29-9.

(e). You can use the DBMS_SHARED_POOL package (using the KEEP procedure) to keep a PL/SQL program in memory, particularly a frequently used package. Typically, you pin selected packages in the database startup routines.

29-10.

Avoiding repetitive calculations inside a loop is a key tuning concept; the following procedure is faster because it evaluates the UPPER function (which doesn’t change) only once:

PROCEDURE process_data (nm_in IN VARCHAR2) IS
   v_nm some_table.some_column%TYPE := UPPER (nm_in);
BEGIN
   FOR rec IN pkgd.cur
   LOOP
      process_rec (v_nm, rec.total_production);
   END LOOP;
END;

29-11.

Oracle recommends that you avoid applying the NOT NULL constraint to a declaration, and instead check for a NULL value yourself. Therefore, the following rewrite is better:

DECLARE
   my_value INTEGER := 0;
BEGIN
   IF my_value IS NULL THEN /* ERROR! */
   ELSIF my_value > 0 THEN ...
END;

29-12.

“SELECT COUNT(*) INTO emp_count” queries are inefficient and expensive ways to determine if at least one record exists in a table that meets a particular criterion (in this case, whether any employees exist for the given department). Fetching the first row and using an IF statement is a much faster way to test whether at least one row exists in a query:

CREATE OR REPLACE PROCEDURE drop_dept (
   deptno_in          IN NUMBER,
   reassign_deptno_in IN NUMBER
)
IS
   CURSOR my_cur IS
      SELECT COUNT (*)
        INTO temp_emp_count
        FROM emp
       WHERE deptno = deptno_in;
   cur_rec my_cur%ROWTYPE;

BEGIN
   OPEN my_cur;
   FETCH my_cur INTO cur_rec;
   IF my_cur%FOUND
   THEN
      UPDATE emp
         SET deptno = reassign_deptno_in
       WHERE deptno = deptno_in;
   END IF;

   CLOSE my_cur;

   DELETE
     FROM dept
    WHERE deptno = deptno_in;

   COMMIT;
END drop_dept;

29-13.

(c). The PLS_INTEGER datatype utilizes the machine’s native arithmetic functions.

29-14.

Snippet (b) is faster because it exits as soon as the condition is met. In snippet (a), all 2045 conditions are always checked.

29-15.

Trigger (a) is better because the WHEN and UPDATE OF clauses cause it to fire only when the desired conditions are met; Trigger (b) fires regardless of what happens. For example, suppose you update on the dept columns. Trigger (a) doesn’t do anything; Trigger (b) still performs the test for each row.

29-16.

One possible way to improve the code is to avoid repeating unnecessary calculations inside the loop:

DECLARE
   v_today CONSTANT VARCHAR2(10) := TO_CHAR (SYSDATE, 'MM/DD/YYYY'),

   CURSOR emp_cur
   IS
      SELECT SUBSTR (last_name, 1, 20) last_name FROM employee;
BEGIN
   FOR rec IN emp_cur
   LOOP
      process_employee_history (rec.last_name, v_today);
   END LOOP;
END;

29-17.

One of the best ways to simplify maintenance and minimize the number of times a SQL statement must be parsed is to replace literals with bind variables. You can almost always improve a cursor simply by replacing hardcoded values with bind variables:

CURSOR name_cur (dept IN INTEGER) IS
      SELECT last_name FROM employee
       WHERE department_id = dept;
BEGIN
   OPEN marketing_cur(20);

29-18.

You can get a performance boost by replacing repetitive, cursor-based PL/SQL loops with “pure” SQL statements:

UPDATE emp SET sal = sal * 1.01;

29-19.

Correlated subqueries and complex multiple joins can result in excessive processing. You can improve the complex SQL statement by first breaking it into two cursors (using bind variables where appropriate) and then processing the cursors inside a PL/SQL procedure. Here is a rewrite of the original query:

CURSOR dept_cur IS
   SELECT department_id, MAX (salary) max_salary
     FROM employee E GROUP BY department_id;

CURSOR emp_cur (dept IN PLS_INTEGER,maxsal IN NUMBER) IS
   SELECT last_name || ', ' || first_name emp_name
     FROM employee
    WHERE department_id = dept AND salary = maxsal;

29-20.

The following package uses a global package variable to cache the current user:

CREATE OR REPLACE PACKAGE thisuser
IS
   /* Persistent "global" variable */
   g_user VARCHAR2(30) := USER;
END;

29-21.

Here is the implementation of the lookup package:

/* Filename on web page: lookup.pkg */
CREATE OR REPLACE PACKAGE te_company
IS
   FUNCTION name$val (id_in IN company.company_id%TYPE)
      RETURN company.name%TYPE;
END te_company;
/
CREATE OR REPLACE PACKAGE BODY te_company
IS
   TYPE names_tabtype IS TABLE OF company.name%TYPE
      INDEX BY BINARY_INTEGER;

   names names_tabtype;

   FUNCTION name$val (id_in IN company.company_id%TYPE)
      RETURN company.name%TYPE
   IS
      CURSOR comp_cur
      IS
         SELECT name
           FROM company
          WHERE company_id = id_in;

      retval company.name%TYPE;
   BEGIN
      IF names.EXISTS (id_in)
      THEN
         retval := names (id_in);
      ELSE
         OPEN comp_cur;
         FETCH comp_cur INTO retval;
         CLOSE comp_cur;

         IF retval IS NOT NULL
         THEN
            names (id_in) := retval;
         END IF;
      END IF;

      RETURN retval;
   END name$val;
END te_company;
/

29-22.

Three SQL statements are parsed when the block is executed. Since the first two standalone queries are physically different, they are cached separately. The two statements inside the PL/SQL block, however, are treated as a single statement because the PL/SQL engine reformats them so that they are physically (and logically) equivalent.

29-23.

You can use the Oracle8 RETURNING clause to combine the two operations into a single (faster) SQL statement:

BEGIN
   INSERT INTO UnionBuster VALUES (ub_seq.NEXTVAL, 'Prison', 5)
      RETURNING ub_id, hourly_wage
           INTO v_latest_bustID, v_hard_to_beat;
END;

29-24.

If you’ve defined a cursor using the SELECT FOR UPDATE clause, you can use the WHERE CURRENT OF clause to speed up subsequent UPDATE or DELETE statements:

LOOP
   FETCH cur INTO rec;
   EXIT WHEN cur%NOTFOUND;

   UPDATE employee SET last_name = UPPER (last_name)
    WHERE CURRENT OF cur;
END LOOP;

29-25.

You can use the Oracle8i FORALL statement to perform the entire operation in just one line:

PROCEDURE whack_emps_by_dept (deptlist dlist_t)
IS
BEGIN
   FORALL aDept IN deptlist.FIRST..deptlist.LAST
      DELETE emp WHERE deptno = deptlist(aDept);
END;

29-26.

Here is the SQL statement that completes the program:

SELECT empno, ename, hiredate
      BULK COLLECT INTO enos, names, hdates
      FROM emp
     WHERE deptno = deptno_in;

29-27.

The following script formats and displays objects that are larger than a user-supplied size:

SET PAGESIZE 66
COLUMN name FORMAT A30
COLUMN type FORMAT A15
COLUMN source_size FORMAT 999999
COLUMN parsed_size FORMAT 999999
COLUMN code_size FORMAT 999999
TTITLE 'Size of PL/SQL Objects > &1 KBytes'
SPOOL pssize.lis
SELECT name, type, source_size, parsed_size, code_size
  FROM user_object_size
 WHERE code_size > &1 * 1000
 ORDER BY code_size DESC
/
SPOOL OFF
TTITLE OFF

29-28.

The matches are:

29-29.

There are several possible ways to improve the program. The following optimization eliminates the nested loops by accumulating the schedule in reverse, resulting in a program that is both faster and easier to understand (note that there are several versions on the book’s web page):

/* Filename on web page: presvalue.sql */
PROCEDURE build_lease_schedule
IS
   pv_total_lease NUMBER (9) := 0;
   one_year_pv NUMBER (9) := 0;
BEGIN
   fixed_count := 0;
   var_count := 0;

   FOR year_count IN REVERSE 1 .. 20
   LOOP
      one_year_pv :=
         pv_of_fixed (year_count) +
         pv_of_variable (year_count);
      pv_total_lease :=
         pv_total_lease + one_year_pv;
      pv_table (year_count) :=
         pv_total_lease;
   END LOOP;
END;

29-30.

You can create an anonymous PL/SQL block within an IF statement to declare and use memory only when necessary:

PROCEDURE only_as_needed (...) IS
BEGIN
   IF <condition>
   THEN
      DECLARE
         big_string VARCHAR2(32767) :=  ten_minute_lookup (...);
         big_list list_types.big_strings_tt;
      BEGIN
         use_big_string (big_string);
         Process_big_list (big_list);
      END;
   ELSE
      /* Nothing big
         going on here */
      ...
   END IF;
END;

29-31.

You can use native dynamic SQL to easily change the status of a trigger:

/* Filename on web page: settrig_status.sp */
CREATE OR REPLACE PROCEDURE settrig (tab IN VARCHAR2, action IN VARCHAR2)
IS
   v_action VARCHAR2 (10) := UPPER (action);
   v_other_action VARCHAR2 (10) := 'DISABLE';
BEGIN
   IF v_action = 'DISABLE'
   THEN
      v_other_action := 'ENABLE';
   END IF;

   FOR rec IN (SELECT trigger_name
                 FROM user_triggers
                WHERE table_owner = USER
                  AND table_name = UPPER (tab)
                  AND status = v_other_action)
   LOOP
      EXECUTE IMMEDIATE 'ALTER TRIGGER ' ||
                        rec.trigger_name ||
                        ' ' ||
                        v_action;
      DBMS_OUTPUT.put_line (
         'Set status of ' || rec.trigger_name || ' to ' || v_action
      );
   END LOOP;
END;

29-32.

You can use DBMS_JOB to run the calc_comp procedure in parallel:

DECLARE
   job# PLS_INTEGER;
   PROCEDURE doit (job IN VARCHAR2) IS BEGIN
      DBMS_JOB.SUBMIT (
         job#, 'calc_comp (''' || job || ''')', SYSDATE, NULL);
      COMMIT;
   END;
BEGIN
   doit ('CLERK'),
   doit ('VP'),
   doit ('PROGRAMMER'),
END;

29-33.

Yes, the fact that the compensation at one level is the sum of the sublevels affects your ability to run calc_comp in parallel; the processes started by DBMS_JOB must be independent (i.e., not rely on the results of a previous calculation).

29-34.

Although statements (a) and (b) do the same thing (i.e., they are logically equivalent), they are structurally different (version (a) uses “2” as a multiplier, version (b) uses “2.0”). The effect of this small difference is that it prevents the PL/SQL engine from using the preparsed cursor for (a) in computing the result set of (b). In order to reuse two logically equivalent statements, they must be physically identical.

29-35.

Your script must query the all_dependencies data dictionary table:

SELECT owner || '.' || name refs_table,
       referenced_owner || '.' || referenced_name table_referenced
  FROM all_dependencies
 WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
   AND referenced_type IN ('TABLE', 'VIEW'),

29-36.

You can use the bulk collection operations to perform this relatively complex task with a minimal amount of code:

FUNCTION whack_emps_by_dept (deptlist dlist_t)
   RETURN enolist_t
IS
   enolist enolist_t;
BEGIN
   FORALL aDept IN deptlist.FIRST..deptlist.LAST
      DELETE emp WHERE deptno IN deptlist(aDept)
         RETURNING empno BULK COLLECT INTO enolist;
   RETURN enolist;
END;

29-37.

You can use packaged variables (and an anonymous PL/SQL block) to eliminate the calls to the DBMS_SQL.BIND_VARIABLES procedure:

CREATE OR REPLACE PACKAGE myvars
IS
   empno emp.empno%TYPE;
   deptno emp.deptno%TYPE;
   ename emp.ename%TYPE;
END;
/

CREATE OR REPLACE PROCEDURE bindnone
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
   rows_inserted INTEGER;
BEGIN
   DBMS_SQL.PARSE (
      cur,
      'BEGIN
          INSERT INTO emp (empno, deptno, ename)
            VALUES (myvars.empno, myvars.deptno, myvars.ename);
       END;',
      DBMS_SQL.NATIVE
   );

   FOR rowind IN 1 .. 1000
   LOOP
      myvars.empno := rowind;
      myvars.deptno := 40;
      myvars.ename := 'Steven' || rowind;
      rows_inserted := DBMS_SQL.EXECUTE (cur);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR (cur);
END;

29-38.

Before Oracle8, VARCHAR2 variables were treated as variable length but stored as fixed length. Hence, in an Oracle 7.3 database, this script consumes 32,767 * 32,767 bytes (approximately 1 GB!) of memory, even though each element of the array is NULL.

29-39.

Pass-by-value is a problem for large collections. For example, suppose that you have a large index-by table with 50,000 elements. Passing the table as an IN OUT parameter creates another copy of the structure in memory. Additionally, any updates made to this copy must be propagated back to the original collection variables.

There are two ways to mitigate the problem. The first is to change the parameters into packaged variables. The second method, available only in Oracle8i, is to use the NOCOPY keyword in the parameter declaration. NOCOPY switches the parameter “more” to pass by reference.

There are several potential pitfalls with these approaches: