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: Sat, 12 Aug 2000 03:11:47 +1000
Message-Id: <10586.114382@fatcity.com> 





Hi Lucia,



You need to run the create_xviews.sql script from the prerequisites page
on my web site.



Regards,


Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/






-----Original Message-----


From: Lucia DeMeester [mailto:ldemeester_at_nm2.com]
Sent: Saturday, 12 August 2000 2:29


To: steve.adams_at_ixora.com.au; ORACLE-L_at_fatcity.com
Subject: RE: redo log space requests growing and log stats : beyond the
basics




Steve,



I tried to run the log_block_size script but the view x_$kccle has not
been


defined.  Do I need to run some dbms script to create this view first
and


where is it located?



Thanks!!


Lucia



-----Original Message-----


From: steve.adams_at_ixora.com.au [mailto:steve.adams_at_ixora.com.au]
Sent: Friday, August 11, 2000 12:54 AM


To: ORACLE-L_at_fatcity.com


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




Hi Tom,



Yes, you have clearly gained something here with a larger log buffer,
and in a batch environment that is more important than the commit
response time. Anyway, a 560K log buffer is not exactly huge. It is just
towards the upper end of the "normal" range, and the negative impact on
commit response time from needing to do bigger syncs would be no more
than 20ms. If your redo generation is intensive, then you may also be
doing better in terms of reduced contention for the 'redo allocation'
latch. So what you've done seems right.



However, for future reference, the 'redo buffer allocation retries'
statistic is not as helpful here as 'log buffer space' waits, because it
includes retries after log switches as well as those related to buffer
space.



Also, your suggestion that you have 8192 byte log buffer blocks
surprises me. I am not aware of any platform on which Oracle uses an 8K
log block size. Could you please check using my log_block_size.sql
script.



Thanks,


Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/






-----Original Message-----


Sent: Tuesday, 8 August 2000 2:27


To: Steve Adams; J. Laurindo Chiappa; DBAs - TeleLists;
LazyDBA/Quickdocs -


basics




Steve,



How is the too large lob_buffer damaging commit response times?



I ask this because I've been upping log_buffers on an at times very
batch intensive database from


20 8192 byte buffers and 8,700 redo buffer allocation retries per day to
current 70 8192 byte buffers,


and 261 redo buffer allocation retries per day.







>> 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_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

>>


--
Author: Steve Adams
  INET: steve.adams_at_ixora.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


--- Internet Message Header Follows ---
Received: from newsfeed.cts.com (209.68.192.199)
	by prophet.com (FirstClass Mail Server v5.11)
	transient id 16; 1:03:27 AM -0800
Received: from fatcity.UUCP (uucp_at_localhost)
	by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id BAA66519;
	Fri, 11 Aug 2000 01:02:43 -0700 (PDT)
Received: by fatcity.com (04-May-2000/v1.0f-b69/bab) via UUCP id
001A73C8;
Thu, 10 Aug 2000 23:54:15 -0800
Message-ID: <F001.001A73C8.20000810235415_at_fatcity.com>
Date: Thu, 10 Aug 2000 23:54:15 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
X-Comment: Oracle RDBMS Community Forum
X-Sender: "Steve Adams" <steve.adams_at_ixora.com.au>
Sender: root_at_fatcity.com
Reply-To: ORACLE-L_at_fatcity.com
Errors-To: ML-ERRORS_at_fatcity.com
From: "Steve Adams" <steve.adams_at_ixora.com.au>
Subject: RE: redo log space requests growing and log stats : beyond the


Received on Fri Aug 11 2000 - 12:11:47 CDT












Original text of this message













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