Re: Experiencing again apperant hang very slow

From: David Barbour <david.barbour1_at_gmail.com>
Date: Mon, 15 Dec 2008 17:35:23 -0500
Message-ID: <69eafc3f0812151435l12474bcod28cb31b619e8d8e@mail.gmail.com>


log_checkpoint_interval really does look to be too small. According to the 9i Database Reference:

*LOG_CHECKPOINT_INTERVAL<http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1101.htm#REFRN10095> *specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.

Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. Therefore, if the value exceeds the actual redo log file size, checkpoints occur only when switching logs. Checkpoint frequency is one of the factors that influence the time required for the database to recover from an unexpected failure. That being said, if you've set up your redo logs with 512k blocks, you're going to be checkpointing every 1MB.

Either set it to 0 (zero) or some big number. You'll checkpoint at a log switch. If you're worried about recovery, use either your FAST_START_MTTR_TARGET or run a log switch every x minutes via cron or dbms_job. You can check v$instance_recovery to get additional information.

On Mon, Dec 15, 2008 at 9:46 AM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

>
> Hello Suboth
>
> Can you please print top wait events from statspack report? (or) send
> a report to me please.
>
> My comments about these changes below:
>
> >> log_checkpoints_to_alert TRUE => this is what i changed to false
> You should keep this to True. This provides valuable information about
> checkpoint progress details in alert log.
>
> >>log_checkpoint_interval 2048
> This will require an incremental checkpoint after 2048 log blocks or so. In
> HP, this will be 2MB and other platforms 1MB. So, yes, you might want to
> remove or increase this parameter, if excessive checkpoint has been
> established as a bottleneck.
>
> >> log_checkpoint_timeout 0 => I think this will not have now any effect as
> only integers should be considered..
> default is zero. Not sure why this is an issue.
>
> >>and should set FAST_START_MTTR_TARGET to say 600
> It sounds like recommendation is to eliminate manual control of checkpoint
> and let software decide how often to checkpoint based upon MTTR target. Not
> a bad idea, but then again, we have not established that bottleneck is due
> to excessive checkpointing. Can you please post symptoms of the problems and
> post statspack report details?
>
> --
> Cheers
>
> Riyaj Shamsudeen
> Principal DBA,
> Ora!nternals - http://www.orainternals.com
> Specialists in Performance, Recovery and EBS11i
> Blog: http://orainternals.wordpress.com
>
>
>
>
> On Mon, Dec 15, 2008 at 6:04 AM, Subodh Deshpande <
> subodh_deshpande_at_yahoo.com> wrote:
>
>> hi all,
>>
>> this is a 9.2.0.8 rac two node db.
>> we have increased the size of redo from 33 mb to 100mb as per
>> recommendations of expert and after which we are facing the problmes of
>> waits..it seems that db is frequently checkpointing, now have been given the
>> recommendation that should remove the follwing parameters
>> log_checkpoints_to_alert TRUE => this is what i changed to false
>> log_checkpoint_interval 2048
>> log_checkpoint_timeout 0 => i think this will not have now any effect as
>> only integers should be considered..
>> and should set FAST_START_MTTR_TARGET to say 600
>>
>> can somebody suggest me what are the default values, especially of
>> log_checkpoint_interval 2048
>>
>> thanks
>> subodh
>>
>>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 15 2008 - 16:35:23 CST

Original text of this message