This parameter controls how much optimization Oracle does to PL/SQL code when it is compiled.
In Oracle 10, valid values were 0, 1 and 2:
Oracle 10: sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Apr 3 17:41:02 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set plsql_optimize_level = 3
2 /
ERROR:
ORA-00068: invalid value 3 for parameter plsql_optimize_level, must be between
0 and 2
SQL>
In Oracle 11, they were 0, 1, 2 and 3:
Oracle 11: sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 3 17:47:49 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter session set plsql_optimize_level = 4
2 /
ERROR:
ORA-00068: invalid value 4 for parameter plsql_optimize_level, must be between
0 and 3
SQL>
You can see the effect of PLSQL_OPTIMIZE_LEVEL in the examples below, which I ran in an Oracle 11 database.
A procedure is compiled with three different values for this parameter. The code contains a loop, which is executed 10 million times. Inside the loop there is a statement, which assigns a value, which is never used. You could achieve the same effect by doing the assignment only once outside the loop. You could even omit the assignment altogether as the only information provided to the outside world by running the procedure is the amount of CPU time it has used.
As far as I understand, Oracle do not say how they will optimize your code, they simply say that they might alter and/or reorder the statements to achieve the same result in a different way.
With the parameter set to 0 i.e. no optimization, the procedure uses 8.1 seconds of CPU. Setting it to 1 reduces the time to 1.04 seconds. Finally, when it is set to 2, no CPU usage is recorded at all:
SQL> alter session set plsql_optimize_level = 0
2 /
Session altered.
SQL> create or replace procedure counter is
2 x number;
3 t1 integer;
4 t2 integer;
5 t3 number;
6 begin
7 t1 := dbms_utility.get_cpu_time();
8 for a in 1..10000000 loop
9 x := 1 + 2 - 3 * 4 / 5;
10 end loop;
11 t2 := dbms_utility.get_cpu_time();
12 t3 := (t2-t1)/100;
13 dbms_output.put_line('CPU used = '||t3||' seconds');
14 end;
15 /
Procedure created.
SQL> exec counter;
CPU used = 8.1 seconds
PL/SQL procedure successfully completed.
SQL> alter session set plsql_optimize_level = 1
2 /
Session altered.
SQL> create or replace procedure counter is
2 x number;
3 t1 integer;
4 t2 integer;
5 t3 number;
6 begin
7 t1 := dbms_utility.get_cpu_time();
8 for a in 1..10000000 loop
9 x := 1 + 2 - 3 * 4 / 5;
10 end loop;
11 t2 := dbms_utility.get_cpu_time();
12 t3 := (t2-t1)/100;
13 dbms_output.put_line('CPU used = '||t3||' seconds');
14 end;
15 /
Procedure created.
SQL> exec counter;
CPU used = 1.04 seconds
PL/SQL procedure successfully completed.
SQL> alter session set plsql_optimize_level = 2
2 /
Session altered.
SQL> create or replace procedure counter is
2 x number;
3 t1 integer;
4 t2 integer;
5 t3 number;
6 begin
7 t1 := dbms_utility.get_cpu_time();
8 for a in 1..10000000 loop
9 x := 1 + 2 - 3 * 4 / 5;
10 end loop;
11 t2 := dbms_utility.get_cpu_time();
12 t3 := (t2-t1)/100;
13 dbms_output.put_line('CPU used = '||t3||' seconds');
14 end;
15 /
Procedure created.
SQL> exec counter;
CPU used = 0 seconds
PL/SQL procedure successfully completed.
SQL>
No comments:
Post a Comment