| |
 |
|
Oracle STATSPACK & AWR reports showing scattered read
timings
Oracle Tips by Burleson Consulting
October 28, 2007
|
Samples from
the real world
Work in
progress - Not complete yet!
Here is an Oracle8i system running
Oracle Application manufacturing module. Please note that
multi-block reads show as 2x slower than single block reads:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 65,998.37 15,749.46
Logical
reads: 50,859.74 12,136.87
Block
changes: 450.87 107.59
Physical
reads: 68.71 16.40
Physical
writes: 33.13 7.91
. . .
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (cs) Wt
Time
-------------------------------------------- ------------ ------------
-------
SQL*Net message from
client 1,342,946 47,258,362 79.20
pipe
get 19,517 7,616,463 12.76
rdbms ipc
message 18,109 3,585,905 6.01
smon
timer 12 368,652 .62
pmon
timer 1,180 362,842 .61
. . .
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (cs) (ms) /txn
----------------------------
------------ ---------- ----------- ------ ------
SQL*Net message from
client 1,342,946 0 47,258,362 352 88.4
pipe
get 19,517 16,976 7,616,463 3902 1.3
wakeup time
manager 114 114 350,322 30730 0.0
SQL*Net more data from clien
7,188 0 31,539 44 0.5
log file parallel
write 15,492 0 27,934 18 1.0
db file sequential
read 102,300 0 26,537
3 6.7
log file
sync 14,485 3 23,103 16 1.0
db file scattered
read 7,191 0 5,087
7 0.5
Here is an Oracle eBusiness suite
database where we see a small number of scattered reads, which
are 2x faster than single block reads::
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 8,592.35 30,970.33
Logical
reads: 164,756.72 593,850.28
Block
changes: 31.95 115.16
Physical
reads: 1,762.80 6,353.85
Physical
writes: 30.81 111.04
. . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time
(s) Ela Time
-------------------------------------------- ------------
----------- --------
db file sequential read 3,489,208
6,486 49.21
CPU time
6,336 48.07
db file scattered read 232,831
211 1.60
. . .
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms) /txn
----------------------------
------------ ---------- ---------- ------ --------
db file sequential
read 3,489,208 0 6,486
2
3,661.3
db file scattered
read 232,831 0 211
1 244.3
Here is a sample Oracle 9i
database where we see slower multi-block reads:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 1,378.67 5,856.08
Logical
reads: 348.58 1,480.64
Block
changes: 9.42 40.01
Physical
reads: 62.25 264.42
. . .
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait %
Total
Event Waits Time (s) Wt
Time
-------------------------------------------- ------------ -----------
-------
db file sequential
read 211,516 66
72.71
control file parallel
write 1,178 13
14.83
db file scattered
read 939 4 4.46
log file
sync 868 3 3.23
db file parallel
write 1,100 2 2.61
. . .
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms) /txn
----------------------------
------------ ---------- ---------- ------ --------
db file sequential read
211,516 0 66 0
249.4
control file parallel
write 1,178 0 13 11 1.4
db file scattered
read 939 0 4
4 1.1
Here is another 9i database with
high read activity where see see that multi-block reads register
as faster than sequential reads:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 24,569.28 4,834.38
Logical
reads: 3,751.76 738.22
Block
changes: 136.22 26.80
Physical
reads: 1,023.42 201.37
Physical
writes: 3.67 0.72
. . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
db file scattered
read 145,571 10,098 80.00
CPU
time 1,313 10.40
db file sequential
read 45,408 573 4.54
log file
sync 18,456 362 2.87
log file parallel
write 19,007 121 .95
. . .
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms) /txn
----------------------------
------------ ---------- ---------- ------ --------
db file scattered
read 145,571 0 10,098
69 8.0
db file sequential
read 45,408 0 573 13
2.5
Here is a 10g database
where we see scattered read as being 3x slower than single block access:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo
size: 15,291.71 8,386.60
Logical
reads: 11,389.83 6,246.65
Block
changes: 28.79 15.79
Physical
reads: 23.31 12.78
Physical
writes: 2.36 1.29
. . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ %
Total
Event Waits Time (s) Ela
Time
-------------------------------------------- ------------ -----------
--------
CPU
time 1,899 85.42
latch
free 28,426 93 4.18
db file scattered
read 11,872 68 3.07
log file
sync 19,706 27 1.20
db file sequential
read 15,920 24 1.10
. . .
Avg
Total Wait wait
Waits
Event Waits Timeouts Time (s) (ms) /txn
----------------------------
------------ ---------- ---------- ------ --------
latch
free 28,426 26,676 93 3 1.4
db file scattered
read 11,872 0 68
6 0.6
log file sync
19,706 0 27 1 1.0
db file sequential
read 15,920 0 24 2
0.8
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
