Re: log buffer growing and sga tunning

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

I would say that your log_buffer is WAY too big...You'll end up with LGWR only doing bursty activity on each commit (which will probably end up leading to your space requests)

I would vetnure that your redo logs being too slow - I'd be trying to isolate them and make them raw...heavy emphasis on the latter.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse