High commit time

[Former Member]

Hi,

we've had an incident in production where at one time the response times of all transaction slowed down a lot. When we enquired , in stad, we saw that the time was spent in database commit time. The normal time we see for a commit is under 100 ms. During that period , the time went up to 22 seconds. Note that at that same time sequential reads and other db operations were performing fine.

We checked wp trace files, sm21 and oracle logs. There was nothing out of the ordinary.

We were thinking somekind of deadlock but why would it affect different programs.

Any ideas on what could cause this ?

Thanks.

Thierry

[Former Member · Accepted solution]

Hi,

This is what i could suggest for a High Commit time.

If the commit time of the processes in your system is too high, then the system

has to wait for the redo entry to be flushed to disk, which increases

the database response time of your system. See 'log file sync' in the

table above.

I think in your case the database response time may be the prime contributor of the Avg.

Response time.

Recommendation: Check the hardware components in your system that are

part of the I/O related to the online redo log files. For example:

- Check for heavily used data files on the same disks as the online redo

log files.

- Use fast disks or/and disk striping over more than one disk.

- Check whether RAID technology with limited WRITE performance is used

for the online redo log files.

Other possible reasons for high commit times are:

- A slow database writer can cause the logwriter to wait. In this case,

the message 'checkpoint not complete' is written to the Oracle alert

file.

- If the Oracle parameter log_buffer is set too high, this can increase

the wait time.

If you need further assistance, contact your hardware vendor.

Please let me know if it helps and do feel free to get back to me in case of any query.

Thank you,

Tilak

[Former Member]

Hi Thierry

Just for the records: at commit time the logwriter (LGWR) process has to write the contents of the log_buffer to the actual online redo log. What you can do is:

- check your redo log configuration, number of groups, mirrors and their size

- check the disks holding the origlog and mirrlog filesystems, as already mentionend, monitor the disk at the critical times

- there are no locks in the classical sense needed to commit, but there could be latch contention (latch = kind of low level oracle internal locks) -> please monitor v$session, ST04 -> Detailed Analysis -> Session Monitor

- the problem could be caused by high load, either thousends of very small commits, or large transactions creating lots of redo

So i suggest you monitor the system, when everything is going well, and compare it when you have the high times.

Regards

Michael

[Former Member]

One thing to try is to separate the software issues from the hardware. On Windows boxes, I will watch the system monitor to see if the disk queues are filling up and/or if the reads/writes are high (pointing to a storage issue).

Have you noticed anything else about the system that houses the DB?

Hope that helps.

J. Haynes