Re: REDO log shifts are blocking for DB updates !!!
Date: 1997/02/23
Message-ID: <19970223104400.FAA26604_at_ladder02.news.aol.com>#1/1
How many redo log files are you using? , since this may provide the answer. I assume you are running archive logging. If you are using archive logging, then the log you are switching to must have been archived before it can be used, so it could be that the archiving hasn't finished before the log switch occurs. If you introduce some more redo log files it may alleviate the problem.
Subject: REDO log shifts are blocking for DB updates !!! From: "Kjell R. Christensen" <krc_at_nera.no> Date: Tue, 11 Feb 1997 12:44:14 +0000
Message-ID: <3300699E.3C39_at_nera.no>
Hi !
[Quoted] 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.
[Quoted] An attached WORD file will show the delay graphically. Both axis are in milliseconds.
Any hints will be appreciated,
Kjell R. Received on Sun Feb 23 1997 - 00:00:00 CET