RE: Checkpoint duration - oracle 10g

How about you keep watching 'DBWR checkpoint buffers written' statistic and correlate that with what you see in alert.log? I can't think of a better way. You can automate it with code like this:

tail -f alert.log | perl -nl 'check_stat.pl'

where check_stat.pl, upon matching a checkpoint string pattern, logs into the database and records the statistic. (I wish we could create a trigger on an external table!)

Yong Huang

• On Mon, 4/20/09, Savio Pinto (s) <spinto_at_cap.org> wrote:

> From: Savio Pinto (s) <spinto_at_cap.org>
> Subject: RE: Checkpoint duration - oracle 10g
> To: yong321_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> Date: Monday, April 20, 2009, 4:40 PM
>
> Thanks Yong. in the alert log it logs SCN # for the checkpoint, is there
> a way to get the total # of buffer blocks that were written for the SCN?
>
> -----Original Message-----
> From: Yong Huang [mailto:yong321_at_yahoo.com]
> Sent: Monday, April 20, 2009 3:54 PM
> To: Savio Pinto (s)
> Cc: oracle-l_at_freelists.org
> Subject: Re: Checkpoint duration - oracle 10g
>
>
> > I need to find out how long it takes for the database to flush the dirty
> > buffers to the disk, is there a way I can find the total number of
> > buffers that are written to the disk during the checkpoint operation,
> > and how long does it take for the database to write it to the disk
> > (checkpoint duration) ?
>
> There're a couple of statistics about checkpoint buffers written so you
> can check v$sysstat to get that number. I don't think there's a way to
> find how long a checkpoint takes unless you watch your clock while you
> do checkpoint. But there're many types of checkpoint. For log switch
> checkpoint, you may get two timestamps in alert.log if you alter system
> set log_checkpoints_to_alert = true and watch the lines "Beginning log
> switch checkpoint up to RBA" and "Completed checkpoint up to RBA". Even
> that is not guaranteed; you may still get only one timestamp so you
> don't know the duration.
>
> Yong Huang
      

--
http://www.freelists.org/webpage/oracle-l