plsql_optimize_level parameter Oracle Database Tips by Donald Burleson

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"