plsql_can_transform_sql_to_assign controls whether select into from dual statements within PL are allowed to be rewritten as assign statements.
Privilege requirements
Query variables
Both the
systenant and all user tenants can use theSHOW VARIABLESstatement and theinformation_schema.SESSION_VARIABLESview to view the values of session system variables.Modify variables
The
systenant and all user tenants can directly modify the values of session system variables for their own tenants.
Attributes
Attribute |
Description |
|---|---|
| Parameter type | Bool |
| Default Value | OFF |
| Value range | ON/OFF |
| Modifiable | Yes, you can use theSETStatement modification. |
| Whether to restart the OBServer node for the change to take effect | No, the setting takes effect immediately. |
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 will occur. When the variable is set to ON, the kernel attempts automatic rewriting, which is expected to improve execution performance if successful. Since rewriting modifies the statement composition of the PL/SQL, it causes the compiled output before and after optimization to differ. 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;
/
The func stored function call took 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;
When the func stored function is called again, it takes 1.445 seconds:
obclient> select func(level) from dual connect by level < 10000;
cost : 9999 rows in set (1.445 sec)
After enabling the optimization, performance is significantly improved.