REDO log shifts are blocking for DB updates !!!
Date: 1997/02/11
Message-ID: <3300699E.3C39_at_nera.no>#1/1
Hi !
[Quoted] [Quoted] We are currently implementing a 'time critical' system where all
[Quoted] [Quoted] 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 !!!
[Quoted] [Quoted] 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 ?????
[Quoted] [Quoted] On average an update takes 25 milliseconds, but during the REDO log shift it is delayed for 3 SECONDS!!!!
[Quoted] [Quoted] This has to be a 'classical' problem in 'this' database 'community' ??
[Quoted] [Quoted] 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
[Quoted] [Quoted] AlphaServer 4100 (2 CPUs), 1 GB RAM, 6 x 2.1 GB disks
OpenVMS 7.1
WHAT ALREADY HAS BEEN TESTED OUT!!!
[Quoted] -Different sizes of REDO log files. [Quoted] -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.
[Quoted] [Quoted] -REDO log size has been tested out for the range 25K -> 10 MB, and we are currently 'stuck' on 3 MB.
[Quoted] [Quoted] 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 CET