Re: REDO log shifts are blocking for DB updates !!!

Kjell R. Christensen wrote:
>
> Hi !
>
> We are currently implementing a 'time critical' system where all
> accesses towards the DB must be guaranteed a response time less or equal
> to 150 milliseconds.
> The transactions consists of both select and select for update
> statements.
>
> THE PROBLEM !!!
> Since transaction also contains updates, the REDO log file fills up and
> are switches on regular basis. During the switch the update statements
> are stalled/delayed for the same amount of time as it takes to switch
> REDO log file. Why ?????
>
> On average an update takes 25 milliseconds, but during the REDO log
> shift it is delayed for 3 SECONDS!!!!
>
> This has to be a 'classical' problem in 'this' database 'community' ??
>
> Are there any ways of configuring the instance (init.ora) such that LGWR
> will continue writing wile DBWR and CKPT completes there tasks during
> the REDO log shift?
>
> HW/SW CNFIGURATION:
> Running Oracle Server 7.3.2.2
> AlphaServer 4100 (2 CPUs), 1 GB RAM, 6 x 2.1 GB disks
> OpenVMS 7.1
>
> WHAT ALREADY HAS BEEN TESTED OUT!!!
> -Different sizes of REDO log files.
> -Multithreaded REDO log files.
> -The ALERT log does not contain any incomplete CHECKPOINT messages,
> indicating that CHECKPOINTS are accumulated up, until the REDO log
> shift.
> -Most init.ora parameters has been tested out. such as:
> -db_block_buffers
> -shared_pool_size,
> -log_checkpoint_interval,
> -log_checkpoint_timeout,
> -log_checkpoint_to_alert=YES,
> -db_block_checkpoint_batch,
> -db_file_simultaneous_writes,
> -log_buffers.
>
> -REDO log size has been tested out for the range 25K -> 10 MB, and we
> are currently 'stuck' on 3 MB.
>
> An attached WORD file will show the delay graphically. Both axis are in
> milliseconds.
>
> Any hints will be appreciated,
>
> Kjell R.

Have you tried using a checkpoint process to take up the slack between log switches by setting the checkpoint_process parameter in the init.ora file to true? Or you could make size of the log large enough so that it won't do a switch until you force it to switch. Hope this helps and please let me know what you find out what works.

Edward Hall
ehall_at_epicuc.com
Database Administrator
Entertainment Publications, INC. Received on Fri Feb 14 1997 - 00:00:00 CST