Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!
From: Charles Hooper on 4 Sep 2009 21:04
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 .
From: Jonathan Lewis on 5 Sep 2009 02:04


> "Charles Hooper" <hooperc2000(a)yahoo.com> wrote in message
> news:1ea99c67-8713-4ece-a0f5-85f66851b016(a)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).

....


Charles,

Interesting figures.
A couple of follow-on questions:
How much memory in the machine ?
How many CPUs
Were you running the client locally - ipc or tcp
How much CPU usage "outside" Oracle in each test
Any significant variation in 'prefetch' blocks
Any other significant variation in stats


--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


From: Aman Sharma on 5 Sep 2009 03:27
On 5 Sep, 06:04, Charles Hooper <hooperc2...(a)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....
From: Robert Klemme on 5 Sep 2009 06:41
On 05.09.2009 08:04, Jonathan Lewis wrote:
>> "Charles Hooper" <hooperc2000(a)yahoo.com> wrote in message
>> news:1ea99c67-8713-4ece-a0f5-85f66851b016(a)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).
>
> ...
>
>
> Charles,
>
> Interesting figures.
> A couple of follow-on questions:
> How much memory in the machine ?
> How many CPUs
> Were you running the client locally - ipc or tcp
> How much CPU usage "outside" Oracle in each test
> Any significant variation in 'prefetch' blocks
> Any other significant variation in stats

I am also wondering about table and index stats. Charles, I see you
only mention system stats with NOWORKLOAD. What about stats for the
table and what about system stats gathered with INTERVAL and / or
START/STOP?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
From: Matthias Hoys on 5 Sep 2009 11:17

"Jonathan Lewis" <jonathan(a)jlcomp.demon.co.uk> wrote in message
news:stydnRbjqr7nYjzXnZ2dnUVZ8nSdnZ2d(a)bt.com...
>
>
>> "Charles Hooper" <hooperc2000(a)yahoo.com> wrote in message
>> news:1ea99c67-8713-4ece-a0f5-85f66851b016(a)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).
>
> ...
>
>
> Charles,
>
> Interesting figures.
> A couple of follow-on questions:
> How much memory in the machine ?
> How many CPUs
> Were you running the client locally - ipc or tcp
> How much CPU usage "outside" Oracle in each test
> Any significant variation in 'prefetch' blocks
> Any other significant variation in stats
>
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>

And what disk subsystem did you use? DAS or a shared system?

Matthias


 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: So is 11.2.0.2 going to be out anytime soon?
Next: Ora-19906 when restoring user managed backup - stumped!