performance of functions -12C

Oracle Database 12c includes two new features to enhance the performance of functions when called from SELECT statements. With Oracle 12c, a PL/SQL subprogram can be created inline with the SELECT query in the WITH clause declaration. The function created in the WITHclause subquery is not stored in the database schema and is available for use only in the current query. Since a procedure created in the WITH clause cannot be called from theSELECT query, it can be called in the function created in the declaration section. The feature can be very handy in read-only databases where the developers were not able to create PL/SQL wrappers.

Oracle Database 12c adds the new PRAGMA UDF to create a standalone function with the same objective.

Earlier, the SELECT queries could invoke a PL/SQL function, provided the function didn't change the database purity state. The query performance used to degrade because of the context switch from SQL to the PL/SQL engine (and vice versa) and the different memory representations of data type representation in the processing engines.

In the following example, the function fun_with_plsql calculates the annual compensation of an employee's monthly salary:

/*Create a function in WITH clause declaration*/

WITH FUNCTION fun_with_plsql (p_sal NUMBER) RETURN NUMBER IS

BEGIN

   RETURN (p_sal * 12);

END;

SELECT ename, deptno, fun_with_plsql (sal) "annual_sal"

FROM emp

/

ENAME       DEPTNO   annual_sal

---------- --------- ----------

SMITH       20       9600

ALLEN       30       19200

WARD      30       15000

JONES       20       35700

MARTIN     30       15000

BLAKE       30       34200

CLARK       10       29400

SCOTT       20       36000

KING       10       60000

TURNER     30       18000

ADAMS       20       13200

JAMES       30       11400

FORD       20       36000

MILLER     10       15600

14 rows selected.

If the query containing the WITH clause declaration is not a top-level statement, then the top level statement must use the WITH_PLSQL hint. The hint will be used if INSERT, UPDATE, or DELETE statements are trying to use a SELECT with a WITHclause definition. Failure to include the hint results in an exception ORA-32034: unsupported use of WITH clause.

A function can be created with the PRAGMA UDF to inform the compiler that the function is always called in a SELECT statement. Note that the standalone function created in the following code carries the same name as the one in the last example. The local WITH clause declaration takes precedence over the standalone function in the schema.

/*Create a function with PRAGMA UDF*/

CREATE OR REPLACE FUNCTION fun_with_plsql (p_sal NUMBER)

RETURN NUMBER is

PRAGMA UDF;

BEGIN

RETURN (p_sal *12);

END;

/

Since the objective of the feature is performance, let us go ahead with a case study to compare the performance when using a standalone function, a PRAGMA UDF function, and a WITHclause declared function.

Test setup

The exercise uses a test table with 1 million rows, loaded with random data.

/*Create a table for performance test study*/

CREATE TABLE t_fun_plsql

(id number,

str varchar2(30))

/

/*Generate and load random data in the table*/

INSERT /*+APPEND*/ INTO t_fun_plsql

SELECT ROWNUM, DBMS_RANDOM.STRING('X', 20)

FROM dual

CONNECT BY LEVEL <= 1000000

/

COMMIT

/

Case 1: Create a PL/SQL standalone function as it used to be until Oracle Database 12c. The function counts the numbers in the str column of the table.

/*Create a standalone function without Oracle 12c enhancements*/

CREATE OR REPLACE FUNCTION f_count_num (p_str VARCHAR2)

RETURN PLS_INTEGER IS

BEGIN

   RETURN (REGEXP_COUNT(p_str,'\d'));

END;

/

The PL/SQL block measures the elapsed and CPU time when working with a pre-Oracle 12c standalone function. These numbers will serve as the baseline for our case study.

  

/*Set server output on to display messages*/

SET SERVEROUTPUT ON

/*Anonymous block to measure performance of a standalone function*/

DECLARE

l_el_time PLS_INTEGER;

l_cpu_time PLS_INTEGER;

