Re: log buffer growing and sga tunning

Connor McDonald wrote:

> jchiappa wrote:
> >
> > 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
> >
> > -----------------------------------------------------------
> >
> > Got questions? Get answers over the phone at Keen.com.
> > Up to 100 minutes free!
> > http://www.keen.com

>

Perfectly correct. Way too high. This may lead to bad performance. Why don't use archiving. Is this database not important for you?

How is your redo log configuration?
Are they on separate disks? Is Raid 5 used, are the disks mirrored? You should look at these things?

And besides are you having any performance problems or are you only concerned about the statistics?
To help you any further - you could send me an utlbstat report

Yours Stefan Received on Thu Aug 03 2000 - 00:00:00 CDT