RE: redo log space requests growing and log stats : beyond the ba

flushing a large redo_log buffer will take time on each commit.. and the processes will wait on redo log sync ?

Rahul

> ----------
> From: Tom Pall[SMTP:tpall_at_cdproc.com]
> Sent: Monday, August 07, 2000 11:27 PM
> To: Steve Adams; J. Laurindo Chiappa; DBAs - TeleLists; LazyDBA/Quickdocs -
> Subject: Re: redo log space requests growing and log stats : beyond the basics
>
> Steve,
>
> How is the too large lob_buffer damaging commit response times?
>
> I ask this because I've been upping log_buffers on an at times very batch intensive database from
> 20 8192 byte buffers and 8,700 redo buffer allocation retries per day to current 70 8192 byte buffers,
> and 261 redo buffer allocation retries per day.
>
>
> ----- Original Message -----
> From: Steve Adams <steve.adams_at_ixora.com.au>
> To: J. Laurindo Chiappa <jlchiappa_at_uol.com.br>; DBAs - TeleLists <ORACLE-L_at_fatcity.com>; LazyDBA/Quickdocs -
> <oracledba_at_quickdoc.co.uk>
> Sent: Tuesday, August 01, 2000 7:29 PM
> Subject: RE: redo log space requests growing and log stats : beyond the basics
>
>
> > Hi Laurindo,
> >
> > Your 'redo log space requests' are a symptom of log switches, not an
> > indication that the log buffer is too small. Indeed your log buffer is
> > too large and this is damaging your commit response times. There is a
> > detailed explanation of these issues available on my web site, as well
> > as an explanation of 'redo wastage' and some of other issues you have
> > raised.
> >
> > Regards,
> > Steve Adams
> > http://www.ixora.com.au/
> > http://www.oreilly.com/catalog/orinternals/
> > http://www.christianity.net.au/
> >
> >
> > -----Original Message-----
> > From: J. Laurindo Chiappa [mailto:jlchiappa_at_uol.com.br]
> > Sent: Wednesday, 2 August 2000 10:21
> > To: DBAs - TeleLists; LazyDBA/Quickdocs -; ODTUG Oracle DBA List; VOUG -
> > DBAs Virginia
> > Subject: redo log space requests growing and log stats : beyound the
> > basics
> >
> >
> > Hi ! I'm receiving many redo log spacerequests in my prod db; I followed
> > the common steps in this case (add
> > more log_buffer space; more log member, etc , but no avail. the hardware
> > is : IBM F50 with AIX 4.3.3, 2
> > processors, 1Gb RAM, 1 local (non-raid) disk (with the logs and the
> > control files), and 1 array of disks (due to
> > hardware reasons, unfortunately, mounted as RAID-5), db version is
> > 7.3.4.5, and Oracle is the only app in
> > the box.
> > Parameters of the db :
> >
> > pre_page_sga FALSE
> > log_archive_start FALSE
> > log_archive_buffers 4
> > log_archive_buffer_size 127
> > log_archive_dest ?/dbs/arch
> > log_archive_format %t_%s.dbf
> > log_buffer 20971520
> > log_checkpoint_interval 0
> > log_checkpoint_timeout 0
> > log_block_checksum FALSE
> > log_small_entry_max_size 80
> > log_simultaneous_copies 4
> > log_files 255
> > log_checkpoints_to_alert TRUE
> > delayed_logging_block_cleanouts TRUE
> > dblink_encrypt_login FALSE
> > db_block_buffers 22000
> > db_block_checksum FALSE
> > db_block_size 4096
> > db_block_checkpoint_batch 8
> > db_block_lru_statistics FALSE
> > db_block_lru_extended_statistics 0
> > db_block_lru_latches 2
> > db_files 20
> > db_file_simultaneous_writes 4
> > db_file_multiblock_read_count 16
> > gc_db_locks 100
> > dblink_encrypt_login FALSE
> > db_domain WORLD
> > db_name prod
> > shared_pool_size 130000000
> > shared_pool_reserved_size 0
> > shared_pool_reserved_min_alloc 5000
> >
> > (OBS : I have doubts about the shared_pool_reserved_size=0 and the
> > shared_pool_reserved_min_alloc=5000 ).
> >
> > Note the obvious points : big log buffer (20 Mb) , 2 db writers , 2>
> > db_block_lru_latches and 4
> > db_file_simultaneous_writes (because of the 2 processors), big
> > db_blocks (22000, meanining more than 90
> > Mbs), but no luck . See the stats :
> >
> > elect name, value from v$sysstat where name like '%redo%';
> > NAME VALUE
> > ------------------------------ -----------------
> > redo synch writes 52505.0000
> > redo synch time 103900.0000
> > redo entries 1033556.0000
> > redo size 234973983.0000
> > redo entries linearized .0000
> > redo buffer allocation retries 4.0000
> > redo small copies 289873.0000
> > redo wastage 17960239.0000
> > redo writer latching time 80.0000
> > redo writes 57997.0000
> > redo blocks written 511296.0000
> > redo write time 109835.0000
> > redo log space requests 4.0000
> > redo log space wait time 172.0000
> > redo log switch interrupts .0000
> > redo ordering marks .0000
> >
> > Log switch is reasonably :
> >
> > sys_at_prod::SQL>select * from v$log
> >
> > GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
> > FIRS
> > T_CHANGE# FIRST_TIME
> > ---------- ---------- ---------- ---------- ---------- --- -------------
> > --- ----
> > --------- --------------------
> > 1 1 2545 83886080 1 NO CURRENT
> > 5363474 08/01/00 16:50:41
> > 2 1 2543 83886080 1 NO INACTIVE
> > 5325532 08/01/00 15:13:58
> > 3 1 2544 83886080 1 NO INACTIVE
> > 5348822 08/01/00 15:45:33
> >
> > OBS : the db was bounced at 07:00 and the stats were queried at 17:00
> > (only a day of work, and 4 redo log
> > space requests! )
> > I'm concerning about the high redo wastage, too, and the wait time.
> > Some ideas ?
> >
> > []s
> > Chiappa
> >
> >
> > --------
> > If you're bored, then visit the list's website: http://www.lazydba.com
> > (updated daily)
> > to unsubscribe, send a blank email to
> > oracledba-unsubscribe_at_quickdoc.co.uk
> > to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
> >
> >
> > --------
> > If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
> > to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk
> > to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk
> >
>
>
> --------
> If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
Received on Mon Aug 07 2000 - 21:28:05 CDT