Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: redo log space requests growing and log stats : beyond the basics

RE: redo log space requests growing and log stats : beyond the basics

From: Steve Adams <steve.adams_at_ixora.com.au>
Date: Wed, 2 Aug 2000 13:21:04 +1000
Message-Id: <10576.113548@fatcity.com>


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

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



If you're bored, then visit the list's website: http://www.lazydba.com
(updated daily)

to unsubscribe, send a blank email to
oracledba-unsubscribe_at_quickdoc.co.uk
to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk

If you're bored, then visit the list's website: http://www.lazydba.com
(updated daily)

to unsubscribe, send a blank email to
oracledba-unsubscribe_at_quickdoc.co.uk
to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk

If you're bored, then visit the list's website: http://www.lazydba.com Received on Tue Aug 01 2000 - 22:21:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US