Re: log buffer growing and sga tunning

Chiappa,

Redo wastage itself is not a problem. It does though indicate a very active LGWR process. This is often the case if the log_buffer is too small. Obviously this is not the case here, as Sybrand has pointed out, log_buffer should typically not need to be greater than 1M, and in most cases around 120k is sufficient. The other reason could be that you have to high a commit rate. This would cause LGWR to be extra busy. I would reduce your log_buffer , and look at the application to reduce the commit rate.

HTH NeilC

Sybrand Bakker wrote:
>
> I wouldn't bother about 4 redo log space requests *per day*. It there would
> have been 4 requests per hour, OK.
> The log_buffer issue has been discussed before here, and the conclusion was
> a large log_buffer was actually having big detrimental effects on
> performance. The absolute maximum was supposed to be 1M!
> You also have a very high log_files setting (255), which may contribute to
> your problems.
> Another obvious point is the 130M shared pool.
> Oracle recommends the complete SGA shouldn't make up more than 25-33 percent
> of complete physical memory, it looks like to me you are probably already
> having a too big SGA, inducing paging and swapping on the O/S side.
>
> Hth,
>
> Sybrand Bakker, Oracel DBA
>
> "jchiappa" <jchiappaNOjcSPAM_at_bbvabrasil.com.br.invalid> wrote in message
> news:0328865d.7c33547b_at_usw-ex0101-005.remarq.com...
> > 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
> >
Received on Wed Aug 02 2000 - 00:00:00 CDT