As it is known, invoking PL/SQL code from SQL cause context switching. SQL is a non-procedural language and it is very different from PL/SQL language. In addition, PL/SQL engine and SQL engine are seperate and distinct two things. Thus, when PL/SQL functions are used within SQL, context switching occurs. In order to reduce this overhead, a new feature was introduced in Oracle 12.1, pragma UDF (UDF stands for User Defined Functions). If a function is created with pragma UDF, it is compiled in a different manner. Besides, another new enhancement came with Oracle 12c. PL/SQL functions can be defined inside the WITH clause. It is also compiled in the same manner as pragma UDF functions. However, it is not stored in the data dictionary. Hence, cannot be called from another SQL or PL/SQL statement.
In this post, I will show different ways to call PL/SQL functions from SQL.
CREATE TABLE T_TEST
AS
SELECT
ROUND(DBMS_RANDOM.VALUE(1, 100)) C1,
DBMS_RANDOM.STRING('A', DBMS_RANDOM.VALUE(5, 10)) C2
FROM DUAL
CONNECT BY LEVEL <= 500000;
CREATE OR REPLACE FUNCTION F_CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
CREATE OR REPLACE FUNCTION F_CALC_UDF(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
PRAGMA UDF;
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
I compared four different methods in this demo. First one is native SQL. Second with traditional PL/SQL function. Third with pragma UDF function and last one is WITH clause. The demo script runs the test case five times to get an approximate result. By the way, I used dynamic SQL instead of static for WITH clause. Because, PL/SQL language does not support the new syntax in Oracle 19c. For this reason, I also used dynamic SQL for the other examples in order to evaluate equitably.
In addition, if you use PL/SQL declared WITH clause, it should be the top level query or the top level query must include WITH_PLSQL hint. Otherwise, the statement will fail to compile as shown below.
SELECT * FROM (
WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T
);
Error at line 2
ORA-32034: desteklenmeyen WITH yan tümcesi kullanımı
SELECT /*+ WITH_PLSQL */ * FROM (
WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T
);
Oracle 19c has been used for the demo.
DECLARE
V_START NUMBER := DBMS_UTILITY.GET_TIME;
TYPE REC IS RECORD(
C1 NUMBER,
C2 VARCHAR2(20),
C3 NUMBER
);
TYPE ARR_TYPE IS TABLE OF REC;
ARR ARR_TYPE;
C1 SYS_REFCURSOR;
V_SQLTEXT VARCHAR2(4000);
PROCEDURE SHOW_ELAPSED_TIME(P_NAME VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(P_NAME||' elapsed time: '||TO_CHAR((DBMS_UTILITY.GET_TIME - V_START)/100));
V_START := DBMS_UTILITY.GET_TIME;
END;
BEGIN
FOR I IN 1..5
LOOP
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, ( POWER(C1, LENGTH(C2) ) / ( MOD(C1, LENGTH(C2))+1 ) ) + ( SQRT(C1) ) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Native SQL');
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, F_CALC(C1, C2) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Traditional PL/SQL');
-----------------------------------------------------------------------------------
V_SQLTEXT := 'SELECT T.*, F_CALC_UDF(C1, C2) NEW_C FROM T_TEST T';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('Pragma UDF');
-----------------------------------------------------------------------------------
V_SQLTEXT :=
q'[WITH
FUNCTION CALC(P1 NUMBER, P2 VARCHAR2)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN ( POWER(P1, LENGTH(P2) ) / ( MOD(P1, LENGTH(P2))+1 ) ) + ( SQRT(P1) );
END;
SELECT T.*, CALC(C1, C2) NEW_C FROM T_TEST T]';
OPEN C1 FOR V_SQLTEXT;
FETCH C1 BULK COLLECT INTO ARR;
CLOSE C1;
SHOW_ELAPSED_TIME('WITH clause');
DBMS_OUTPUT.PUT_LINE(' ');
-----------------------------------------------------------------------------------
END LOOP;
END;
Native SQL elapsed time: 2,47
Traditional PL/SQL elapsed time: 6,4
Pragma UDF elapsed time: 2,89
WITH clause elapsed time: 2,98
---------------------------------------------
Native SQL elapsed time: 2,4
Traditional PL/SQL elapsed time: 5,34
Pragma UDF elapsed time: 2,19
WITH clause elapsed time: 2,37
---------------------------------------------
Native SQL elapsed time: 1,99
Traditional PL/SQL elapsed time: 6,72
Pragma UDF elapsed time: 3,13
WITH clause elapsed time: 3,15
---------------------------------------------
Native SQL elapsed time: 2,75
Traditional PL/SQL elapsed time: 6,96
Pragma UDF elapsed time: 3,13
WITH clause elapsed time: 3,24
---------------------------------------------
Native SQL elapsed time: 3,21
Traditional PL/SQL elapsed time: 7,74
Pragma UDF elapsed time: 3
WITH clause elapsed time: 3,31
---------------------------------------------
Undoubtedly, native SQL is the fastest way amoung the others. For 500K rows, the average result of five times run is 2,5 seconds. However, the point of this post is not to prove that native SQL is the fastest. To show the right way when we need to use PL/SQL inside SQL. Although, according to the graph, pragma UDF and WITH clause increase and decrease in the same direction and at the same rate, the avarage execution time of the pragma UDF function is 2,86 whereas, WITH clause is 3,01 seconds. We can say that pragma UDF performs better as compared to WITH clause. If you look at the results of the traditional PL/SQL function, you will see how context switching increases the overhead. It seems obvious that Pragma UDF function is more than two times faster than standalone PL/SQL function.
Conclusion
The results show that it is beneficial to use pragma UDF function to reduce context switching overhead. However, it might become more expensive to call it from PL/SQL. When you state the pragma UDF clause in the function, you will say that the function will be exclusively used from SQL. On the other hand, WITH clause can be used as another alternative to reduce the context switching effect. If the function that you want to call from SQL is specific to a single SQL or if you cannot install your PL/SQL function or procedure to the database or maybe you need to work read only database, in such cases you can use WITH clause. Finally, as it is shown in the demo script, I used DETERMINISTIC function for both PL/SQL and WITH clause. If you use 12c, you might have noticed that deterministic functions cannot work in WITH clause as stated here by Jonathan Lewis. This behaviour was fixed in 18c onward.