Oracle中的log sync wait

log sync waits are most commonly caused by frequent commits.  When you commit, you must
wait for LGWR to flush the contents of the redo log buffer to disk (else we could not
assure you of recovery).

A small log buffer would manifest itself as a LOG_BUFFER_SPACE wait.

Having data files and other things on the same device as your online redo log can
definitely impact log writers ability to do its job as fast as possible.

Things you can do:

o look at your transactions.  If you are "over committing" -- commiting in the mistaken
belief that you are saving resources and not on true transactional boundaries -- stop it.
Commit only when your transaction is over and do not commit too frequently (if you have
very small transactions, so be it -- you MUST commit but if you are committing just for
the sake of committing, don't)

o speed up LGWR.  Make it more efficient.  Ensure you are not using something like RAID 5
for log devices.  Reduce contention on that device (let LGWR own it).  Ensure LGWR and
ARCH are not contending with eachother (you want at lead 5 devices dedicated to logging
-- you NEED to mirror redo members and you NEED to archive.  So, if you have disks 1..5
you can:

have redo log groups 1, 3, 5, 7, .... on disks 1 and 3
redo log groups 2, 4, 6, 8, .... on disks 2 and 4
archive destination to disk 5

Now, when LGWR is writing to groups 1, 3, 5, 7, .... on disks 1/3, ARCH will be reading
groups 2, 4, 6, 8, ... on disks 2/4 and writing to disk 5.  When LGWR advances, so does
ARCH and they'll switch disks.  Never does ARCH and LGWR contend with eachother.

In fact, you should get rid of all (or most) factors affecting redo log contention. In addition, maybe: - Making sure DBWR checkpoiting and ARCH copying are not blocking log switches (check out ALTER.LOG); - You can also verify that the following statistics in V$SYSSTAT are either high or increasing: "redo buffer allocation retries" - Total number of retries necessary to allocate space in the redo buffer. Retries are needed either because the redo writer has fallen behind or because an event such as a log switch is occurring. "redo log space requests" - Number of times the active log file is full and Oracle must wait for disk space to be allocated for the redo log entries. Such space is created by performing a log switch. In these cases, you can try some things, the main ones being: . Make DBWR more infrequent (performance will inscrease but recoverbility could be compomised). Do that adjusting FAST_START_IO_TARGET (8i) or FAST_START_MTTR_TARGET (9i). . Enlarge your redo files. This will make checkpoiting more infrequent on log switches. . Spawn more DBRWs, ARCHs, or their IO Slaves to increase throughput.



Now - now you'll be waiting for lgwr to finish writing after each record. You'll have tons of log file sync waits (like 1,000,000 of them). And if you have high log file parallel write waits - you might be able to blame part of that on the writes.
I would blame the programmer, but you could blame the IO. the IO isn't really the cause, the algorithm is, but you see what I mean.
Make sense?
log file parallel write is something lgwr waits for.

log file sync waits might be you waiting on lgwr (might be starved for CPU, lgwr might not be the cause at all). If you are waiting on lgwr, you might want to look at what lgwr is waiting for. those could be log file parallel writes.

转自: