Re: Tuning: What causes "Thread 1 cannot allocate new log" ?

A copy of this was sent to "John Haskins" <76054.334SPAMBEGONE_at_compuserve.com> (if that email address didn't require changing) On Tue, 24 Aug 1999 18:59:07 GMT, you wrote:

>Greetings, helpful folks:
>
>I've been tuning an Oracle 7.3.4 instance running on Solaris 2.6 and have
>run into an error in the instance's alert log that I cannot eliminate. The
>message is:
>
>Thread 1 cannot allocate new log, sequence 4331
>
>Since theer is no number at the beginning of this message, I haven't had a
>lot of luck finding it in the Oracle online documentation. I did use the
>Java-based search function, but it continually crashed. A search of .htm
>files in the Docs directory using the criteria "cannot allocate new log"
>produced no hits.
>
>I'd appreciate any assistance anyone can provide. Whatever is causing this
>problem is also cutting throughput to about 1/10th of normal on the INSERT
>batch where it occurs.
>
>
>
>
>

the infamous "checkpoint not complete, cannot allocate new log" message.

this occurrs when Oracle attempts to reuse a log file but the checkpoint that would flush the blocks that may have redo in this log file has not yet completed -- we must wait until that checkpoint completes before we can reuse that file -- thats when this message is printed. during this time (when we cannot allocate a new log) processing is suspended in the database while the checkpoint is made to complete ASAP.

The major way to relieve this is to have sufficient log to carry you through peak times. that way, we can complete the checkpoint while you are not busy.

also make sure your checkpoints happen as fast as they can (eg: enable ASYNC IO or configure >1 DBWR if ansyc IO cannot be used, make sure disks are not contending with other apps and so on)

Another way is to make the log files smaller, hence increasing the frequency with which we checkpoint (log checkpoint interval and other init.ora parameters achieve the same effect btw).

I myself prefer to use a modest sized log file (around 25meg) and will add logfiles until these messages cease...  

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 24 1999 - 14:15:46 CDT