How PL/SQL Optimizes Your Programs

Today, I learned a new 10g Release 2 and above 'trick' that I wasn't aware of. This is pretty cool

You can choose the level of optimization through the  plsql_optimization_level setting:

2  Most aggressive, maximum possible code transformations, biggest impact on compile time. [default]
1  Smaller scale change, less impact on compile times
0  Pre-10g compilation without optimization

scott@10GR2> show parameter plsql_optim;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
plsql_optimize_level                 integer     2

scott@10GR2> alter session set plsql_optimize_level=0;

Session altered.

scott@10GR2> create or replace procedure p
  2  as
  3     l_start_time number ;
  4     a positiven := 1;
  5     b positiven := 1;
  6     c positiven := 1;
  7     d positiven := 1;
  8  begin
  9     l_start_time := dbms_utility.get_cpu_time;
 10     for i in 1..1000000
 11     loop
 12             a := 42;
 13             b := a*2;
 14             c := a * 5;
 15             d := a+b+c;
 16     end loop;
 17     dbms_output.put_line ( dbms_utility.get_cpu_time - l_start_time);
 18  end p;
 19  /

Procedure created.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
39
41
39
39
41

PL/SQL procedure successfully completed.

scott@10GR2> alter session set plsql_optimize_level=1;

Session altered.

scott@10GR2> alter procedure p compile;

Procedure altered.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
9
8
9
8
8

PL/SQL procedure successfully completed.

scott@10GR2> alter session set plsql_optimize_level=2;

Session altered.

scott@10GR2> alter procedure p compile;

Procedure altered.

scott@10GR2> begin
  2     for i in 1..5
  3     loop
  4             p;
  5     end loop;
  6  end;
  7  /
10
7
10
9
8

PL/SQL procedure successfully completed.

scott@10GR2> SELECT name, type, plsql_optimize_level
  2  FROM user_plsql_object_settings
  3  WHERE name = 'P'
  4  /

NAME                           TYPE         PLSQL_OPTIMIZE_LEVEL
------------------------------ ------------ --------------------
P                              PROCEDURE                       2