plsql_optimize_level

Description

The plsql_optimize_level variable controls the optimization level for PL code compilation.

Privilege requirements

• Query variables

  • Global level

    Both the sys tenant and all user tenants can use the SHOW VARIABLES statement or the view SYS.TENANT_VIRTUAL_GLOBAL_VARIABLE (Oracle-compatible mode) and the view information_schema.GLOBAL_VARIABLES (MySQL-compatible mode) to view the values of global system variables.

  • Session level

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

• Modify variables

  • Global level

    • The sys tenant can directly modify the values of global system variables.
    • MySQL-compatible tenants need to have the SUPER or ALTER SYSTEM privilege to modify the values of global system variables.
    • Oracle-compatible tenants need to have the ALTER SYSTEM privilege to modify the values of global system variables.

  • Session level

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

Attributes

Considerations

Higher optimization levels mean more optimizations are performed by the compiler. We recommend that you set the plsql_optimize_level variable to 2. If you want more information, see Examples.

Examples

• MySQL-compatible mode

  • Set the PL optimization level to 1 at the global level to disable frontend optimization and enable backend optimization.

    obclient> SET GLOBAL plsql_optimize_level = 1;
    

  • Set the PL optimization level to 2 at the global level to enable frontend and backend optimization.

    obclient> SET GLOBAL plsql_optimize_level = 2;
    

• Oracle-compatible mode

  • Set the PL optimization level to 1 at the global level to disable frontend optimization and enable backend optimization.

    obclient> ALTER SYSTEM SET plsql_optimize_level = 1;
    

  • Set the PL optimization level to 2 at the global level to enable frontend and backend optimization.

    obclient> ALTER SYSTEM SET plsql_optimize_level = 2;