Re: log file sync

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.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

• Original Message ----- From: "Alex Gorbachev" <gorbyx_at_gmail.com> To: <oracle_at_digistar.com> Cc: "Christian Antognini" <Christian.Antognini_at_trivadis.com>; <oracle-l_at_freelists.org> Sent: Saturday, October 14, 2006 1:31 AM Subject: Re: log file sync

> 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 9.2.0.5.
> 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.
>

--
http://www.freelists.org/webpage/oracle-l