Enhancing Efficiency in Oracle 23ai: Using the Automatic SQL Transpiler

 

What is Context Switching between PL/SQL and SQL?

The PL/SQL engine is a virtual machine that resides in memory and processes the PL/SQL m-code instructions. When the PL/SQL engine encounters an SQL statement, a context switch is made to pass the SQL statement to the Oracle server processes. The PL/SQL engine waits for the SQL statement to complete and for the results to be returned before it continues to process subsequent statements in the PL/SQL block. 

In other words, context switching in Oracle databases refers to the overhead incurred when control shifts between the SQL engine and the PL/SQL engine during the execution of mixed SQL and PL/SQL code. The two engines operate in different runtime environments, so when you run SQL statements within PL/SQL code, the database has to 'switch' between these environments. This switching can cause performance degradation.

Automatic PL/SQL to SQL Transpiler

PL/SQL functions within SQL statements are automatically converted (transpiled) into SQL expressions whenever possible.

Oracle has introduced the Automatic SQL Transpiler feature in Oracle 23. By setting this parameter to ON, you can prevent context switching between the SQL engine and the PL/SQL engine since this feature will automatically convert functions used in SQL commands to SQL expressions, reducing overhead and improving performance.

Let me illustrate this with an example.

1.      Create a function in the HR schema that applies a fixed percentage increase to the SALARY and ignores the COMMISSION_PCT if it's NULL.

CREATE OR REPLACE FUNCTION hr.calculate_total_salary (
   salary NUMBER, 
   commission_pct NUMBER
) RETURN NUMBER DETERMINISTIC IS
BEGIN
   RETURN salary * (1 + NVL(commission_pct, 0));
END calculate_total_salary;
/

Here I created this function in FREEPDB1 pluggable database

2.    Executing the following query when sql_transpiler = OFF by default.

SELECT employee_id, first_name, last_name, 
       salary, commission_pct,
       hr.calculate_total_salary(salary, commission_pct) AS total_salary
FROM hr.employees
WHERE  department_id = 80 and
hr.calculate_total_salary(salary, commission_pct) > 10000;

Checking the Execution Plan when sql_transpiler = OFF by default shows that oracle did the context switch and used PL/SQL engine. This is because the feature is disabled, forcing Oracle to follow its traditional execution path, which involves invoking the PL/SQL engine for function calls within SQL statements.

3.   Change sql_transpiler to ON and execute the above queries again.

ALTER SESSION SET sql_transpiler = ON;

When sql_transpiler is set to ON, checking the execution plan will typically show that Oracle avoided context switching and used the SQL engine directly to execute the function. This is because the Automatic SQL Transpiler feature has converted the function call to an equivalent SQL expression, eliminating the need to involve the PL/SQL engine.