Performance problem related with strange increase of physical writes
Hello all,
10.2.0.4 database running on a Windows x-64 machine.
Suddenly database performance dropped and only becomes again normal after reboot machine (including database of course) and storage system without any change in database neither application neither machine, operating neither storage system.
I have compared with awr the problematic period and normal period (after reboot) and the main differences are:
a) Log File Sync: the main event wait in problematic period is log file sync: 40%
We know I/O perfomance dropped during performance period, so this is almost sure due to that.
Problematic:
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
log file sync 4,736,735 330,752 70 40.4 Commit
CPU time 210,327 25.7
control file sequential read 5,881,719 91,861 16 11.2 System I/O
db file sequential read 3,764,625 86,101 23 10.5 User I/O
db file parallel write 11,826,201 72,948 6 8.9 System I/O
Normal:
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 309,060 79.3
log file sync 6,602,054 28,680 4 7.4 Commit
db file sequential read 5,591,641 19,566 3 5.0 User I/O
enq: TX - row lock contention 23,475 17,273 736 4.4 Application
log file parallel write 8,540,045 15,717 2 4.0 System I/O
b) In problematic period there were much more physical writes, but however db block changes were less !!
Probably this was what did decrease the I/O performance
I would like to know:
1) Why physical writes are greater in problematic period but db block changes are not ?
2) After machine and storage reboot, the physical writes decrease to the normal number. The application did not change. The Storage system response time got back to usual numbers ...
Could it be the increment in physical writes due to log file sync waits and therefore in redo logs writes and in general in all writes, that is, could it be that there were not more 'logical' physical writes but more 'retries' , 'spurious' or 'useless' writes due to time-outs or high storage response time or due to a general performance degradation of the database (The storage systems also shows the increment in physical writes, that is, for the storage system were real ).
I say this because in problematic period there are metrics as "DBWR revisited being-written buffer" higher than in normal period.
3) If the answer to previos question is no, then could i know what processes or sqls were the main cause of the increase of physical writes ? I have tried quering dba_hist_seg_stat and dba_hist_sqlstat; but this is not very accurate, we have snapshots every hour
Thanks a lot in advance !!!
Jorge
Edited by: JorgeGrijalba on May 3, 2013 2:10 AM