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
