Skip to content

Automatic PL/SQL to SQL Transpiler

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:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
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.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Oracle Team

Oracle Team

Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.View Author posts

1,009 posts since December 2010

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

1 thought on “Automatic PL/SQL to SQL Transpiler”

Leave a Comment

Your email address will not be published. Required fields are marked *