Primary reason not to switch more often then 30 minutes to 1hour, is to
reduce the checkpointing frequency. If you switch every 3 to 4 minutes,
then waits for checkpoint related activity will be higher. Hence you may
want to increase the log file size and also set your
log_checkpoint_interval, and timeout to more than 30 minutes / log file
size, so that checkpoint will occur only during the log switches.
Checkpointing has changed slightly from Oracle8 onwards. In Oracle 8, the
SGA has a checkpoint queue which is an ordered list of buffers by their low
RBA. So DBW is continually checkpointing and writing the buffers from the
checkpoint queue and the ckpt process keeps a heartbeat about this
checkpoint progress in the control file. (check out x$kcccp table). So, the
effect of checkpoints in newer versions of Oracle is not quite dramatic as
previous versions. Due to this continuous checkpointing activity, the
thread recovery time is also minimized. But still it is better to keep the
log switching frequency at 30 minutes to 1 hour since there are other
overhead associated with log switches.
So, I would increase the log file size and disable the
log_checkpoint_interval and log_checkpoint_timeout parameters by setting
them to an high value.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com
"Mitchell"
<mitchell@com To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
net.ca> cc:
Sent by: Subject: redo size and
log_checkpoint_interval
root@fatcity.
com
04/30/01
04:10 PM
Please
respond to
ORACLE-L
Dear DBA
I have 130 gb database running on 7/24 basis. redo logfile size is 40mb and
oracle create about most to 400 archivive log. that mean log switch happend
at 3-4 min at peak time.
As oracle suggest, it is better log switch between 30min. (Why, if so that
need more recovery time?)
If I will increase the size to 100mb and make time around 30 min. How
about
Log_checkpooint_interval. My currently setting is 2097152 (20mb).
log_checkpoint_timeout = 0
As my understand, the interval based on volume. If setting is 100mb, mean 5
check point will occured at 20mb interval. I am right?
Any suggest?
Mitchell
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mitchell
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
