Implicit conversion of Cursor FOR LOOP into BULK FETCH using the PLSQL_OPTIMIZE_LEVEL parameter

Prior to the Oracle version 10gR1, the PL/SQL compiler does not apply any changes to the PL/SQL unit to improve their performance while translating them to the system code. From and after the release 10gR1, Oracle has introduced the initialization parameter PLSQL_OPTIMIZE_LEVEL to instruct the PL/SQL optimizer to automatically rearrange the source code, remove redundant and unreachable code, transform the FOR loops to Bulk Collect, and inline the subroutines for better performance.

The PL/SQL optimizer performs the optimization techniques based on the level value that is set for the PLSQL_OPTIMIZE_LEVEL parameter. The possible values allowed for this parameter are 0, 1, 2 (Default), 3 (11g and higher only). The higher the value set for this parameter, the more the effort the PL/SQL optimizer makes to optimize the PL/SQL units.

The prototype of the PLSQL_OPTIMIZE_LEVEL initialization parameter is shown below,

plsql_optimize_level=<0|1|2|3>;

Where, 2 is the default.

Altering the Optimization Level

The PL/SQL optimization level can be set/altered using the PLSQL_OPTIMIZE_LEVEL initialization parameter either at the SYSTEM or SESSION or at the OBJECT level.

The prototype for setting the optimization level either at the SYSTEM or SESSION level is shown below,

ALTER <SYSTEM | SESSION> SET plsql_optimize_level=<0|1|2|3>;

The prototype for setting the optimization at the object level is shown below,

ALTER <object_type> <object_name> COMPILE plsql_optimize_level = <0|1|2|3>;

If the object is recompiled without the REUSE SETTINGS clause, it takes the database’s current optimization level as its own. With this clause included in the compilation script, the object retains the optimization level it was last compiled with, instead of the database’s current optimization level.

Consider the test procedure for the further demonstration of the PLSQL_OPTIMIZE_LEVEL parameter for the implicit conversion of  LOOPs into BULK FETCHes.

CREATE OR replace PROCEDURE Proc_test 
  authid current_user 
IS 
  l_pi_start_time PLS_INTEGER; 
  l_pi_end_time PLS_INTEGER; 
  l_pi_cpu_start_time PLS_INTEGER; 
  l_pi_cpu_end_time PLS_INTEGER; 
  l_pi_var1 PLS_INTEGER; 
  l_pi_var2 PLS_INTEGER; 
FUNCTION Func_test( ip_pi_var1 PLS_INTEGER) 
  RETURN PLS_INTEGER 
IS 
BEGIN 
  RETURN ip_pi_var1+ip_pi_var1; 
END; 
BEGIN 
  l_pi_start_time :=NULL; 
  l_pi_cpu_start_time :=NULL; 
  l_pi_start_time :=dbms_utility.get_time; 
  l_pi_cpu_start_time :=dbms_utility.get_cpu_time; 
  FOR i IN 
  ( 
         SELECT object_name 
         FROM   dba_objects ) 
  LOOP 
    NULL; 
  END LOOP i; 
  FOR j IN 
  ( 
         SELECT table_name, 
                column_name 
         FROM   dba_tab_cols ) 
  LOOP 
    l_pi_var2 :=0; 
    l_pi_var2 :=1; 
  END LOOP j; 
  l_pi_var2 :=Func_test(10); 
  IF 1 =1 THEN 
    l_pi_var2 :=100; 
  ELSE 
    l_pi_var2:=1000; 
  END IF; 
  NULL; 
  l_pi_end_time :=dbms_utility.get_time; 
  l_pi_cpu_end_time :=dbms_utility.get_cpu_time; 
  dbms_output.put_line( 'Program run time: ' 
  ||(l_pi_end_time l_pi_start_time) /100 
  ||' Seconds'); 
  dbms_output.put_line( 'CPU run time: ' 
  ||(l_pi_cpu_end_time l_pi_cpu_start_time)/100 
  ||' Seconds'); 
END;
/

After the procedure is created, its compilation mode can be verified by querying the USER_PLSQL_OBJECT_SETTINGS data dictionary table as shown below,

SELECT name, 
       TYPE, 
       plsql_optimize_level 
FROM   user_plsql_object_settings 
WHERE  name = 'PROC_TEST';

NAME               TYPE                    PLSQL_OPTIMIZE_LEVEL
----------------   ------------------  ------------------------------------
PROC_TEST    PROCEDURE     2

Note: The default optimization level for the procedure will be the database’s current optimization level.

The PLSQL_OPTIMIZE_LEVEL=2 setting performs the below implicit actions on the package.

1. Redundant code removal
1. The null assignment to the variables, L_PI_START_TIME and L_PI_CPU_START_TIME in the line numbers 16 and 17 respectively, are already null during their creation in the declaration section, thus making these two statements redundant.
2. The statements L_PI_VAR2=0 and L_PI_VAR2=1 are redundant as it assigns a literal integer for every iteration inside the loop. Also, the assignment of the first statement is overridden by the second statement. The PL/SQL compiler finds these properties, takes the first statement (L_PI_VAR2=0) out of the loop structure and skips the second statement (L_PI_VAR2=1) during the program execution.
2. Unreachable code removal
1. The statement  l_pi_var2:=1000; is unreachable in the code and this will be avoided.
3. Implicit conversion of Cursor FOR LOOP into BULK ARRAY FETCH.
1. This setting along with NCOMP mode (PLSQL_CODE_TYPE=NATIVE) converts all the FOR loops into ARRAY fetches with a limit of 100. let us compare the result of the trace file output for the above package with PLSQL_OPTIMIZE_LEVEL>=2 and PLSQL_OPTIMIZE_LEVEL<=1 settings respectively.

The above trace file output is extracted when the procedure code was executed with the PLSQL_OPTIMIZE_LEVEL parameter value set to <=1. The total number of rows processed is 2,24,208 and the total fetch operations performed is 2,24,214. This indicates that approximately 1 row is fetched for each fetch operation, resulting in CPU overhead and context switching.

The above trace file output is extracted when the procedure code was executed with the PLSQL_OPTIMIZE_LEVEL parameter value set to >=2 in the NCOMP mode (PLSQL_CODE_TYPE=NATIVE). The total number of rows processed is 2,24,208 and the total fetches performed is 2,247. This shows that the recursive statements are implicitly converted into ARRAY fetches with a limit of 100.

This approach automatically reduces the context switching and increases the performance of the package.


Thank you,
Boobal Ganesan
Author of Advanced PL/SQL Programming - The Definitive Reference