Message-Id: <10576.113539@fatcity.com> From: "Steve Adams" Date: Wed, 2 Aug 2000 10:29:49 +1000 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@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@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