|
|
|
Creative STATSPACK and AWR scripts
Oracle Database Tips by Donald BurlesonSeptember 6, 2015
|
The Oracle STATSPACK and AWR tables can be combined in many ways, and this is a
web survey of the most creating scripts for STATSPACK and AWR. The Oracle
Sponge (Jan) has this query from dba_hist_filestatxs:
select
file#,
PHYRDS-SINGLEBLKRDS MULTIBLKRDS,
PHYBLKRD-SINGLEBLKRDS MULTIBLKBLKRD,
(PHYBLKRD-SINGLEBLKRDS)/
Decode(PHYRDS-SINGLEBLKRDS,0,Null,PHYRDS-SINGLEBLKRDS)
BLKSPERMULTIBLKRD,
READTIM-SINGLEBLKRDTIM MULTIBLKRDTIM,
(PHYRDS-SINGLEBLKRDS)/
Decode(READTIM-SINGLEBLKRDTIM,0,null,READTIM-SINGLEBLKRDTIM)
AVGMULTIBLKRDTIM
from v$filestat
/
select to_char(snap_time,'mm/dd/yyyy hh24:mi:ss') snaptime
, max(decode(event,'db file scattered read', nvl(wait_ms,0), null))
wait_ms_dbfscatrd
, max(decode(event,'db file sequential read',nvl(wait_ms,0), null))
wait_ms_dbfseqrd
, max(decode(event,'db file scattered read', nvl(waits,0), null))
waits_dbfscatrd
, max(decode(event,'db file sequential read',nvl(waits,0), null)) waits_dbfseqrd
from
(
select ps.snap_time
, event
, case
when (total_waits - lag_total_waits > 0)
then round(( (time_waited_micro - lag_time_waited_micro) / (total_waits -
lag_total_waits)) / 1000)
else -1
end wait_ms
, (total_waits - lag_total_waits) waits
, (time_waited_micro - lag_time_waited_micro) time_waited
from (
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file sequential read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) - 1
)
union all
select se.snap_id
, event
, se.total_waits
, se.total_timeouts
, se.time_waited_micro
, lag(se.event) over (order by snap_id, event) lag_event
, lag(se.snap_id) over (order by snap_id, event) lag_snap_id
, lag(se.total_waits) over (order by snap_id, event) lag_total_waits
, lag(se.total_timeouts) over (order by snap_id, event) lag_total_timeouts
, lag(se.time_waited_micro) over (order by snap_id, event) lag_time_waited_micro
from perfstat.stats$system_event se
where event = 'db file scattered read'
and snap_id in (select snap_id from stats$snapshot
where snap_time > trunc(sysdate) -1
)
order by event, snap_id
) a
, perfstat.stats$snapshot ss
, perfstat.stats$snapshot ps
where a.lag_snap_id = ps.snap_id
and a.snap_id = ss.snap_id
and a.lag_total_waits != a.total_waits
and a.event = a.lag_event
order by a.snap_id, event
)
group by snap_time
;
Read deltas:
SELECT s1.ucomment,
w1.event,
s1.snap_id,
w1.total_waits,
LAG(w1.total_waits)
OVER (ORDER BY s1.snap_id) prev_val,
w1.total_waits -
LAG(w1.total_waits)
OVER (ORDER BY s1.snap_id) delta_val
FROM stats$snapshot s1,
stats$system_event w1
WHERE s1.snap_id BETWEEN 313 AND 320
AND s1.snap_id = w1.snap_id
AND w1.event = 'db file sequential read'
ORDER BY w1.event, s1.snap_id;
Comparing two periods:
SELECT sy.snap_id,
sy.statistic# statistic#,
sy.name statname,
sy.value - (LAG(sy.value)
OVER (PARTITION BY sy.name
ORDER BY sy.snap_id)) statdelta
FROM stats$sysstat sy
WHERE sy.snap_id IN (12208,12599,13480,13843)
AND sy.name IN
('consistent gets','consistent changes',
'db block gets', 'db block changes')
ORDER BY sy.name, sy.snap_id;
|