Chapter 6. Optimizing PL/SQL Code

In this chapter, we will cover:

• Using bind variables and parsing

• Array processing and bulk-collect

• Passing values with NOCOPY (or not)

• Using short-circuit IF statements

• Avoiding recursion

• Using native compilation

• Taking advantage of the function result cache

• Inlining PL/SQL code

• Using triggers and virtual columns

Introduction

In this chapter, we will focus on PL/SQL code, such as stored procedures, functions, and triggers.

The topics explained in this chapter are easier to apply than those in Chapter 4, Optimizing SQL Code because you can make changes to PL/SQL code without having to rebuild the entire application—and they can be just as easily reversed if something doesn't work as expected.

It is relatively easy to obtain great performance improvements by tuning PL/SQL code with very little effort. As always, we have to measure the results before and after our changes, using the process introduced in Chapter 1, Starting with Performance Tuning in the recipe The performance tuning process.

Using bind variables and parsing

We have already discussed bind variables and parsing in the Using bind variables recipe in Chapter 4, Optimizing SQL Code. In this recipe, we will see another example, using the same principles applied to a PL/SQL procedure.

How to do it...

The following steps will demonstrate the bind variables using PL/SQL:

1. Connect to the database as user SH:

   CONNECT sh@TESTDB/sh
   

2. Create a function to calculate the maximum length of data stored in an arbitrary field with a variable condition on another field:

   CREATE FUNCTION CONDITIONAL_COLUMN_LEN(TABLE_NAME IN VARCHAR2,
    COLUMN_NAME IN VARCHAR2, COND_FIELD IN VARCHAR2,
    COND_VALUE IN VARCHAR2) RETURN NUMBER
   IS
     L_RESULT NUMBER := 0;
     L_STMT VARCHAR2(2000);
   BEGIN
     L_STMT := 'SELECT MAX(LENGTH(' || COLUMN_NAME ||
      ')) FROM ' || TABLE_NAME ||
      ' WHERE ' || COND_FIELD || ' = ' || COND_VALUE;
     EXECUTE IMMEDIATE L_STMT INTO L_RESULT;
     RETURN L_RESULT;
   END;
   /
   

3. Calculate using the function created in the previous step for...

Array processing and bulk-collect

In this recipe, we will see how to use the BULK COLLECT and FORALL statements to speed up the processing of huge amounts of data in a single statement.

We will also see how to limit the amount of memory used for these statements, to avoid a decrease in performance due to reduced available memory for other processes.

How to do it...

The following steps will demonstrate array processing:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a MY_CUSTOMERS table to store the ID and FIRST_NAME of the customers:

   CREATE TABLE sh.MY_CUSTOMERS (
     CUST_ID NUMBER,
     CUST_FIRST_NAME VARCHAR2(20));
   

3. Populate the MY_CUSTOMERS table using an INSERT statement inside a FOR loop:

   SET TIMING ON
   BEGIN
     FOR aRow IN (SELECT CUST_ID, CUST_FIRST_NAME FROM CUSTOMERS)
     LOOP
       INSERT INTO sh.MY_CUSTOMERS (CUST_ID, CUST_FIRST_NAME)
         VALUES (aRow.CUST_ID, aRow.CUST_FIRST_NAME);
     END LOOP;
   END;
   /
   SET TIMING OFF
   

4. Truncate the MY_CUSTOMERS table to empty it:

   TRUNCATE TABLE sh.MY_CUSTOMERS...

Passing values with NOCOPY (or not)

In programming languages, we can pass parameters by reference and by value to a function. In this recipe, we will see how to make out this difference in PL/SQL functions and procedures.

How to do it...

The following steps will demonstrate passing parameters to functions:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the type TAB_NUMBERS, which is a table of numbers:

   CREATE OR REPLACE TYPE sh.TAB_NUMBERS AS TABLE OF NUMBER;
   

