RE: delay in commit statements

--openmail-part-09881615-00000001

Content-Type: text/plain; charset=US-ASCII; name="BDY.TXT"
Content-Disposition: inline; filename="BDY.TXT"
Content-Transfer-Encoding: 7bit

Arvind,

Carlos pointed you in the right direction - look at the redo log, rollback segs and associated coinfigurations. Stated very simply, an UPDATE just generates the change vectors and changes the data in the SGA block buffers (data and redo) which may not result in disk I/O while a COMMIT has to flush these blocks out to disk. (A lot goes on in between, though!) In short, COMMIT _has_ to generate physical I/O while UPDATE may complete with just logical I/O which consumes only CPU. This points to I/O bottlenecks on your system. If using UNIX on non-RAID5 disks, iostat will give you a good feel of which disks are heavily loaded. Are you using some other application on that system as well? Also have a look at V$SYSTEM_EVENT, esp. the 'db file %' values. Are you seeing large wait values? (need TIMED_STATISTICS=TRUE). You can trace the file-in-wait from V$SESSION_WAIT (look at p1 values) for all processes. Cross-check these values with output of iostat and redistribute the files....

An excellent description of wait events can be seen in the Oracle 8i Reference Guide. This is based on Anjo Kolk's excellent paper on Waits.

John Kanagaraj
Brunei Shell Petroleum
http://www.geocities.com/john_sharmila

God so loved the world that He didn't send a committee! (See John 3:16 for details)

Watch your thoughts, it becomes word. Watch your words, it becomes actions. Watch your actions, it becomes your habit. Watch your habit, it becomes your character. Watch your character, it becomes your Destiny : Michael Buen - alleged co-author of the love bug virus

• Opinions expressed here are solely mine and not necessarily those of