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

To: krc_at_nera.no
Subject: Re: REDO log shifts are blocking for DB updates !!! [Quoted] Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools References: <3300699E.3C39_at_nera.no>

Kjell,

I have experienced the same behavior as what you describe. What you need to do is make sure that all of your redo logs and archive directory are on separate devices.

Otherwise, when there is a redo switch, the archiver process copies the previously active redo log to the archive directory WHILE the new active redo log is being used. If they are on the same device, you will have major performance problems during each switch.

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> Visit my Web Page: http://homepage.interaccess.com/~akaplan   <->
<->             email: akaplan_at_interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

In comp.databases.oracle.server you write:

>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 CET