Oracle-L: RE: Online redo sizing and parameters

From: Freeman, Robert <Robert_Freeman_at_csx.com>
Date: Tue, 07 May 2002 12:28:31 -0800
Message-ID: <F001.0045A6ED.20020507122831@fatcity.com>

Some random thoughts on this topic, since no one else replied...

Assuming you are using Oracle8i...

Generally, these parameters (log_checkpoint_interval and log_checkpoint_timeout) are set
to a 0 value. Setting these otherwise kind of negates the benefits of the larger online redo
log files, as they are triggers for incremental checkpointing. I think they are probably
of less import in 8i in terms of performance impacts than they were in 7.x though because
of performance improvements in the way checkpoints are handled when these targets are
reached. Checkpoints will have some performance impacts, but I'd say that incremental
checkpointing may well reduce overall impacts because when a full checkpoint occurs
(e.g. at log switch) then there will be less overall data to process).

Since you are sizing your online redo logs such that they will switch consistently
every 15 minutes, I doubt there is much of a need to set these. On the other hand, if you
had a database with up and down transactional volumes, where in some cases you would experience
log switches every 15 minutes and in some cases every two hours, then you might want to set
these parameters (or fast_start_io_target) so that you are getting checkpointed with some
reasonable frequency.

15 minutes seems to be kind of a standard measure for log switches in my experience. So, I'd
agree with Oracle that setting these to 0 is probably reasonable.

HTH.. RF

-----Original Message-----
Sent: Tuesday, May 07, 2002 9:38 AM
To: Multiple recipients of list ORACLE-L

I'm looking to resize our online redo logs for a particular app and am somewhat confused about the appropriate settings. The current config is 4 groups (2 members each) of 5Mb size. We cycle through a daily average of 750 logs in a 22 hour period. During a more intensize daily batch window of 5 hours there is an average of 100 per hour switches i.e. two thirds of switches are occuring during the batch window.

I'm planning to resize the online redo logs to 120Mb based upon rationale of 500Mb per hour in batch window and 4 groups. So switching will happen apporox every 15 minutes. Some Oracle documentation suggests 1 switch per hour is best and others one switch every 30 minutes. So question 1: Does this proposed resize make sense?

Our current log_checkpoint_interval = 10,000 which means a checkpoint at approx 39Mb given it's a Win 2k OS with it's OS block size of 4096. So question 2: Does it make sense to leave this setting as is and have approx 3 checkpoints per each redo (timewise every 5 minutes) or is there a better value and why? (Support suggested setting to zero, i.e. effectively ignore!).

The log_checkpoint_timeout is currently 1800. Support suggested setting this to zero but the reference manual suggests that this should not be done!.
So final question: What would the best value be given the log_checkpoint_interval would probably have a checkpoint every 5 minutes using heavy processing?.

Your considered opinions/input re above would be much appreciated.

Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode]

This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: O'Neill, Sean
INET: Sean.ONeill_at_organon.ie

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Freeman, Robert
INET: Robert_Freeman_at_csx.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------