plsql_can_transform_sql_to_assign

The plsql_can_transform_sql_to_assign variable specifies whether to allow rewriting select into from dual in PL/SQL to assign statements.

Privilege requirements

• Query variables

  The sys tenant and all user tenants can execute the SHOW VARIABLES statement or query the SYS.TENANT_VIRTUAL_SESSION_VARIABLE view (Oracle mode) or the information_schema.SESSION_VARIABLES view (MySQL 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 in their own tenants.

Attributes

Usage notes

The plsql_can_transform_sql_to_assign variable specifies whether to allow rewriting select into from dual in PL/SQL to assign statements. The default value is OFF, which indicates that no rewriting is performed. When the value is ON, the kernel attempts to automatically rewrite the statement. Rewriting is expected to improve execution performance. However, rewriting changes the composition of PL/SQL statements, resulting in inconsistent compilation outputs before and after optimization. Therefore, this variable affects PL/SQL cache matching.

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. The execution time is 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. The execution time is 1.445 seconds:

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

After optimization, performance significantly improves.