Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
From: Aman Sharma <amansharma1981_at_gmail.com>
Date: Sat, 5 Sep 2009 00:27:31 -0700 (PDT)
Message-ID: <35458378-3ae7-4179-83d4-d8782819be7a_at_38g2000yqr.googlegroups.com>
On 5 Sep, 06:04, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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 .
Date: Sat, 5 Sep 2009 00:27:31 -0700 (PDT)
Message-ID: <35458378-3ae7-4179-83d4-d8782819be7a_at_38g2000yqr.googlegroups.com>
On 5 Sep, 06:04, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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
> (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|
> |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 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 .
Very nice Charles!
regards
Aman....
Received on Sat Sep 05 2009 - 02:27:31 CDT