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

Ari Kaplan wrote:
>
> To: krc_at_nera.no
> Subject: Re: REDO log shifts are blocking for DB updates !!!
> 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.

Thanks to all, for the advice given!!!!!!

Ari Kaplan, you were absolutely 'spot on' !

By using the MONITOR command in VMS, I verified that some of the disks had 'disk i/o request queuelength' >5 which obviously where causing an i/o congestion.

The maximum peak is now 700 milliseconds for a 6MB redo file. Is this within your scope of expectation?

Since this is known to be a broblem, do any of you have a suggestion for what types og database file to pair together on devices? Should there be a mix of redo,control files or redo and tablespace files etc.

Thanks again for your help so far,

Kjell R. Received on Thu Feb 13 1997 - 00:00:00 CET