|
 |
|
plsql_optimize_level parameter
Oracle Tips by Burleson Consulting |
This test indicates that the default setting for
plsql_optimize_level in Oracle 10g (default
plsql_optimize_level=2), can make a 10x speed difference for
“I run the script
for 9.2.0.7 it takes 3 seconds, does ~30000 LIOs/sec/CPU via 40300
LIOs.
I run the script
for 10.2.0.1 it takes .25 seconds”
This result may be due to the automatic bulking
of fetches in PL/SQL, as noted by the plsql_optimize_level
parameter.
Steve Feuerstein notes the values for plsql_optimize_level
below.
plsql_optimize_level:
·
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
ALTER SESSION SET
PLSQL_OPTIMIZE_LEVEL = 0;
Setting plsql_optimize_level=2 (the
default) will often replace cursor for loops (CFL) to the faster
bulk collect. Feuerstein offers the following advice on this PL/SQL
transformation with plsql_optimize_level:
“If you want to do
complex processing on each row as it is queried – and possibly halt
further fetching.
You are retrieving
many rows and cannot afford to use up the memory (large numbers of
users).
Otherwise, moving
to BULK COLLECT is a smart move”
|
