SQL is a wonderful language and very easy to use.

For example you can call a PL/SQL function into a SELECT or WHERE clause and the result of the PL/SQL function is returned into the SQL expression.

But when we call a PL/SQL function inside a SQL statement there is a context switch between the SQL Engine and the PL/SQL Engine impacting the SQL performance mostly for SQL statements returning a high number of rows because for each row returned by the SQL, a roundtrip is done between the SQL engine and the PL/SQL engine.

Let’s do a demo :

We create a simple PL/SQL function:

SQL> create or replace function price_with_tax(p1 in number, p2 in number)
return number as
begin
      return p1*(1+p2/100);
end;
/
Function created.

We have a table with more than 100001 rows:

SQL> select count(*) from big_table;

  COUNT(*)
----------
    100001

We enable the PL/SQL profiling via the DBMS_HPROF package which allows to monitore the number of calls between the PL/SQL engine and the SQL engine:

SQL> BEGIN
  2    DBMS_HPROF.start_profiling (
  3  	 location => 'PROFILER_DIR',
  4  	 filename => 'profiler.txt');
  5   end;
  6   /

PL/SQL procedure successfully completed.

We execute a SQL statement on the table “big_table” calling the PL/SQL function “price_with_tax”:

SQL> set autotrace on
select id,owner,object_name from big_table where price_with_tax(object_id,19.6) > 1;
. . .
ID OWNER OBJECT_NAME
---------- ------------------------------------------------------------------
99903 SYS org/w3c/dom/css/CSSStyleRule
99904 SYS org/w3c/dom/css/CSSUnknownRule
. . .
100000 SYS sun/awt/AWTIcon32_java_icon16_png
100001 SYS sun/awt/AWTIcon32_java_icon24_png

100001 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id  | Operation	  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	      |  5000 |   263K|   503	(2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_TABLE |  5000 |   263K|   503	(2)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PRICE_WITH_TAX"("OBJECT_ID",19.6)>1)

We see in the Explain Plan that the PL/SQL function is called and there are 100001 rows returned.

Now let’s check the number of roundtrips (calls) between the SQL Engine and the PL/SQL Engine for the function “price_with_tax” by using the package DBMS_HPROF.ANALYZE:

QL> Begin
  2    DBMS_HPROF.stop_profiling;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> prompt ANALYZE THE RESULTS OF PROFILING -->  "DMMS_HPROF.ANALYZE"
ANALYZE THE RESULTS OF PROFILING -->  "DMMS_HPROF.ANALYZE"
SQL> pause

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    l_runid	NUMBER;
  3  BEGIN
  4    l_runid := DBMS_HPROF.analyze (
  5  		    location	=> 'PROFILER_DIR',
  6  		    filename	=> 'profiler.txt',
  7  		    run_comment => 'Test run.');
  8
  9    DBMS_OUTPUT.put_line('l_runid=' || l_runid);
 10  END;
 11  /
l_runid=36

PL/SQL procedure successfully completed.

SQL> prompt GET RUNID of DBMS_HPROF.ANALYZE
GET RUNID of DBMS_HPROF.ANALYZE
SQL> accept runid number prompt 'Enter the RUNID of DBMS_HPROF.ANALYZE:'
Enter the RUNID of DBMS_HPROF.ANALYZE:36
SQL> SET LINESIZE 500 PAGESIZE 1000
SQL> COLUMN name FORMAT A100
SQL> SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
    	    a.subtree_elapsed_time,
    	    a.function_elapsed_time,
    	    a.calls
    FROM   (SELECT fi.symbolid,
    		    pci.parentsymid,
    		    RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
    		    NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
    		    NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
   		    NVL(pci.calls, fi.calls) AS calls
   	     FROM   dbmshp_function_info fi
   		    LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
   	     WHERE    fi.runid = &runid) a
   CONNECT BY a.parentsymid = PRIOR a.symbolid
   START WITH a.parentsymid IS NULL;
old  13:	 WHERE	  fi.runid = &runid) a
new  13:	 WHERE	  fi.runid =	     36) a

NAME                  SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
-----------------------------------------------------------------------------
..__plsql_vm 	      352444		   46117                      100006
..__anonymous_block   253320		   1589                       5
. . .
SYS.DBMS_XPLAN.__pkg_init 1		  1 	                      1
LFE.PRICE_WITH_TAX	 53007		  53007                       100001

There are 100001 roundtrips between the SQL Engine and the PL/SQL Engine which of course is very bad from a performance point of view.

The solution is to rewrite the SQL expression by moving the PL/SQL code into the SQL statement but it has a cost in term of development mostly if you have huge sql statement which contains several lines of code.

WIth the new parameter SQL_TRANSPILER in 23c, oracle does this conversion automatically for you.

We just have to enable the parameter:

SQL> alter system set sql_transpiler=ON scope = both;

System altered.

We execute the same sql statement:

select id,owner,object_name from big_table where price_with_tax(object_id,19.6) > 1;

ID OWNER OBJECT_NAME
---------- ------------------------------------------------------------------   
100000 SYS																    sun/awt/AWTIcon32_java_icon16_png
    100001 SYS																    sun/awt/AWTIcon32_java_icon24_png

100001 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3993303771

-------------------------------------------------------------------------------
| Id  | Operation	  | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	      |  5000 |   263K|   494	(1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| BIG_TABLE |  5000 |   263K|   494	(1)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"*1.196>1)

We can see in the Explain Plan that the PL/SQL function is no more called and oracle has moved automatically the PL/SQL code inside the SQL Expression.

Now let’s check the number of roundtrips between the SQL Engine and the PL/SQL Engine:

SQL> SET LINESIZE 500 PAGESIZE 1000
SQL> COLUMN name FORMAT A100
SQL> SELECT RPAD(' ', (level-1)*2, ' ') || a.name AS name,
    	    a.subtree_elapsed_time,
    	    a.function_elapsed_time,
    	    a.calls
    FROM   (SELECT fi.symbolid,
    		    pci.parentsymid,
    		    RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function,fi.module), '.') AS name,
    		    NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time,
    		    NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time,
   		    NVL(pci.calls, fi.calls) AS calls
   	     FROM   dbmshp_function_info fi
   		    LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
   	     WHERE    fi.runid = &runid) a
   CONNECT BY a.parentsymid = PRIOR a.symbolid
   START WITH a.parentsymid IS NULL
old  13:	 WHERE	  fi.runid = &runid) a
new  13:	 WHERE	  fi.runid =	     37) a

NAME                   SUBTREE_ELAPSED_TIME FUNCTION_ELAPSED_TIME      CALLS
-----------------------------------------------------------------------------
..__plsql_vm	       154522		      7 	 5
SYS.XMLTYPE.GETCLOBVAL 25		     25 	 1

43 rows selected.

No more roundtrip occurs between the SQL Engine and the PL/SQL Engine which is very good from a performance point of view.

Post Views: 1,309
Thumbnail [60x60]
by
Oracle Team