| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: redo log space requests growing and log stats : beyond the basics
Hi Chark,
For info on the log block size, please see http://www.ixora.com.au/tips/creation/raw_log_files.htm. For the answer on redo wastage please see http://www.ixora.com.au/q+a/1999_09.htm and scroll down to the entry from 25 September 1999.
Regards,
Steve Adams
http://www.ixora.com.au/ http://www.oreilly.com/catalog/orinternals/ http://www.christianity.net.au/
-----Original Message-----
From: Sairlao, Chark [mailto:SairlaoC_at_transfield.com.au]
Sent: Wednesday, 2 August 2000 13:09
To: 'Steve Adams'; Sairlao, Chark; 'J. Laurindo Chiappa'; DBAs -
TeleLists; LazyDBA/Quickdocs -
Subject: RE: redo log space requests growing and log stats : beyond the
basics
Thanks Steve,
Still confused, so what is the default size for log block? when you said
last log block in each log I/O
do you mean when lgwr flush the log buffer to disk and at that time the
last
log block is not fully utilized?
sorry couldn't find a doco on redo wastage on your website, can you tell
us
which doco it is.
Regards,
Chark
-----Original Message-----
From: Steve Adams [mailto:steve.adams_at_ixora.com.au]
Sent: Wednesday, 2 August 2000 10:53
To: Sairlao, Chark; 'J. Laurindo Chiappa'; DBAs - TeleLists;
LazyDBA/Quickdocs -
Subject: RE: redo log space requests growing and log stats : beyond the
basics
Hi Chark,
That bit about a large log buffer causing 'redo wastage' is not correct.
Redo wastage is the unused part of the last log block in each log I/O.
It therefore depends on the log block size (over which you have no
control)
and the LGWR I/O rate (which is effectively the commit rate in this
case).
Regards,
Steve Adams
http://www.ixora.com.au/ http://www.oreilly.com/catalog/orinternals/ http://www.christianity.net.au/
-----Original Message-----
From: Sairlao, Chark [mailto:SairlaoC_at_transfield.com.au]
Sent: Wednesday, 2 August 2000 10:33
To: 'J. Laurindo Chiappa'; DBAs - TeleLists; LazyDBA/Quickdocs -; ODTUG
Oracle DBA List; VOUG - DBAs Virginia
Subject: RE: redo log space requests growing and log stats : beyound the
basics
you have no contention with redo log buffers at all, 4 space request out
of
1 million entries almost 0.
the ratio redo space request to redo entries if it is less than 1:5000
is
considered good any thing bigger then have to increase log_buffers
value.
Oh you set it to 20Mb is far too big, I would consider set to 1 MB is
big
already.
Because you have set it too big that is why you got big wastage.
Chark
-----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
(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
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
 |
 |