SQL Transpiler

The SQL Transpiler feature aims to reduce the overhead of function calls in SQL queries by automatically converting PL/SQL functions into SQL expressions. This allows the database to more efficiently utilize its underlying execution engine when processing complex queries, thereby improving query execution efficiency.

Enable SQL Transpiler feature

To enable the SQL Transpiler feature, set the SQL_TRANSPILER variable. This variable can be set at the global or session level, allowing for flexible control over the feature's activation. For more information about this variable, see SQL_TRANSPILER

Conversion mechanism

The SQL Transpiler focuses on PL/SQL functions frequently called in SQL queries. By analyzing the function logic, the system determines if it can be directly converted into SQL operations. For example, if the function contains simple arithmetic or string manipulation, the SQL Transpiler converts it into the corresponding SQL expression. This reduces performance overhead from context switching and simplifies the query plan, making it easier for the database optimizer to generate more efficient execution plans.

Examples

1. Execute the following command to create a function named add_numbers.

   obclient> create or replace function add_numbers(a in number, b in number)
   return number
   is
   begin
   return a + b;
   end;
   /
   

2. Execute the following command to view the execution plan without optimization.

   obclient> EXPLAIN SELECT add_numbers(1, 2) FROM dual;
   Query Plan
   ==========================================
   |ID|OPERATOR  |NAME|EST.ROWS|EST.TIME(us)|
   ------------------------------------------
   |0 |EXPRESSION|    |1       |1           |
   ==========================================
   Outputs & filters:
   -------------------------------------
   0 - output([ADD_NUMBERS(1, 2)]), filter(nil)
       values({ADD_NUMBERS(1, 2)})
   

3. Query to verify the results.

   obclient> SELECT add_numbers(1, 2) FROM dual;
   +------------------+
   | ADD_NUMBERS(1,2) |
   +------------------+
   |                3 |
   +------------------+
   

4. Enable SQL Transpiler optimization.

   obclient> ALTER session SET sql_transpiler = 'on';
   

5. View the execution plan after optimization.

   obclient> EXPLAIN SELECT add_numbers(1, 2) FROM dual;
   Query Plan
   ==========================================
   |ID|OPERATOR  |NAME|EST.ROWS|EST.TIME(us)|
   ------------------------------------------
   |0 |EXPRESSION|    |1       |1           |
   ==========================================
   Outputs & filters:
   -------------------------------------
   0 - output([:0 + :1]), filter(nil)
       values({:0 + :1})
   

   After optimization, the execution plan output changes to output([:0 + :1]), which is a significant change. The database's SQL Transpiler has successfully identified the logic (a + b) inside the add_numbers function and directly converted it into the equivalent SQL addition expression :0 + :1 (where :0 and :1 are binding parameter placeholders corresponding to the input values 1 and 2). This means that during actual execution, the database will no longer call the PL/SQL function but will instead handle it like a simple 1+2 expression, significantly reducing execution overhead.

6. Verify the query results after optimization.

   obclient> SELECT add_numbers(1, 2) FROM dual;
   +------------------+
   | ADD_NUMBERS(1,2) |
   +------------------+
   |                3 |
   +------------------+