Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
Date: Fri, 4 Sep 2009 18:04:37 -0700 (PDT)
Message-ID: <1ea99c67-8713-4ece-a0f5-85f66851b016_at_v2g2000vbb.googlegroups.com>
I am working on an Oracle performance related project, so I thought that I would compare the performance of Oracle database 11.1.0.6 (on 64 bit Linux), 11.1.0.7 (on 64 bit Windows), and 11.2.0.1 (on 64 bit Linux). I set up a test case with a table containing 100,000,000 rows having an average row length of 53 bytes. A normal B*tree index exists on a numeric column which contains numbers ranging from 0 to 10,000. A test query will be selecting 2,547,158 (2.55%) of the rows from the table. Leaving OPTIMIZER_INDEX_COST_ADJ at the default of 100 with NOWORKLOAD system statistics results in a full table in all three releases of Oracle. Setting OPTIMIZER_INDEX_COST_ADJ to 5, for instance, results in an index access path. So, the question is: should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower number to (quoting from a posting on the Internet) “immediately tune all of the SQL in your database to favor index scans over full-table scans”? I am certain that I know the answer based on extensive testing, but what are your thoughts?
What about the performance results?
* Oracle 11.1.0.7 on 64 bit Windows finished the full tablescan access
path for the query in 35.38 seconds and the index access path in 1
hour, 38 minutes and 7 seconds.
* Oracle 11.1.0.6 on 64 bit Oracle Enterprise Linux 5 required 41.98
seconds for the full tablescan access path and one hour, 14 minutes
and 40 seconds for the index access path.
* Oracle 11.2.0.1 required 13.13 seconds (a significant drop from the
41.98 seconds received on the same server with the same operating
system and disk subsystem) and 22.13 seconds for the index access path
(an unbelievable drop from the one hour, 14 minutes and 40 seconds on
the same server, operating system, and I/O subsystem).
Obviously from the above, 11.2.0.1 is significantly faster at the
index access than is 11.1.0.6 on the same platform. But wait, I
forgot something. On 11.1.0.6 on Linux I had enabled direct I/O and
asynchronous I/O by setting the FILESYSTEMIO_OPTIONS parameter to
SETALL. I did not change that parameter on 11.2.0.1, so it defaulted
to NONE. What happens when the FILESYSTEMIO_OPTIONS parameter is set
to SETALL?
* Oracle 11.2.0.1 now required 42.45 seconds for the full tablescan
while the index access path required one hour, 16 minutes and 46
seconds.
Summaries of the 10046 level 12 trace files follow (word wrapping will
be a problem):
Oracle 11.1.0.6 with direct I/O and asynchronous I/O enabled on 64 bit
Linux and OPTIMIZER_INDEX_COST_ADJ set to the default of 100:
Total for Trace File:
|PARSEs 1|CPU S 0.000000|CLOCK S 0.008696|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 8.692690|CLOCK S 35.727505|ROWs 2547158| PHY RD BLKs 813104|CON RD BLKs (Mem) 838386|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000030|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.025758 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000040 db file sequential read 0.048904 On DB Server Min Wait: 0.004940 Avg Wait: 0.016301 Max Wait: 0.034032 direct path read 24.058316 On DB Server Min Wait: 0.000001 Avg Wait: 0.003651 Max Wait: 0.121805 SQL*Net message from client 4.663131 On Client/Network Min Wait: 0.000110 Avg Wait: 0.000183 Max Wait: 0.000638 db file scattered read 0.006442 On DB Server Min Wait: 0.006442 Avg Wait: 0.006442 Max Wait: 0.006442 ----------------------------------------------------Oracle 11.1.0.6 with direct I/O and asynchronous I/O enabled on 64 bit Linux and OPTIMIZER_INDEX_COST_ADJ set to 5 to force an index access: Total for Trace File:
|PARSEs 1|CPU S 0.000000|CLOCK S 0.000514|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 53.766858|CLOCK S 4474.545172|ROWs 2547158| PHY RD BLKs 2434458|CON RD BLKs (Mem) 2573801|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000023|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.033969 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000010 db file sequential read 462.867187 On DB Server Min Wait: 0.000134 Avg Wait: 0.005942 Max Wait: 0.128406 SQL*Net message from client 4.649001 On Client/Network Min Wait: 0.000086 Avg Wait: 0.000183 Max Wait: 0.000655 db file parallel read 3988.799453 On DB Server Min Wait: 0.003374 Avg Wait: 0.052228 Max Wait: 0.184168 db file scattered read 0.869661 On DB Server Min Wait: 0.000189 Avg Wait: 0.005998 Max Wait: 0.048183 ----------------------------------------------------Oracle 11.2.0.1 with the FILESYSTEMIO_OPTIONS parameter defaulted to NONE on 64 bit Linux and OPTIMIZER_INDEX_COST_ADJ set to the default of 100:
Total for Trace File:
|PARSEs 1|CPU S 0.001000|CLOCK S 0.000688|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 7.001935|CLOCK S 7.376174|ROWs 2547158| PHY RD BLKs 813120|CON RD BLKs (Mem) 838370|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000022|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.017904 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000021 Disk file operations I/O 0.001040 On DB Server Min Wait: 0.001040 Avg Wait: 0.001040 Max Wait: 0.001040 db file sequential read 0.000047 On DB Server Min Wait: 0.000012 Avg Wait: 0.000016 Max Wait: 0.000019 direct path read 1.674106 On DB Server Min Wait: 0.000028 Avg Wait: 0.000262 Max Wait: 0.000703 SQL*Net message from client 4.359015 On Client/Network Min Wait: 0.000081 Avg Wait: 0.000171 Max Wait: 0.001026 db file scattered read 0.000023 On DB Server Min Wait: 0.000023 Avg Wait: 0.000023 Max Wait: 0.000023 ----------------------------------------------------Oracle 11.2.0.1 with the FILESYSTEMIO_OPTIONS parameter defaulted to NONE on 64 bit Linux and OPTIMIZER_INDEX_COST_ADJ set to 5: Total for Trace File:
|PARSEs 1|CPU S 0.001000|CLOCK S 0.000912|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 18.420196|CLOCK S 18.980762|ROWs 2547158| PHY RD BLKs 2498244|CON RD BLKs (Mem) 2573633|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000029|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.013756 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000033 db file sequential read 0.413121 On DB Server Min Wait: 0.000003 Avg Wait: 0.000005 Max Wait: 0.000270 SQL*Net message from client 2.358266 On Client/Network Min Wait: 0.000066 Avg Wait: 0.000093 Max Wait: 0.000787 db file parallel read 7.574612 On DB Server Min Wait: 0.000008 Avg Wait: 0.000099 Max Wait: 0.000482 db file scattered read 0.000842 On DB Server Min Wait: 0.000019 Avg Wait: 0.000034 Max Wait: 0.000042 ----------------------------------------------------Oracle 11.2.0.1 with direct I/O and asynchronous I/O enabled on 64 bit Linux and OPTIMIZER_INDEX_COST_ADJ set to the default of 100: Total for Trace File:
|PARSEs 1|CPU S 0.000000|CLOCK S 0.000673|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 7.663844|CLOCK S 35.891614|ROWs 2547158| PHY RD BLKs 813120|CON RD BLKs (Mem) 838370|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000022|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.020031 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000027 Disk file operations I/O 0.001034 On DB Server Min Wait: 0.001034 Avg Wait: 0.001034 Max Wait: 0.001034 db file sequential read 0.022515 On DB Server Min Wait: 0.006175 Avg Wait: 0.007505 Max Wait: 0.008720 direct path read 24.816811 On DB Server Min Wait: 0.000060 Avg Wait: 0.004305 Max Wait: 0.037980 SQL*Net message from client 5.124648 On Client/Network Min Wait: 0.000159 Avg Wait: 0.000201 Max Wait: 0.001608 db file scattered read 0.005516 On DB Server Min Wait: 0.005516 Avg Wait: 0.005516 Max Wait: 0.005516 ----------------------------------------------------Oracle 11.2.0.1 with direct I/O and asynchronous I/O enabled on 64 bit Linux and OPTIMIZER_INDEX_COST_ADJ set to 5 to force an index access: Total for Trace File:
|PARSEs 1|CPU S 0.001000|CLOCK S 0.000905|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs|FETCHs 25473|CPU S 58.796059|CLOCK S 4599.654386|ROWs 2547158| PHY RD BLKs 2508560|CON RD BLKs (Mem) 2573633|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 1|
|EXECs 1|CPU S 0.000000|CLOCK S 0.000029|ROWs 0| PHY RD BLKs 0|CON RD BLKs (Mem) 0|CUR RD BLKs
(Mem) 0|SHARED POOL MISs 0|
(Mem) 0|SHARED POOL MISs 0|
Wait Event Summary:
SQL*Net message to client 0.028755 On Client/Network Min Wait: 0.000001 Avg Wait: 0.000001 Max Wait: 0.000031 db file sequential read 484.066367 On DB Server Min Wait: 0.000130 Avg Wait: 0.006024 Max Wait: 0.143995 SQL*Net message from client 4.916161 On Client/Network Min Wait: 0.000105 Avg Wait: 0.000193 Max Wait: 0.001608 db file parallel read 4091.261439 On DB Server Min Wait: 0.005298 Avg Wait: 0.053553 Max Wait: 0.195775 db file scattered read 0.175371 On DB Server Min Wait: 0.000354 Avg Wait: 0.007307 Max Wait: 0.015409
Posing the question again: should the OPTIMIZER_INDEX_COST_ADJ parameter be set to the lower number to “immediately tune all of the SQL in your database to favor index scans over full-table scans”? Some questions do not have simple answers.
Incidentally, essentially the same parameters were used for 11.1.0.6, 11.1.0.7 and 11.2.0.1 with the SGA_TARGET set to 8G, the DB_KEEP_CACHE_SIZE set to 6G (the test table used the DEFAULT buffer pool), the PGA_AGGREGATE_TARGET set to 1800M, and most other parameters at their default values.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc .
Received on Fri Sep 04 2009 - 20:04:37 CDT