---orasar.sql listing of System Activity for the past 24 hours
set pages 1000 lines 160
col SNAP_Times format a22
col logons format 99999
col CPU format 99999999
col Execs format 9999999
col MBS_S format 99999
col MBS_R format 99999
col REDOMB format 99999
col PARSE format 999999
col Dsorts format 999999
col eqWaits format 999999
--- Set up title
set termout off serveroutput off heading off timing off
col title_name new_value title_name
select '['||to_char(sysdate,'HH24:MI DD-MON-RR')||' '||INSTANCE_NAME||'@'||HOST_NAME||' '||VERSION||'] ' title_name from v$instance;
set termout on serveroutput on heading on
TTitle Left '### DB System Activity Report (&&hours hrs) - ' title_name skip 2
select to_char(lag(si.SNAP_TIME) over (order by si.dbid, si.instance_number, si.snap_id),'DDMONRR HH24:MI') ||'-'|| to_char(si.SNAP_TIME,'HH24:MI') as SNAP_Times,
st.logons, st.Execs, st.Parse, st.CPU, st.eqWaits, st.DBgets, st.ConGets, st.PhyReads, st.ConChgs, st.PhyWrts, st.Dsorts,
round(st.Redo/(1024*1024)) REDOMB, round(st.BytesS/(1024*1024)) MBS_S, round(st.BytesR/(1024*1024)) MBS_R
from stats$snapshot si,
(select SNAP_ID,
case when logons < lag(logons,1,0) over (order by snap_id) then logons
when lag(logons,1,0) over (order by snap_id) = 0 then 0
else logons - lag(logons,1,0) over (order by snap_id) end as logons,
case when Execs < lag(Execs,1,0) over (order by snap_id) then Execs
when lag(Execs,1,0) over (order by snap_id) = 0 then 0
else Execs - lag(Execs,1,0) over (order by snap_id) end as Execs,
case when Parse < lag(Parse,1,0) over (order by snap_id) then Parse
when lag(Parse,1,0) over (order by snap_id) = 0 then 0
else Parse - lag(Parse,1,0) over (order by snap_id) end as Parse,
case when CPU < lag(CPU,1,0) over (order by snap_id) then CPU
when lag(CPU,1,0) over (order by snap_id) = 0 then 0
else CPU - lag(CPU,1,0) over (order by snap_id) end as CPU,
case when ConGets < lag(ConGets,1,0) over (order by snap_id) then ConGets
when lag(ConGets,1,0) over (order by snap_id) = 0 then 0
else ConGets - lag(ConGets,1,0) over (order by snap_id) end as ConGets,
case when PhyReads < lag(PhyReads,1,0) over (order by snap_id) then PhyReads
when lag(PhyReads,1,0) over (order by snap_id) = 0 then 0
else PhyReads - lag(PhyReads,1,0) over (order by snap_id) end as PhyReads,
case when PhyWrts < lag(PhyWrts,1,0) over (order by snap_id) then PhyWrts
when lag(PhyWrts,1,0) over (order by snap_id) = 0 then 0
else PhyWrts - lag(PhyWrts,1,0) over (order by snap_id) end as PhyWrts,
case when DBgets < lag(DBgets,1,0) over (order by snap_id) then DBgets
when lag(DBgets,1,0) over (order by snap_id) = 0 then 0
else DBgets - lag(DBgets,1,0) over (order by snap_id) end as DBgets,
case when eqWaits < lag(eqWaits,1,0) over (order by snap_id) then eqWaits
when lag(eqWaits,1,0) over (order by snap_id) = 0 then 0
else eqWaits - lag(eqWaits,1,0) over (order by snap_id) end as eqWaits,
case when ConChgs < lag(ConChgs,1,0) over (order by snap_id) then ConChgs
when lag(ConChgs,1,0) over (order by snap_id) = 0 then 0
else ConChgs - lag(ConChgs,1,0) over (order by snap_id) end as ConChgs,
case when dsorts < lag(dsorts,1,0) over (order by snap_id) then dsorts
when lag(dsorts,1,0) over (order by snap_id) = 0 then 0
else dsorts - lag(dsorts,1,0) over (order by snap_id) end as dsorts,
case when Redo < lag(Redo,1,0) over (order by snap_id) then Redo
when lag(Redo,1,0) over (order by snap_id) = 0 then 0
else Redo - lag(Redo,1,0) over (order by snap_id) end as Redo,
case when BytesS < lag(BytesS,1,0) over (order by snap_id) then BytesS
when lag(BytesS,1,0) over (order by snap_id) = 0 then 0
else BytesS - lag(BytesS,1,0) over (order by snap_id) end as BytesS,
case when BytesR < lag(BytesR,1,0) over (order by snap_id) then BytesR
when lag(BytesR,1,0) over (order by snap_id) = 0 then 0
else BytesR - lag(BytesR,1,0) over (order by snap_id) end as BytesR
from
(select SNAP_ID,
max(decode(NAME ,'logons cumulative', value )) logons,
max(decode(NAME ,'CPU used by this session', value )) cpu,
max(decode(NAME ,'consistent gets', value )) ConGets,
max(decode(NAME ,'physical reads', value )) PhyReads,
max(decode(NAME ,'physical writes', value )) PhyWrts,
max(decode(NAME ,'parse count (total)', value )) Parse,
max(decode(NAME ,'execute count', value )) Execs,
max(decode(NAME ,'enqueue waits', value )) eqWaits,
max(decode(NAME ,'db block gets', value )) DBgets,
max(decode(NAME ,'consistent changes', value )) ConChgs,
max(decode(NAME ,'sorts (disk)', value )) dsorts,
max(decode(NAME ,'redo size', value )) Redo,
max(decode(NAME ,'bytes sent via SQL*Net to client', value )) BytesS,
max(decode(NAME ,'bytes received via SQL*Net from client', value )) BytesR
from stats$sysstat
where NAME in ('logons cumulative','CPU used by this session','enqueue waits','db block gets','consistent gets','physical reads',
'consistent changes','physical writes','parse count (total)','execute count','redo size',
'bytes sent via SQL*Net to client','bytes received via SQL*Net from client','sorts (disk)')
group by SNAP_ID)) st
where si.SNAP_ID = st.SNAP_ID
and si.SNAP_TIME > sysdate - &&hours/24
order by si.SNAP_TIME;
TTitle off
Posted in scripts