log file parallel write
Hi The statspack report is like this. There is lot of waits for log file parallel write.
^LWait Events for DB: ITVD4 Instance: ITVD4 Snaps: 5 -6
-> s - second
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc (idle events last)
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
log file parallel write 96,536 95,819 87 1 1.0
db file parallel write 626 313 14 23 0.0
db file scattered read 27,977 0 9 0 0.3
control file parallel write 618 0 5 8 0.0
db file sequential read 19,044 0 3 0 0.2
The LGWR background process waits for this event while it is copying redo records from the memory Log Buffer cache to the current redo group's member logfiles on disk. Asynchronous I/O will be used if available to make the write parallel, otherwise these writes will be done sequentially one member after the other. However, LGWR has to wait until the I/Os to all member logfiles are complete before the wait is completed. Hence, the factor that determines the length of this wait is the speed with which the I/O subsystem can perform the writes to the logfile members.
To reduce the time waited for this event, one approach is to reduce the amount of redo generated by the database:
* Make use of UNRECOVERABLE/NOLOGGING options.
* Reduce the number of redo group members to the minimum necessary to ensure not all members can be lost at the same time.
* Do not leave tablespaces in BACKUP mode for longer than necessary. * Only use the minimal level of Supplemental Logging required to achieve the required functionality e.g. in LogMiner, Logical Standby or Streams.
Another approach is to tune the I/O itself:
* Place redo group members on storage locations so that parallel writes do not contend with each other.
* Do not use RAID-5 for redo logfiles.
* Use Raw Devices for redo logfiles.
* Use faster disks for redo logfiles.
* If archiving is being used setup redo storage so that writes for the current redo group members do not contend with reads for the group(s) currently being archived.
OCP DBA (8,8i,9i)
"Let's document it and call it a feature."
Click Here to Expand Forum to Full Width