RE: Checkpoint duration - oracle 10g

the query below should give you the number of checkpoint buffers written in each AWR period. Is this what you need (this was obe of your questions) ?

HTH. Milen  

set lin 200 pagesize 40

col snap_id for 9999 head "SNAP|ID"

col inst_num for 9 head "I"

col Startup for a7 head "DB|STARTUP"

col begin_time for a20 head "PERIOD|START"

col period_sec for 9999 head "PERIOD|[sec]"

col delta_value for 999,999 head "CHECKP|BUFF|WRITTEN"  

select

snap_id,

inst_num,

CASE WHEN begin_time = startup_time then 'Y' else NULL end as Startup,

begin_time,

round((end_time - begin_time)*24*60*60,0) as period_sec,

CASE WHEN begin_time = startup_time

            THEN value

            ELSE             value - LAG(value,1) OVER

            (PARTITION BY inst_num, startup_time ORDER BY snap_id ASC)

            END as delta_value

from

(

    select

    SN.snap_id                         as snap_id,

    SN.instance_number                 as inst_num,

    ROUND(SN.startup_time,'MI')        as startup_time,

    ROUND(SN.begin_interval_time,'MI') as begin_time,

    ROUND(SN.end_interval_time,'MI') as end_time,

    ss.value as value

    from

    DBA_HIST_SYSSTAT ss ,

    dba_hist_snapshot sn

    where

    ss.snap_id = sn.snap_id AND

    ss.stat_name = 'DBWR checkpoint buffers written'

)

;  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Savio Pinto (s)
Sent: Sonntag, 19. April 2009 15:53
To: oracle-l_at_freelists.org
Subject: Checkpoint duration - oracle 10g  

Hi,  

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

Thanks for the help

Savio Pinto  

  <http://www.cap.org/apps/docs/images/emailGreenLogo.gif>

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