RE: Checkpoint duration - oracle 10g
- From: "Milen Kulev" <makulev@xxxxxxx>
- To: <spinto@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 19 Apr 2009 20:30:42 +0200
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"
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Savio Pinto (s)
Sent: Sonntag, 19. April 2009 15:53
To: oracle-l@xxxxxxxxxxxxx
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>
Other related posts:
