REDO log shifts are blocking for DB updates !!!

From: Kjell R. Christensen <krc_at_nera.no>
Date: 1997/02/11
Message-ID: <3300699E.3C39@nera.no>#1/1

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. Received on Tue Feb 11 1997 - 00:00:00 CST