-
log file parallel write
Hi The statspack report is like this. There is lot of waits for log file parallel write.
Any solution..?
^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)
Avg
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.
