Oracle professional Tim Onions has noted a vast difference in
logical I/O speed in Oracle 10g, when compared to Oracle 9i. He
notes a test on the same server with 10g running the test more
than 10 times faster:
http://www.freelists.org/archives/oracle-l/01-2006/msg00239.html
“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, does only
25000 LIOs/sec/CPU but (and here is the confusion) only uses
1500 LIOs!. So the LIO/sec is slower but the overall result is
much quicker due to the vastly reduced number of LIOs needed.”
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!”
