plsql_can_transform_sql_to_assign

plsql_can_transform_sql_to_assign controls whether select into from dual statements within PL can be rewritten as assign statements.

Privilege requirements

• Query variables

  Both the sys tenant and all user tenants can use the SHOW VARIABLES statement, or the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (in Oracle-compatible mode) and the information_schema.SESSION_VARIABLES view (in MySQL-compatible mode), to view the values of session system variables.

• Modify variables

  The sys tenant and all user tenants can directly modify the values of session system variables for their own tenants.

Attributes

Usage instructions

This variable controls whether SELECT INTO FROM Dual statements within PL/SQL can be rewritten as ASSIGN statements. The default value is OFF, which means no rewriting is performed. When the variable is set to ON, the kernel attempts automatic rewriting, which is expected to improve execution performance if successful. Since the rewrite modifies the statement composition of the PL/SQL, it causes the compiled output before and after optimization to be inconsistent. Therefore, this variable affects PL cache matching.

Usage examples

Run the following command to disable the plsql_can_transform_sql_to_assign variable:

obclient> SET plsql_can_transform_sql_to_assign = OFF;

Run the following command to create a stored function named func:

obclient> create or replace function func(a int)  return int is
        x int;
        begin
        select a into x from dual;
        return x;
        end;
        /

Call the func stored function, which takes 9.147 seconds:

obclient> SELECT func(level) FROM dual connect by level < 10000;
cost : 9999 rows in set (9.147 sec)

Run the following command to enable the plsql_can_transform_sql_to_assign variable:

obclient> SET plsql_can_transform_sql_to_assign = ON;

Call the func stored function again, which takes 1.445 seconds:

obclient> select func(level) from dual connect by level < 10000;
cost : 9999 rows in set (1.445 sec)

Performance improves significantly after enabling optimization.