Enable code optimization technique using PLSQL_OPTIMIZE_LEVEL

Attribution: This article is a complimentary excerpt from Oracle Advanced PL/SQL Developer Professional Guide

Oracle initialization parameter PLSQL_OPTIMIZE_LEVEL

The parameter was introduced in Oracle 10g to decide the level of optimization applicable for a program unit during compilation. The optimization during compilation means the removal of dead code, code movements to optimize iterative constructs, inline the calls if required, and choosing the best philosophy to optimally compile the program.

It is set by the DBA at system, session and object level. It is the object compilation parameter which is retained by the object library unit. It can be queried in USER_PLSQL_OBJECT_SETTINGS view for a given object.

Prior to 11g release, the parameter could accommodate only three valid values i.e. 0, 1, and 2. Oracle 11g introduced an additional optimization level 3. By default, the parameter value is 2. The compiler’s effort is directly proportional to the parameter value i.e. more the value, more the compiler’s effort.

It can be set using ALTER [SYSTEM | SESSION] command as shown below

/*To view the current parameter setting*/

Conn / as SYSDBA
Connected.

SQL> show parameter plsql_optimize_level

NAME TYPE VALUE
———————————— ———– —————–
plsql_optimize_level integer 2

/*Modify the setting for the current session*/
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1
/

Session altered.

The description for each value is as below

Case 1: PLSQL_OPTIMIZE_LEVEL = 0

This level represents ‘No Optimization’ mode of the compiler. The compiler only maintains the code evaluation order and executes it without any efforts on code optimization.

Let us write a small program and compare the same with other optimization levels also

Scenario: In your application, you have to give insert a delay of approx 1 second during some user operations. You create a program which runs a large loop to delay the control transfer.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 0;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:19294

PL/SQL procedure successfully completed.

Case 2: PLSQL_OPTIMIZE_LEVEL = 1

This level represents ‘Elementary and eliminative Optimization’ level by maintaining the same code order. The compiler optimizes the code by eliminating the code of irrelevant logic.

We shall re execute the previous PL/SQL block and check the difference.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:7112

PL/SQL procedure successfully completed.

Note that the execution time has reduced by 60% during this optimization level.

Case 3: PLSQL_OPTIMIZE_LEVEL = 2

This is the standard level of optimization where the compiler intelligently does the code refactoring and uses advanced optimization techniques to modify the code structure in order to achieve the best.

Re executing the previous PL/SQL block to compare the ‘Execution time’ results.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 2;

Session altered.

DECLARE
L_START_TIME NUMBER;
L_END_TIME NUMBER;
A NUMBER; B NUMBER; C NUMBER;
BEGIN
L_START_TIME := DBMS_UTILITY.GET_TIME();
FOR I IN 1..1000000000
LOOP
A := 1;
B := 1;
C := A+1;
END LOOP;
L_END_TIME := DBMS_UTILITY.GET_TIME();
DBMS_OUTPUT.PUT_LINE(‘Execution time:’||TO_CHAR(L_END_TIME – L_START_TIME));
END;
/

Execution time:0

PL/SQL procedure successfully completed.

Just observe the incredible intelligence of the PL/SQL compiler. The complete code has been re-structured. Analytically, the code inside the FOR loop is not relevant to the loop. So it has been moved outside the loop and now, the loop is completely ignored by the compiler. Therefore, the execution time comes out to be zero.

Case 4: PLSQL_OPTIMIZE_LEVEL = 3

This is the latest optimization level introduced in Oracle 11g. It is a sort of ‘Forced’ optimization level, which Oracle terms as ‘High Priority’ optimization. The code optimizer does the code refactoring and automatically inlines all program calls in the program unit.

Conn / as sysdba
Connected.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Session altered.

As mentioned earlier, the parameter is treated as the object compilation factors. Hence it is stored in the object library unit and can be queried in
[ALL | DBA | USER]_PLSQL_OBJECT_SETTINGS.

Refer Oracle 11g Documentation on the topic