Re: sql writers block
- From: Scott <oraracdba@xxxxxxxxx>
- To: riyaj.shamsudeen@xxxxxxxxx, daniel.fink@xxxxxxxxxxxxxx
- Date: Mon, 9 Feb 2009 17:42:58 -0800 (PST)
Thanks, This sounds bad, but I really had no idea these functions existed. This
information is very useful.
I have found that when troubleshooting large clusters comparing workload
information across nodes is just not available (easily with GC and canned
reports) but it is stored in the dba_hist* tables. So I was trying to fill a
hole that GC and the awr and ash reports lack.
Again thanks for the input,
Scott
________________________________
From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
To: oraracdba@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Monday, February 9, 2009 5:52:21 PM
Subject: Re: sql writers block
Hi Scott
I think, following SQL might be a good start.. I haven't tested this
thoroughly and so, please test it..
with stats as (
select begin_interval_time, instance_number, snap_id,
case when stat_name='DB CPU' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number
,startup_time order by snap_id )
end dbcpu,
case when stat_name='DB time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number
,startup_time order by snap_id )
end dbtime,
case when stat_name='background elapsed time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number
,startup_time order by snap_id )
end dbbgela,
case when stat_name='background cpu time' then
value - lag(value,1,0) over( partition by stat_id, dbid, instance_number
,startup_time order by snap_id )
end dbbgcpu
from
(
select h.begin_interval_time, s.snap_id, s.dbid, s.instance_number, s.stat_id,
s.stat_name, value, startup_time
FROM DBA_HIST_SYS_TIME_MODEL s , DBA_HIST_SNAPSHOT h
where
s.instance_number = h.instance_number and
s.snap_id = h.snap_id and
s.dbid = h.dbid and
s.stat_name in ('DB time','DB CPU','background elapsed time','background cpu
time') and
h.begin_interval_time > sysdate -1
order by stat_id, instance_number,snap_id
)
)
select begin_interval_time, instance_number, max(dbcpu),
max(dbtime),max(dbbgela),max(dbbgcpu)
from stats
group by begin_interval_time, instance_number
order by instance_number, begin_interval_time
/
--
Cheers
Riyaj Shamsudeen
Principal DBA,
Ora!nternals - http://www.orainternals.com
Specialists in Performance, Recovery and EBS11i
Blog: http://orainternals.wordpress.com
On Mon, Feb 9, 2009 at 3:32 PM, Scott <oraracdba@xxxxxxxxx> wrote:
Either this can't be done in SQL, If it can then I am missing something. The
goal is break this information based on snap_id between a given time. This
probably can be done with lots of inline sql but I want to keep this as simple
as possible.
Thanks in advance
Scott
select b.snap_id
,sum(case when e.stat_name = 'DB time'
then e.value - b.value
else 0
end) tdbtim
, sum(case when e.stat_name = 'DB CPU'
then e.value - b.value
else 0
end) tdbcpu
, sum(case when e.stat_name = 'background elapsed time'
then e.value - b.value
else 0
end) tbgtim
, sum(case when e.stat_name = 'background cpu time'
then e.value - b.value
else 0
end) tbgcpu
from dba_hist_sys_time_model b
, dba_hist_sys_time_model e
where e.instance_number = b.instance_number
and b.snap_id = (select max(snap_id)
from dba_hist_snapshot
where
END_INTERVAL_TIME between to_date(:BDATE,'yyyymmddhh24mi')-(1/24)
and
to_date(:BDATE,'yyyymmddhh24mi'))
and e.snap_id = (select max(snap_id)
from dba_hist_snapshot
where
END_INTERVAL_TIME between to_date(:EDATE,'yyyymmddhh24mi')-(1/24)
and
to_date(:EDATE,'yyyymmddhh24mi'))
and b.stat_id = e.stat_id
and e.stat_name in ('DB time','DB CPU'
,'background elapsed time','background cpu time')
group by b.snap_id
--
//www.freelists.org/webpage/oracle-l
Other related posts:
