Automatic PL/SQL optimization

This entry is part 7 of 13 in the series New Pl/Sql features 10g
As of 10g Oracle introduced automatic PL/SQL optimization. Which has 3 settings:

PLSQL_OPTIMIZE_LEVEL=0 Optimization is disabled which might be in order if you find the optimization alters your programs behaviour
PLSQL_OPTIMIZE_LEVEL=1 Optimization will perform some optimizations, but will not change the order of your code
PLSQL_OPTIMIZE_LEVEL=2 Optimization will perform all possible optimizations. This is the DEFAULT setting

You might find reasons to reduce the optimization level if needed. Changing the optimization level is done via an alter statement:
alter session set PLSQL_OPTIMIZE_LEVEL = 1;

The optimization is achieved trough taking advantage of

  • changing the order of interpretation of code
  • rewriting code to a more optimal form
  • eliminate unreachable code
  • short circuit code

To view the level used for your code
select NAME,PLSQL_OPTIMIZE_LEVEL from USER_PLSQL_OBJECT_SETTINGS

NAME                           PLSQL_OPTIMIZE_LEVEL
------------------------------ ----------------------
PLSQLWEB                       2
DIV_SLIDE_INDEX                2  

To read in-depth explanation of all the methods the optimizer utilizes to achieve it’s optimization read the whitepapers on the Oracle 10g website

It doesn’t hurt to review your USER_PLSQL_OBJECT_SETTINGS for code that is compiled using PLSQL_OPTIMIZE_LEVEL <2. If you find some that are not intentional then there is a performance gain waiting for you.

Series Navigation<< Enhancements to PL/SQL Native CompilationDynamic Wrap >>

Posted in Pl/Sql Tagged with: , ,

Leave a Reply