Reducing Context Switching in Functions and Procedures

When we mix SQL and PL/SQL together, we get context switches. That is, the control is switched from the SQL engine to the PL/SQL engine and vice versa. This impacts the database by increasing the number of CPU processes and the time of execution of the program code. Oracle 12c has introduced two new features to reduce the impact of context switching and maximizes the code inlining to increase the performance of the PL/SQL. They are,

1) Functional and Procedural WITH clauses, and
2) Pragma UDF compiler directive.

The Functional and Procedural WITH clauses allows us to use the PL/SQL functions and procedures in SQL. Of course, we cannot call the procedure in SQL using the WITH clause, but we can call the procedure inside the function and call that function in SQL. These objects cannot be created, but only selected similarly to an SQL query.

The Pragma UDF (User Defined Functions) directs the compiler that the function has to be prepared for execution in a SELECT statement to avoid context switching. Calling a function that is defined as UDF from PL/SQL might not provide any performance gain.

For demonstration, two functions with and without the PRAGMA UDF clause in its declaration section respectively, are created for calculating the tax incurred on the employee’s corresponding salary.
 

CREATE OR replace FUNCTION Func_without_pragma( ip_n_sal NUMBER) 
  RETURN PLS_INTEGER 
IS 
BEGIN 
  RETURN ip_n_sal*0.3; 
END;

/

CREATE OR replace FUNCTION Func_with_pragma( ip_n_sal NUMBER) 
  RETURN PLS_INTEGER 
IS 
  PRAGMA udf; 
BEGIN 
  RETURN ip_n_sal*0.3; 

END;

/

In the below anonymous block, the three functions (Functional WITH clause, Function with and without the PRAGMA UDF clause), calculating the tax incurred in a similar fashion are bulk fetched into a nested table as shown. The timings of these operations are calculated and found that,
The Function with UDF Pragma is better than the Functional WITH clause is better than the Function without UDF pragma.

DECLARE 
    TYPE type_rec IS RECORD ( 
      empno NUMBER, 
      ename VARCHAR2(10), 
      sal NUMBER, 
      tax_incurred NUMBER); 
    TYPE type_ntt 
      IS TABLE OF TYPE_REC; 
    l_ntt_var1         TYPE_NTT; 
    l_n_start_time     NUMBER; 
    l_n_start_cpu_time NUMBER; 
    l_n_end_time       NUMBER; 
    l_n_end_cpu_time   NUMBER; 
    l_n_var1           NUMBER; 
    l_rf_var1          SYS_REFCURSOR; 
BEGIN 
    l_n_start_time := dbms_utility.get_time; 

    l_n_start_cpu_time := dbms_utility.get_cpu_time; 

    OPEN l_rf_var1 FOR 
'SELECT empno,   ename,   sal sal_per_month,   func_without_pragma(sal) sal_per_annum FROM emp'; 

FETCH l_rf_var1 bulk collect INTO l_ntt_var1; 

CLOSE l_rf_var1; 

l_n_end_time := dbms_utility.get_time; 

l_n_end_cpu_time := dbms_utility.get_cpu_time; 

dbms_output.Put_line('Function Without Pragma UDF: Run time=> ' 
                     ||( l_n_end_time-l_n_start_time ) / 100 
                     ||' Seconds, CPU time=> ' 
                     ||( l_n_end_cpu_time-l_n_start_cpu_time ) / 100 
                     ||' Seconds.'); 

l_n_start_time := dbms_utility.get_time; 

l_n_start_cpu_time := dbms_utility.get_cpu_time; 

OPEN l_rf_var1 FOR 
'WITH FUNCTION func_with_clause(     ip_n_sal NUMBER)   RETURN pls_integer IS BEGIN   RETURN ip_n_sal*12; END; SELECT empno,   ename,   sal sal_per_month,   func_with_clause(sal) sal_per_annum FROM emp'
; 

FETCH l_rf_var1 bulk collect INTO l_ntt_var1; 

CLOSE l_rf_var1; 

l_n_end_time := dbms_utility.get_time; 

l_n_end_cpu_time := dbms_utility.get_cpu_time; 

dbms_output.Put_line('Functional WITH clause: Run time=> ' 
                     ||( l_n_end_time-l_n_start_time ) / 100 
                     ||' Seconds, CPU time=> ' 
                     ||( l_n_end_cpu_time-l_n_start_cpu_time ) / 100 
                     ||' Seconds.'); 

l_n_start_time := dbms_utility.get_time; 

l_n_start_cpu_time := dbms_utility.get_cpu_time; 

OPEN l_rf_var1 FOR 
'SELECT empno,   ename,   sal sal_per_month,   func_with_pragma(sal) sal_per_annum FROM emp' 
; 

FETCH l_rf_var1 bulk collect INTO l_ntt_var1; 

CLOSE l_rf_var1; 

l_n_end_time := dbms_utility.get_time; 

l_n_end_cpu_time := dbms_utility.get_cpu_time; 

dbms_output.Put_line('Function With Pragma UDF: Run time=> ' 
                     ||( l_n_end_time-l_n_start_time ) / 100 
                     ||' Seconds, CPU time=> ' 
                     ||( l_n_end_cpu_time-l_n_start_cpu_time ) / 100 
                     ||' Seconds.'); 
END; 

/

Result:

Function Without Pragma UDF: Run time=> 2.25 Seconds, CPU time=> 2.21 Seconds.

Functional WITH clause: Run time=> 1.13 Seconds, CPU time=> 1.15 Seconds.

Function With Pragma UDF: Run time=> .68 Seconds, CPU time=> .67 Seconds.

Thank you,

Boobal Ganesan