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 enables the database to utilize the underlying execution engine more efficiently when processing complex queries, thereby improving query execution performance.
Enable SQL Transpiler feature
To enable the SQL Transpiler feature, you need to set a variable named SQL_TRANSPILER. This variable can be set at the global or session level, allowing for flexible control over whether SQL Transpiler is enabled. For more information about this variable, see sql_transpiler.
Conversion mechanism
SQL Transpiler primarily targets PL/SQL functions that are frequently called in SQL queries. By analyzing the internal logic of these functions, the system determines whether they can be directly converted into SQL operations. For example, if a function contains only simple arithmetic operations or string processing, SQL Transpiler converts it into the corresponding SQL expression. This not only reduces performance overhead due to context switching but also makes query plans more concise and understandable, facilitating more efficient execution plan generation by the database optimizer.
Examples
Run the following command to create a function named
add_numbers.obclient> create function add_numbers(a int, b int) returns int begin return a + b; end; /Run the following command to view the execution plan when optimization is not enabled.
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(cast(1, INT(11, 0)), cast(2, INT(11, 0)))]), filter(nil) values({add_numbers(cast(1, INT(11, 0)), cast(2, INT(11, 0)))})Query the verification result.
obclient> SELECT add_numbers(1, 2) FROM dual; +-------------------+ | add_numbers(1, 2) | +-------------------+ | 3 | +-------------------+Enable SQL Transpiler optimization.
obclient> SET sql_transpiler = 'on';View the execution plan after optimization is enabled.
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([column_conv(INT,PS:(11,0),NULL,cast(:0, INT(-1, 0))) + column_conv(INT,PS:(11,0),NULL,cast(:1, INT(-1, 0)))]), filter(nil) values({column_conv(INT,PS:(11,0),NULL,cast(:0, INT(-1, 0))) + column_conv(INT,PS:(11,0),NULL,cast(:1, INT(-1, 0)))})The output for the optimized execution plan is changed to
output([column_conv(INT...) + column_conv(INT...)]). The optimizer's output clearly shows that the originaladd_numbers(1, 2)function call has been replaced with a direct addition expression:0 + :1(where :0 and :1 represent parameter placeholders). This is the core manifestation of how SQL Transpiler works.Verify the optimized query result.
obclient> SELECT add_numbers(1, 2) FROM dual; +------------------+ | ADD_NUMBERS(1,2) | +------------------+ | 3 | +------------------+