Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: log file sync

Re: log file sync

From: Jonathan Lewis <>
Date: Sun, 15 Oct 2006 10:49:43 +0100
Message-ID: <002a01c6f03f$3e1d20a0$0200a8c0@Primary>

Did you get a bug number on this ?

I've seen the problem appearing at both the leaf node split and the branch node split - but since branch node splits are relatively rare checking for leaf node splits is an indicator that either problem could be occurring.

The comment I got from Oracle was that this wasn't a bug as such, just an unfortunate side effect of the way a particular requirement of space management happened to work in extreme cases.

Index blocks that are empty are left in the index structure as well as being placed on the free list. If the number of blocks on the free list is large, then a split (which needs a free block) may pick a block which is located in a position which makes it unusable (for various technical reasons). Unfortunately, the code executes the split before discovering this is the case, so has to rollback (where we see the 'transaction rollback', 'redo synch write' and 'log file sync') and try the next block on the freelist.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

> Jonathan reminded me one issue we hit with branch block splits... It
> was a bug but the way I figured it was mining redo logs and dumping
> redo blocks. One session was crazy with a lot of LIOs and rollbacks
> (transactional rollbacks and not user rollbacks) but doing apprently
> "nothing" useful. After investigation of redo block dumps we figured
> out that Oracle was trying to do branch block split and then was
> applying undo right after that + rollback. That was
> I didn't have the trace for it so I can't say if it caused XCTEND
> lines but it should have produced log file syncs. I would check
> rollbacks in session stats.

Received on Sun Oct 15 2006 - 04:49:43 CDT

Original text of this message