Re: redo size and log_checkpoint_interval

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_at_com        To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>  
                    net.ca>              cc:                                           
                       
                    Sent by:             Subject:     redo size  and 
log_checkpoint_interval                  
                    root_at_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: mitchell_at_comnet.ca

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: 
  INET: Riyaj_Shamsudeen_at_i2.com

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