Re: log file sync
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 15 Oct 2006 10:49:43 +0100
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@xxxxxxxxx>
To: <oracle@xxxxxxxxxxxx>
Cc: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>;
<oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
