Re: Log switch after 3-second timout: what is it about ?

From: Svend Jensen <Svend.S.Jensen_at_it.dk>
Date: Thu, 17 Jul 2003 21:02:45 +0200
Message-ID: <3f16f2ca$0$32505$edfadb0f@dread16.news.tele.dk>

Spendius wrote:

> Hi (again),
> I forgot this question in one of my recent mails: in
> the course book Enterprise DBA Part 1A: Arch. & Admin.
> you can read at page 7-6 (chapter Maintaining Redo Log
> Files, § regarding the flushing of buffer redo entries
> to redo log files):
>

>>The Oracle server sequentially records all changes made
>>to the DB in the redo log buffer. The redo log buffer 
>>is used in a circular manner. The redo entries are 
>>written to one of the online redo log groups called the 
>>*current* online redo log group by the LGWR process under 
>>the following situations:
>>o when a transacdtion commits
>>o when the redo log buffer becomes 1/3 full
>>o when there is more than a megabyte of changed records 
>>  in the redo log buffer
>>o when a timout occurs (every three seconds)
>>o before the DBWn writes modified bolocks in the DB buffer 
>>  cache to the data files

>
>
> What is this "timeout" they talk about ? Does it have anything to
> do with the value at which you can set the LOG_CHECKPOINT_TIMEOUT ?
> I don't think so, so: is there a way to set it and manage it ? Or
> will it ALWAYS be 3 seconds, whatever your configuration and the
> way you've created your DB ?
>
> Thanks...
> Spendius

The 3 seconds time-out is hard coded and can not be changed. The logwriter sets a wakeup timer to 3 seconds (vector post) just before it goes to sleep. The conditions mentioned above can wake up the log writer before the 3 seconds have elapsed. On wakeup the lgwr flushes the content of the log buffer to redologs on disk. Anything in the log buffer that has not been flushed to disk - never happened ! in case of recovery et. al.
(same as nologging statements - but that's a different case)

This timing has nothing to do with the LOG_CHECKPOINT_TIMEOUT. This is, as the name indicates, a checkpoint issue. Checkpointing is performed before a (redo)log shift, all changed blocks were written to disk by db-writer and file_headers were updated with current SCN number. In many cases, this could be seen from the application as if the database 'froze' some seconds, and then continued (at least on older versions <= 8.0.6, afair) Mostly on systems with large redologs - many megabytes. If this behavior was unsatisfactory you could checkpoint more often by setting a checkpoint time-out (in seconds) or bytes written. This made the 'freeze' time shorter (unnoticed by users) but overall wasted processing time for check pointing increased. (You can't make everybody happy)

Check the DOC, init.ora parameters on checkpoint.

/Svend Received on Thu Jul 17 2003 - 14:02:45 CDT