CURSOR C1 IS

   SELECT f_count_num (str) FROM t_fun_plsql;

TYPE t_tab_rec IS TABLE OF PLS_INTEGER;

l_tab t_tab_rec;

BEGIN

l_el_time := DBMS_UTILITY.GET_TIME ();

l_cpu_time := DBMS_UTILITY.GET_CPU_TIME ();

OPEN c1;

FETCH c1 BULK COLLECT INTO l_tab;

CLOSE c1;

DBMS_OUTPUT.PUT_LINE ('Case 1: Performance of a standalone function');

DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));

DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));

END;

/

Performance of a standalone function:

Total elapsed time:1559

Total CPU time:1366

PL/SQL procedure successfully completed.

 Case 2: Create a PL/SQL function using PRAGMA UDF to count the numbers in the str column.

/*Create the function with PRAGMA UDF*/

CREATE OR REPLACE FUNCTION f_count_num_pragma (p_str VARCHAR2)

RETURN PLS_INTEGER IS

   PRAGMA UDF;

BEGIN

   RETURN (REGEXP_COUNT(p_str,'\d'));

END;

  / 

Let us now check the performance of the PRAGMA UDF function using the following PL/SQL block.

/*Anonymous block to measure performance of a PRAGMA UDF function*/

DECLARE

l_el_time PLS_INTEGER;

l_cpu_time PLS_INTEGER;

CURSOR C1 IS

   SELECT f_count_num_pragma (str) FROM t_fun_plsql;

TYPE t_tab_rec IS TABLE OF PLS_INTEGER;

l_tab t_tab_rec;

BEGIN

l_el_time := DBMS_UTILITY.GET_TIME ();

l_cpu_time := DBMS_UTILITY.GET_CPU_TIME ();

OPEN c1;

FETCH c1 BULK COLLECT INTO l_tab;

CLOSE c1;

DBMS_OUTPUT.PUT_LINE ('Case 2: Performance of a PRAGMA UDF function');

DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));

DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));

END;

/

Performance of a PRAGMA UDF function:

Total elapsed time:664

Total CPU time:582

PL/SQL procedure successfully completed.

· Case 3: The following PL/SQL block dynamically executes the function in the WITH clause subquery. Note that, unlike other SELECT statements, a SELECT query with a WITH clause declaration cannot be executed statically in the body of a PL/SQL block.

/*Set server output on to display messages*/

SET SERVEROUTPUT ON

/*Anonymous block to measure performance of inline function*/

DECLARE

l_el_time PLS_INTEGER;

l_cpu_time PLS_INTEGER;

l_sql VARCHAR2(32767);

c1 sys_refcursor;

TYPE t_tab_rec IS TABLE OF PLS_INTEGER;

l_tab t_tab_rec;

BEGIN

l_el_time := DBMS_UTILITY.get_time;

l_cpu_time := DBMS_UTILITY.get_cpu_time;

l_sql := 'WITH FUNCTION f_count_num_with (p_str VARCHAR2)

           RETURN NUMBER IS

           BEGIN

               RETURN (REGEXP_COUNT(p_str,'''||'\'||'d'||'''));

           END;

           SELECT f_count_num_with(str) FROM t_fun_plsql';

OPEN c1 FOR l_sql;

FETCH c1 bulk collect INTO l_tab;

CLOSE c1;

DBMS_OUTPUT.PUT_LINE ('Case 3: Performance of an inline function');

DBMS_OUTPUT.PUT_LINE ('Total elapsed time:'||to_char(DBMS_UTILITY.GET_TIME () - l_el_time));

DBMS_OUTPUT.PUT_LINE ('Total CPU time:'||to_char(DBMS_UTILITY.GET_CPU_TIME () - l_cpu_time));

END;

/

Performance of an inline function:

Total elapsed time:830

Total CPU time:718

PL/SQL procedure successfully completed.

  Chandrasekar S