Re: Simple SQL waiting on 'log file sync'

I missed the start of this

Which version of the database.

Given your comment about a large index being rebuilt, it may be you've hit a bug/anomaly where leaf blocks splits cause recursive transactions that have to be rolled back because the split demands an empty block which is on the free list but still in the index-tree. Under certain "exotic" circumstances, the call can fail and rollback after most of the splitting work has been done. This means another leaf block has to be found, and the process repeated. The workaround is to coalesce the index at 'low-stress' moments so that the empty blocks are detached from the index-tree structure.

Did you also see a high number of 'transaction rollbacks' at the same time ?

Regards

Jonathan Lewis

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

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

http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Nov 2005

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