| |
 |
|
Oracle Hardware Scalability Testing
Oracle Database Tips by Donald Burleson |
| Burleson
Consulting specializes in Oracle hardware scalability
testing services, and you can call for immediate testing of
your Oracle server. |
 |
One test that can be performed to determine how
increasing CPU speed will affect performance in Oracle is to perform
a Logical IO test. The idea of the test is to generate the maximum
number of logical I/Os over a measured period and determine the
maximum LIO per second that the system is capable of from the Oracle
viewpoint.
For example, using a script created by Andy
Rivenes, the test environment (Dual CPU 900 MHz RISC with 4 gig of
memory on SUN 2.9 Solaris 64 bit) yields the following results for
maximum Oracle 9i 9.2.0.1 LIO per second:
|
2-900
MHz RISC 64 bit |
|
LIO/sec |
Time(CS) |
IO/sec |
|
51138 |
157 |
|
|
50810 |
158 |
|
|
50490 |
159 |
|
|
51134 |
157 |
|
|
AVG |
|
50893 |
As a comparison, I ran the identical test using
my 2.8 GHz P-IV 32 bit laptop using XP SP-2 with Oracle9i 9.2.0.5:
|
1-2.8
GHz P IV 32 bit |
|
LIO |
Time(CS) |
IO/sec |
|
83467 |
48 |
|
|
77038 |
52 |
|
|
80120 |
50 |
|
|
83458 |
48 |
|
|
AVG |
|
81020.75 |
As you can see, using an identical test yielded
results that seem to indicate my Laptop will perform 1.6 times
faster for logical IOs than the development SUN box for a single
user query.
|
Perf Inc |
|
159.1982 |
Percent |
The results are indicative not only of the
increased speed of the processor, even taking into account dual
verses single and RISC verses standard, but also of the higher speed
memory probably in use in the Laptop.
It is logical to assume that increasing the
number and performance rating of the CPUS in the database
environment, as well as going with newer, faster memory, will
produce similar speed up in the SUN environment. So switching to
multiple 1.8 GHz CPUs with faster memory should produce similar
results.
One has to wonder what speed increase could be
achieved with a quad 3.4 GHz Opteron or other commodity CPU
environment with high speed front side buses (in excess of 800 MHz)
and high speed ECC memory utilizing a smaller footprint OS such as
Linux.
The RAW data from the performance tests is in
the appendix.
Appendix A: Raw data from LIO Testing
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
SQL> @cpu_test
drop table dbtest
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
PL/SQL procedure successfully completed.
Commit complete.
SID Stat Num Stat Name
Value
---- -------- ----------------------------------------
------------
23 12 CPU used by this session
22
23 9 session logical reads
4,737
23 41 consistent gets
2,406
23 40 db block gets
2,331
23 42 physical reads
131
23 225 buffer is pinned count
758
23 11 CPU used when call started
22
7 rows selected.
> Logical I/O Rate Test
> This script will generate output that can be used to
> correlate Oracle logical I/Os to CPU capacity and speed.
> This can be used to compare CPU speed between platforms and
> Oracle releases or to correlate workload capacity for the
> system in question.
>
> The basic premise is that Oracle logical I/Os translate
directly
> to CPU usage. By measuring the time it takes to execute a
series
> of logical I/Os we can measure the rate a CPU can execute a
> logical I/O. Since all logical I/Os are not the same, the
numbers
> produced by this test should be considered theoretical
maximums.
> These numbers will give values that can be used to measure
CPU
> capacity and relative speed for an Oracle database, and
should be
> within an acceptable margin of error.
>
> NOTE: It is important that physical reads remain constant for
each
> loop in order to insure that only logical I/Os affect the
timing.
> A busy SGA may prevent this as well as CPU queuing for
overall
> timing. It is recommended that this test be run on an idle,
or
> near idle machine.
>
> The results of this script show the session logical reads
> performed for each loop, which should be constant, and the
total
> time, which is in hundredths of a second. The statistic
"physical
> reads" is included to verify that all blocks were cached.
> These values can be used to calculate the maximum logical
I/Os per
> second that can be executed:
>
> session logical reads / ( total time / 100 ) = maximum
logical
> I/Os per second
>
> If calculating for capacity against existing information
> (e.g. SYSMON data) then the total period (elapsed time) must
be
> known, the total number of CPUs available, and some fudge
factor
> (e.g. CPU queuing, SMP scalability):
> Interval capacity (with 20% fudge factor) =
> logical I/Os per second * total time * # of CPUs * .8
**********
Loop > 1
**********
CPU used by this session = 33
session logical reads = 7335
consistent gets = 4964
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
*
CPU used by this session = 33
session logical reads = 47399
consistent gets = 45028
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
**
total time: 48
LIOs/sec per CPU: 83467
LIOs/sec system total: 83467
**********
Loop > 2
**********
CPU used by this session = 33
session logical reads = 47399
consistent gets = 45028
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
*
CPU used by this session = 33
session logical reads = 87459
consistent gets = 85088
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
**
total time: 52
LIOs/sec per CPU: 77038
LIOs/sec system total: 77038
**********
Loop > 3
**********
CPU used by this session = 33
session logical reads = 87459
consistent gets = 85088
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
*
CPU used by this session = 33
session logical reads = 127519
consistent gets = 125148
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
**
total time: 50
LIOs/sec per CPU: 80120
LIOs/sec system total: 80120
**********
Loop > 4
**********
CPU used by this session = 33
session logical reads = 127519
consistent gets = 125148
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
*
CPU used by this session = 33
session logical reads = 167579
consistent gets = 165208
db block gets = 2371
physical reads = 173
buffer is pinned count = 767
CPU used when call started = 33
**
total time: 48
LIOs/sec per CPU: 83458
LIOs/sec system total: 83458
PL/SQL procedure successfully completed.
SID Stat Num Stat Name
Value
---- -------- ----------------------------------------
------------
23 12 CPU used by this session
303
23 9 session logical reads
167,585
23 41 consistent gets
165,214
23 40 db block gets
2,371
23 42 physical reads
173
23 225 buffer is pinned count
767
23 11 CPU used when call started
303
7 rows selected.
Table dropped.
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jan 23 14:02:12
2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights
reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
prts1>@cpu_test
drop table dbtest
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
PL/SQL procedure successfully completed.
Commit complete.
SID Stat Num Stat Name
Value
---- -------- ----------------------------------------
------------
10 12 CPU used by this session
41
10 9 session logical reads
3,016
10 41 consistent gets
242
10 40 db block gets
2,774
10 42 physical reads
3
10 222 buffer is pinned
count 2
10 11 CPU used when call started
41
7 rows selected.
> Logical I/O Rate Test
> This script will generate output that can be used to
> correlate Oracle logical I/Os to CPU capacity and speed.
> This can be used to compare CPU speed between platforms and
> Oracle releases or to correlate workload capacity for the
> system in question.
>
> The basic premise is that Oracle logical I/Os translate
directly
> to CPU usage. By measuring the time it takes to execute a
series
> of logical I/Os we can measure the rate a CPU can execute a
> logical I/O. Since all logical I/Os are not the same, the
numbers
> produced by this test should be considered theoretical
maximums.
> These numbers will give values that can be used to measure
CPU
> capacity and relative speed for an Oracle database, and
should be
> within an acceptable margin of error.
>
> NOTE: It is important that physical reads remain constant for
each
> loop in order to insure that only logical I/Os affect the
timing.
> A busy SGA may prevent this as well as CPU queuing for
overall
> timing. It is recommended that this test be run on an idle,
or
> near idle machine.
>
> The results of this script show the session logical reads
> performed for each loop, which should be constant, and the
total
> time, which is in hundredths of a second. The statistic
"physical
> reads" is included to verify that all blocks were cached.
> These values can be used to calculate the maximum logical
I/Os per
> second that can be executed:
>
> session logical reads / ( total time / 100 ) = maximum
logical
> I/Os per second
>
> If calculating for capacity against existing information
> (e.g. SYSMON data) then the total period (elapsed time) must
be
> known, the total number of CPUs available, and some fudge
factor
> (e.g. CPU queuing, SMP scalability):
> Interval capacity (with 20% fudge factor) =
> logical I/Os per second * total time * # of CPUs * .8
**********
Loop > 1
**********
CPU used by this session = 169
session logical reads = 5437
consistent gets = 2602
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
*
CPU used by this session = 169
session logical reads = 45581
consistent gets = 42746
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
**
total time: 157
LIOs/sec per CPU: 25569
LIOs/sec system total: 51138
**********
Loop > 2
**********
CPU used by this session = 169
session logical reads = 45581
consistent gets = 42746
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
*
CPU used by this session = 169
session logical reads = 85721
consistent gets = 82886
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
**
total time: 158
LIOs/sec per CPU: 25405
LIOs/sec system total: 50810
**********
Loop > 3
**********
CPU used by this session = 169
session logical reads = 85721
consistent gets = 82886
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
*
CPU used by this session = 169
session logical reads = 125861
consistent gets = 123026
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
**
total time: 159
LIOs/sec per CPU: 25245
LIOs/sec system total: 50490
**********
Loop > 4
**********
CPU used by this session = 169
session logical reads = 125861
consistent gets = 123026
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
*
CPU used by this session = 169
session logical reads = 166001
consistent gets = 163166
db block gets = 2835
physical reads = 8
buffer is pinned count = 7
CPU used when call started = 169
**
total time: 157
LIOs/sec per CPU: 25567
LIOs/sec system total: 51134
PL/SQL procedure successfully completed.
SID Stat Num Stat Name
Value
---- -------- ----------------------------------------
------------
10 12 CPU used by this session
1,817
10 9 session logical reads
166,007
10 41 consistent gets
163,172
10 40 db block gets
2,835
10 42 physical
reads 8
10 222 buffer is pinned
count 7
10 11 CPU used when call started
1,817
7 rows selected.
|
|
|
and include the URL for the page.