RE: Checkpoint duration - oracle 10g

From: Milen Kulev <>
Date: Sun, 19 Apr 2009 20:30:42 +0200
Message-ID: <001601c9c11c$f3958fc0$dac0af40$_at_net>

Hello Salvio,

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"  




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


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




    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



    dba_hist_snapshot sn


    ss.snap_id = sn.snap_id AND

    ss.stat_name = 'DBWR checkpoint buffers written'



From: [] On Behalf Of Savio Pinto (s)
Sent: Sonntag, 19. April 2009 15:53
Subject: 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) ?  

Thanks for the help

Savio Pinto  


Received on Sun Apr 19 2009 - 13:30:42 CDT

Original text of this message