3. Create a function called MY_VALUE, which returns an element of an array:

   CREATE OR REPLACE FUNCTION MY_VALUE(ATABLE IN OUT TAB_NUMBERS,
    AIND IN NUMBER) RETURN NUMBER
   IS
     L_VALUE NUMBER := 0;
   BEGIN
     L_VALUE := ATABLE(AIND);
     RETURN L_VALUE;
   END;
   /
   

4. Create the function MY_VALUE_NOCOPY, which acts as the previous one, but the array parameter is defined as NOCOPY:

   CREATE OR REPLACE FUNCTION MY_VALUE_NOCOPY(
    ATABLE IN OUT NOCOPY TAB_NUMBERS,
    AIND IN NUMBER) RETURN NUMBER
   IS
     L_VALUE NUMBER := 0;
   BEGIN
     L_VALUE := ATABLE(AIND)...

Using short-circuit IF statements

In this recipe, we will see how the order in which we evaluate a compound IF statement of more than one condition, may affect performance.

How to do it...

The following steps will demonstrate compound IF statements:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Retrieve the records in the SALES table and loop through them to count the number of sales that took place before June 28, 1998 with a quantity greater than 1:

   SET TIMING ON
   DECLARE
     TAB_QTY DBMS_SQL.NUMBER_TABLE;
     TAB_TIME DBMS_SQL.DATE_TABLE;
     CNT NUMBER := 0;
   BEGIN
     SELECT AMOUNT_SOLD, TIME_ID
       BULK COLLECT INTO TAB_QTY, TAB_TIME FROM SALES;
     FOR J IN TAB_QTY.FIRST..TAB_QTY.LAST LOOP
       IF TAB_QTY(J) > 1 AND TAB_TIME(J) < '27-JUN-98' THEN
         CNT := CNT + 1;
       END IF;
     END LOOP;
   END;
   /
   SET TIMING OFF
   

3. In the previous script, we change the order in which the two conditions are expressed in the IF statement:

   SET TIMING ON
   DECLARE
     TAB_QTY DBMS_SQL.NUMBER_TABLE;
     TAB_TIME DBMS_SQL...

Avoiding recursion

In this recipe, we will investigate the use of recursive PL/SQL functions and their impact on performance.

How to do it...

The following steps demonstrate recursive functions:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the FACTORIAL_RECURSIVE function to calculate the factorial of a given number (which is the product of all positive integers less than or equal to the given number) using the well-known recursive algorithm, as follows:

   CREATE OR REPLACE FUNCTION FACTORIAL_RECURSIVE (ANUM NUMBER) RETURN NUMBER IS
     AVALUE NUMBER;
   BEGIN
     IF ANUM <= 1 THEN
       AVALUE := 1;
     ELSE
       AVALUE := ANUM * FACTORIAL_RECURSIVE(ANUM - 1);
     END IF;
     RETURN AVALUE;
   END;
   

3. Create the function FACTORIAL_ITERATIVE to calculate the factorial of a given number using an iterative algorithm:

   CREATE OR REPLACE FUNCTION FACTORIAL_ITERATIVE (ANUM NUMBER) RETURN NUMBER IS
     AVALUE NUMBER := 1;
   BEGIN
     FOR J IN 2..ANUM LOOP
       AVALUE := AVALUE * J;
     END LOOP;
     RETURN AVALUE;
   END;...

Using native compilation

In this recipe, we will see how to instruct the database to compile our stored procedures in native form—rather than interpreted—to speed up the execution time.

Getting ready

To be sure that our database is not using native compilation by default, we can execute the following command from a SQL*Plus Session:

SHOW PARAMETER PLSQL_CODE_TYPE

If the result is NATIVE, we can execute the following statement to return to the original default value:

ALTER SYSTEM SET PLSQL_CODE_TYPE = INTERPRETED;

How to do it...

