| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RMAN Performance Problem
I have gathered some statistics on the RMAN performance problem, =
although it is difficult to catch it because it happens so quickly. The =
main wait event seems to be async disk IO. I also ran statspack a =
couple of days ago, and async disk IO showed up as the top wait event =
too. Does this usually indicate a problem with the way the disk is =
configured. I think we are using RAID 5, but I have to check on that =
tomorrow.
These are the stats I got today:
This was before the backups ran:
Database: prd1 27-SEP-04 =
04:01pm
Report: swpctx.sql OSM by OraPub, Inc. Page =
1
System Event CHANGE Activity By PERCENT
Time Waited % Time
Wait Event (sec) Waited Waits % Waits
----------------------------------- ----------- ------- -------- -------
db file scattered read 10.890 80.55 460 80.56
db file parallel write 0.630 4.66 2 0.35
control file parallel write 0.590 4.36 12 2.10
log file sync 0.120 0.89 2 0.35
log file parallel write 0.090 0.67 6 1.05
db file sequential read 0.060 0.44 3 0.53
control file sequential read 0.000 0.00 9 1.58
direct path write 0.000 0.00 1 0.18
LGWR wait for redo copy 0.000 0.00 0 0.00
async disk IO 0.000 0.00 0 0.00
buffer busy waits 0.000 0.00 0 0.00
control file heartbeat 0.000 0.00 0 0.00
control file single write 0.000 0.00 0 0.00
db file parallel read 0.000 0.00 0 0.00
db file single write 0.000 0.00 0 0.00
direct path read 0.000 0.00 0 0.00
enqueue 0.000 0.00 0 0.00
free buffer waits 0.000 0.00 0 0.00
latch free 0.000 0.00 0 0.00
library cache load lock 0.000 0.00 0 0.00
library cache pin 0.000 0.00 0 0.00
local write wait 0.000 0.00 0 0.00
log buffer space 0.000 0.00 0 0.00
log file sequential read 0.000 0.00 0 0.00
log file single write 0.000 0.00 0 0.00
log file switch completion 0.000 0.00 0 0.00
row cache lock 0.000 0.00 0 0.00
sort segment request 0.000 0.00 0 0.00
undo segment extension 0.000 0.00 0 0.00
write complete waits 0.000 0.00 0 0.00
SQL> @swpctx %
These are during the backups:
Database: prd1 27-SEP-04 =
04:03pm
Report: swpctx.sql OSM by OraPub, Inc. Page =
1
System Event CHANGE Activity By PERCENT
Time Waited % Time
Wait Event (sec) Waited Waits % Waits
----------------------------------- ----------- ------- -------- -------
db file scattered read 51.250 45.75 3633 54.58
async disk IO 20.510 18.31 1054 15.84
db file parallel read 13.680 12.21 334 5.02
db file parallel write 11.430 10.20 18 0.27
db file sequential read 6.120 5.46 497 7.47
control file parallel write 4.790 4.28 68 1.02
log file parallel write 1.710 1.53 18 0.27
enqueue 1.510 1.35 3 0.05
db file single write 0.310 0.28 18 0.27
control file sequential read 0.290 0.26 915 13.75
log file sync 0.290 0.26 5 0.08
direct path write 0.230 0.21 37 0.56
log file sequential read 0.170 0.15 19 0.29
log file single write 0.030 0.03 2 0.03
direct path read 0.020 0.02 36 0.54
latch free 0.000 0.00 2 0.03
LGWR wait for redo copy 0.000 0.00 0 0.00
buffer busy waits 0.000 0.00 0 0.00
control file heartbeat 0.000 0.00 0 0.00
control file single write 0.000 0.00 0 0.00
free buffer waits 0.000 0.00 0 0.00
library cache load lock 0.000 0.00 0 0.00
library cache pin 0.000 0.00 0 0.00
local write wait 0.000 0.00 0 0.00
log buffer space 0.000 0.00 0 0.00
log file switch completion 0.000 0.00 0 0.00
row cache lock 0.000 0.00 0 0.00
sort segment request 0.000 0.00 0 0.00
undo segment extension 0.000 0.00 0 0.00
write complete waits 0.000 0.00 0 0.00
SQL> @swpctx %
Database: prd1 27-SEP-04 =
04:03pm
Report: swpctx.sql OSM by OraPub, Inc. Page =
1
System Event CHANGE Activity By PERCENT
Time Waited % Time
Wait Event (sec) Waited Waits % Waits
----------------------------------- ----------- ------- -------- -------
async disk IO 40.320 49.99 2266 53.41
db file scattered read 15.840 19.64 1524 35.92
db file parallel read 11.270 13.97 173 4.08
db file sequential read 4.890 6.06 159 3.75
control file parallel write 2.510 3.11 13 0.31
log file sync 1.640 2.03 5 0.12
db file parallel write 1.260 1.56 6 0.14
log file parallel write 0.940 1.17 8 0.19
control file sequential read 0.000 0.00 14 0.33
direct path write 0.000 0.00 1 0.02
LGWR wait for redo copy 0.000 0.00 0 0.00
buffer busy waits 0.000 0.00 0 0.00
control file heartbeat 0.000 0.00 0 0.00
control file single write 0.000 0.00 0 0.00
db file single write 0.000 0.00 0 0.00
direct path read 0.000 0.00 0 0.00
enqueue 0.000 0.00 0 0.00
free buffer waits 0.000 0.00 0 0.00
latch free 0.000 0.00 0 0.00
library cache load lock 0.000 0.00 0 0.00
library cache pin 0.000 0.00 0 0.00
local write wait 0.000 0.00 0 0.00
log buffer space 0.000 0.00 0 0.00
log file sequential read 0.000 0.00 0 0.00
log file single write 0.000 0.00 0 0.00
log file switch completion 0.000 0.00 0 0.00
row cache lock 0.000 0.00 0 0.00
sort segment request 0.000 0.00 0 0.00
undo segment extension 0.000 0.00 0 0.00
write complete waits 0.000 0.00 0 0.00
SQL>
Database: prd1 27-SEP-04 =
04:04pm
Report: swswc.sql OSM by OraPub, Inc. Page =
1
Session Wait Real Time w/Counts
Num. Sess.
Wait Event Waited So Far (sec) Waiting
---------------------------------------- ------------------- ----------
rdbms ipc message 1,280 7
SQL*Net message from client 375 5
smon timer 58 1
async disk IO 1 1
pmon timer 1 1
db file scattered read 0 1
6 rows selected.
SQL> @swswc %
Database: prd1 27-SEP-04 =
04:05pm
Report: swswc.sql OSM by OraPub, Inc. Page =
1
Session Wait Real Time w/Counts
Num. Sess.
Wait Event Waited So Far (sec) Waiting
---------------------------------------- ------------------- ----------
rdbms ipc message 1,322 7
SQL*Net message from client 324 6
smon timer 72 1
async disk IO 0 1
pmon timer 0 1
5 rows selected.
SQL> @swswp db%file%scat%
no rows selected
SQL> @swswp db%file%scat%
no rows selected
SQL> @swswp async
Database: prd1 =
27-SEP-04 04:06pm
Report: swswp.sql OSM by OraPub, Inc. =
Page 1
Session Wait Real Time w/Parameters
W'd So Time
Sess Wait Far W'd
ID Wait Event Stat (secs) (secs) P1 =
P2 P3
1 row selected.
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 27 2004 - 19:15:09 CDT
 |
 |