Re: Is "_db_block_cache_protect = true" killing my throughput?

A copy of this was sent to "Christopher Allen" <progplusSPAMBEGONE_at_gte.net> (if that email address didn't require changing) On Thu, 12 Aug 1999 22:14:51 GMT, you wrote:

>Hundreds of them. I have the feeling you're about to tell me something
>really useful. *chuckle*
>

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.

This can be a *major* performance killer. If you have the 'out of the box' install, you probably have 2 or 3 by 512K logs. You should probably start with at least 3 - 5meg logs and go up from there.

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... Modest is in the eye of the beholder and it really depends on your system. I go from 5meg, to 25meg, to 1gig -- depending on the size and constraints of the system.  

>Christopher
>
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:37b3355e.11383668_at_newshost.us.oracle.com...
>> A copy of this was sent to "Christopher Allen"
><progplusSPAMBEGONE_at_gte.net>
>> (if that email address didn't require changing)
>> On Thu, 12 Aug 1999 20:53:55 GMT, you wrote:
>>
>> do you see this lots "checkpoint cannot complete" in your alert file?
>>
>>
>> >
>> >I tried disabling the $ORACLE_HOME/dbs/init$ORACLE_SID.ora lines for
>> >db_block_cache_protect and the four event lines. After bouncing the
>> >database, performance was a touch better: 213 records per second, instead
>of
>> >200. Still, pretty poor. Even my laptop with Personal Oracle does
>better.
>> >
>> >I appreciate all assistance with this!
>> >
>> >Christopher
>> >
>> >
>>
>>

--
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 Fri Aug 13 1999 - 07:08:07 CDT