oracle中sync,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.