The following steps will demonstrate how to use native compilation:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the function C_N_K, which calculates the number of k-combinations in a set of n elements:

   CREATE OR REPLACE FUNCTION C_N_K (N IN NUMBER, K IN NUMBER)
     RETURN NUMBER
   IS
     N_FAT NUMBER := 1;
     K_FAT NUMBER := 1;
     N_K_FAT NUMBER := 1;
   BEGIN
     FOR J IN 1..N LOOP
       N_FAT := N_FAT * J;
     END LOOP;
     FOR J IN 1..K LOOP
       K_FAT := K_FAT * J;
     END...

Taking advantage of function result cache

In this recipe, we will see how to use the function result cache feature, available from Oracle 11g upwards, to enhance our function's performance.

How to do it...

The following steps will demonstrate the use of the functions result cache:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create the function C_N_K, which calculates the number of k-combinations in a set of n elements:

   CREATE OR REPLACE FUNCTION C_N_K (N IN NUMBER, K IN NUMBER)
     RETURN NUMBER
   IS
     N_FAT NUMBER := 1;
     K_FAT NUMBER := 1;
     N_K_FAT NUMBER := 1;
   BEGIN
     FOR J IN 1..N LOOP
       N_FAT := N_FAT * J;
     END LOOP;
     FOR J IN 1..K LOOP
       K_FAT := K_FAT * J;
     END LOOP;
     FOR J IN 1..(N - K) LOOP
       N_K_FAT := N_K_FAT * J;
     END LOOP;
     RETURN (N_FAT / (N_K_FAT * K_FAT));
   END;
   /
   

3. Create a procedure with the name STRESS to test the function in a loop:

   CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
   IS
     AVAL NUMBER;
   BEGIN
     FOR J IN 1..ANUM LOOP
       AVAL := C_N_K (50,10);
     END LOOP...

Inlining PL/SQL code

In this recipe, we will see the benefits of inlining the PL/SQL code in our functions and procedures.

How to do it...

The following steps will demonstrate how to make PL/SQL functions inline:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a SIMPLE_FUNCTION function, which returns the area of a triangle given the length of he base and the height:

   CREATE OR REPLACE FUNCTION SIMPLE_FUNCTION (N IN NUMBER,
    K IN NUMBER) RETURN NUMBER
   IS
   BEGIN
     RETURN (N * K / 2);
   END;
   /
   

3. Create a STRESS procedure, which calculates the area for a number of triangles using the SIMPLE_FUNCTION function created in step 2:

   CREATE OR REPLACE PROCEDURE STRESS(ANUM NUMBER)
   IS
     AVAL NUMBER;
     T1 NUMBER;
   BEGIN
     T1 := DBMS_UTILITY.get_time;
     FOR J IN 1..ANUM LOOP
       AVAL := SIMPLE_FUNCTION (50,ANUM);
     END LOOP;
     DBMS_OUTPUT.PUT_LINE('TIME: ' ||
       (DBMS_UTILITY.get_time - T1));
   END;
   /
   

4. Create the same procedure as in the previous step, inlining the SIMPLE_FUNCTION by adding a PRAGMA INLINE...

Using triggers and virtual columns

In this recipe, we will see how to use virtual columns, a new feature in Oracle Database 11g, to avoid the use of DML triggers, resulting in a performance gain in our applications.

Note

Virtual columns can also be used in referential integrity, tables can be partitioned by them, and statistics can be gathered on them.

How to do it...

The following steps will demonstrate the use of virtual columns:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. Create a table and call it LOANS:

   CREATE TABLE sh.LOANS (
     LOAN_ID INT NOT NULL,
     PAYMENT NUMBER,
     NUMBER_PAYMENTS NUMBER,
     GROSS_CAPITAL NUMBER);
   

3. Create a trigger on the LOANS table to calculate the GROSS_CAPITAL field, giving the number of payments and the amount of every single payment:

   CREATE OR REPLACE TRIGGER TR_LOANS_INS
     BEFORE UPDATE OR INSERT ON sh.LOANS
     FOR EACH ROW
   BEGIN
     :new.GROSS_CAPITAL := :new.PAYMENT * :new.NUMBER_PAYMENTS;
   END;
   /
   

4. Insert several rows in the LOANS table and query against it, measuring...