PL/ SQL Optimization

 

PL/ SQL Optimizer

Oracle 10g introduced  a new PL/SQL optimization feature to optimize the PL/SQL code by rearranging codes for better performance. PLSQL optimizer do this for you. Optimizer is enabled by default for interpreted p-code and native compilation. The default value for the optimizer is set to 2 but it can be unset or modify compiler aggressiveness.

PLSQL_OPTIMIZE_LEVEL = 0     no optimization
PLSQL_OPTIMIZE_LEVEL = 1     moderate optimize may leave unused code and exception
PLSQL_OPTIMIZE_LEVEL = 2     Aggressive optimize may rearrange source code flow

ALTER SESSION SET PLSQL_OPTIMIZER_LEVEL = 1;

ALTER PROCEDURE procedure_01 COMPILE plsql_optimizer_level =1;

ALTER PROCEDURE procedure_02 COMPILE reuse settings;

Subprogram Inlining

Subprogram inlining replaces a subprogram invocation from the copy of invoked subprogram. To allow subprogram inlining invoking set PLSQL_OPTIMIZE_LEVEL  to 2 (default value) or set it to 3. Subprogram inlining can also be done by using Pragma INLINE. PRAGMA INLINE can enable or disable subprogram inlining.

PRAGMA INLINE (subprogram, YES);              # enable subprogram inlining

PRAGMA INLINE (subprogram, NO);               # disable subprogram inlining

Candidates for Tuning

• Older code that does not take advantage of new PL/SQL language features.
• Older dynamic SQL statements written with the DBMS_SQL package.

• Code that spends much time processing SQL statements.

• Functions invoked in queries, which might run millions of times.

• Code that spends much time looping through query results.

• Code that does many numeric computations.