Featured

Top Scripts – Performance Monitoring

This is the post excerpt.

This is a script to view database instance activity in terms of events (including CPU), sqls (sql_id), modules as reported in the gv$active_session_history table. Shows, in a separate report for each, a pivot of activity for events, modules, sql_ids over the lats 15 minutes – one column for eahc minute. This is similar to the ASH chart in OEM (I think, I dont use OEM much) – useful if you work more on the command line. More drill down scripts possible for individual modules, sqls, events.

rtop.sql

clear breaks
set pages 2000 lines 2000
set timing off feed off
column w format a29 heading “Process State”
col module for a29 trunc
column avrg format 9999.99 heading “Average|Count”
column t_5 format 999.9
column t_10 format 999.9
column t_15 format 999.9
column t_20 format 999.9
column t_25 format 999.9
column t_30 format 999.9
column t_35 format 999.9
column t_40 format 999.9
column t_45 format 999.9
column t_50 format 999.9
column t_55 format 999.9
column t_60 format 999.9
column t_65 format 999.9
column t_70 format 999.9
column t_75 format 999.9
col inst_id for 99 head inst

prompt Process States in last 75 minutes
set head off
select ‘Process States in previous 75 minutes – ‘ || to_char(sysdate, ‘mm/dd hh24:mi:ss’) from dual;
set head on

select * from (
select * from (
select inst_id, w
,round(sum(cnt)/75,2) avrg
,round(sum(decode(floor(mins_hist/5)+1, 1,cnt,null)),2) t_5
,round(sum(decode(floor(mins_hist/5)+1, 2,cnt,null)),2) t_10
,round(sum(decode(floor(mins_hist/5)+1, 3,cnt,null)),2) t_15
,round(sum(decode(floor(mins_hist/5)+1, 4,cnt,null)),2) t_20
,round(sum(decode(floor(mins_hist/5)+1, 5,cnt,null)),2) t_25
,round(sum(decode(floor(mins_hist/5)+1, 6,cnt,null)),2) t_30
,round(sum(decode(floor(mins_hist/5)+1, 7,cnt,null)),2) t_35
,round(sum(decode(floor(mins_hist/5)+1, 8,cnt,null)),2) t_40
,round(sum(decode(floor(mins_hist/5)+1, 9,cnt,null)),2) t_45
,round(sum(decode(floor(mins_hist/5)+1,10,cnt,null)),2) t_50
,round(sum(decode(floor(mins_hist/5)+1,11,cnt,null)),2) t_55
,round(sum(decode(floor(mins_hist/5)+1,12,cnt,null)),2) t_60
,round(sum(decode(floor(mins_hist/5)+1,13,cnt,null)),2) t_65
,round(sum(decode(floor(mins_hist/5)+1,14,cnt,null)),2) t_70
,round(sum(decode(floor(mins_hist/5)+1,15,cnt,null)),2) t_75
from (
select inst_id, mins_hist, w, count(*) / 300 cnt
from
(select inst_id, sysdate sdt,
trunc(( sysdate – to_date(to_char(sample_time,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)) * 24 * 60) mins_hist,
–trunc((sysdate – sample_time) * 24 * 60) mins_hist,
case when in_hard_parse = ‘Y’ then ‘hard parse’
when in_connection_mgmt = ‘Y’ then ‘conn mgmt’
when IN_SQL_EXECUTION = ‘Y’ then ‘sql exec’
when IN_PLSQL_EXECUTION = ‘Y’ then ‘plsql exec’
when IN_SEQUENCE_LOAD = ‘Y’ then ‘seq Load’
end as w
–decode(session_state, ‘ON CPU’, ‘CPU’, event) w
from gv$active_session_history a
where sample_time >= sysdate – 1.25/24
–and sample_time <= to_date(to_char(sysdate,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)
) a
group by inst_id, mins_hist, w
)
group by inst_id, w
order by inst_id, nvl(avrg,0) desc
)
–where avrg > 0.001
order by avrg desc
) where rownum <= 25
/

select * from (
select * from (
select inst_id, w
,round(sum(cnt)/75,2) avrg
,round(sum(decode(floor(mins_hist/5)+1, 1,cnt,null)),2) t_5
,round(sum(decode(floor(mins_hist/5)+1, 2,cnt,null)),2) t_10
,round(sum(decode(floor(mins_hist/5)+1, 3,cnt,null)),2) t_15
,round(sum(decode(floor(mins_hist/5)+1, 4,cnt,null)),2) t_20
,round(sum(decode(floor(mins_hist/5)+1, 5,cnt,null)),2) t_25
,round(sum(decode(floor(mins_hist/5)+1, 6,cnt,null)),2) t_30
,round(sum(decode(floor(mins_hist/5)+1, 7,cnt,null)),2) t_35
,round(sum(decode(floor(mins_hist/5)+1, 8,cnt,null)),2) t_40
,round(sum(decode(floor(mins_hist/5)+1, 9,cnt,null)),2) t_45
,round(sum(decode(floor(mins_hist/5)+1,10,cnt,null)),2) t_50
,round(sum(decode(floor(mins_hist/5)+1,11,cnt,null)),2) t_55
,round(sum(decode(floor(mins_hist/5)+1,12,cnt,null)),2) t_60
,round(sum(decode(floor(mins_hist/5)+1,13,cnt,null)),2) t_65
,round(sum(decode(floor(mins_hist/5)+1,14,cnt,null)),2) t_70
,round(sum(decode(floor(mins_hist/5)+1,15,cnt,null)),2) t_75
from (
select inst_id, mins_hist, w, count(*) / 300 cnt
from
(select inst_id, sysdate sdt,
trunc(( sysdate – to_date(to_char(sample_time,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)) * 24 * 60) mins_hist,
–trunc((sysdate – sample_time) * 24 * 60) mins_hist,
decode(session_state, ‘ON CPU’, ‘CPU’, event) w
from gv$active_session_history a
where sample_time >= sysdate – 1.25/24
–and sample_time <= to_date(to_char(sysdate,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)
) a
group by inst_id, mins_hist, w
)
group by inst_id, w
order by inst_id, nvl(avrg,0) desc
)
–where avrg > 0.001
order by avrg desc
) where rownum <= 25
/

–order by inst_id, avrg desc

select * from (
select inst_id, sql_id
,round(sum(cnt)/75,2) avrg
,round(sum(decode(floor(mins_hist/5)+1, 1,cnt,null)),2) t_5
,round(sum(decode(floor(mins_hist/5)+1, 2,cnt,null)),2) t_10
,round(sum(decode(floor(mins_hist/5)+1, 3,cnt,null)),2) t_15
,round(sum(decode(floor(mins_hist/5)+1, 4,cnt,null)),2) t_20
,round(sum(decode(floor(mins_hist/5)+1, 5,cnt,null)),2) t_25
,round(sum(decode(floor(mins_hist/5)+1, 6,cnt,null)),2) t_30
,round(sum(decode(floor(mins_hist/5)+1, 7,cnt,null)),2) t_35
,round(sum(decode(floor(mins_hist/5)+1, 8,cnt,null)),2) t_40
,round(sum(decode(floor(mins_hist/5)+1, 9,cnt,null)),2) t_45
,round(sum(decode(floor(mins_hist/5)+1,10,cnt,null)),2) t_50
,round(sum(decode(floor(mins_hist/5)+1,11,cnt,null)),2) t_55
,round(sum(decode(floor(mins_hist/5)+1,12,cnt,null)),2) t_60
,round(sum(decode(floor(mins_hist/5)+1,13,cnt,null)),2) t_65
,round(sum(decode(floor(mins_hist/5)+1,14,cnt,null)),2) t_70
,round(sum(decode(floor(mins_hist/5)+1,15,cnt,null)),2) t_75
from (
select inst_id, mins_hist, sql_id, count(*) / 300 cnt
from
(select inst_id, sysdate sdt,
trunc(( sysdate – to_date(to_char(sample_time,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)) * 24 * 60) mins_hist,
–trunc((sysdate – sample_time) * 24 * 60) mins_hist,
sql_id
from gv$active_session_history a
where sample_time >= sysdate – 1.25/24
–and sample_time <= to_date(to_char(sysdate,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)
) a
group by inst_id, mins_hist, sql_id
)
group by inst_id, sql_id
order by nvl(avrg,0) desc
)
where avrg > 0
and rownum <= 25
/

select * from (
select inst_id, module
,round(sum(cnt)/75,2) avrg
,round(sum(decode(floor(mins_hist/5)+1, 1,cnt,null)),2) t_5
,round(sum(decode(floor(mins_hist/5)+1, 2,cnt,null)),2) t_10
,round(sum(decode(floor(mins_hist/5)+1, 3,cnt,null)),2) t_15
,round(sum(decode(floor(mins_hist/5)+1, 4,cnt,null)),2) t_20
,round(sum(decode(floor(mins_hist/5)+1, 5,cnt,null)),2) t_25
,round(sum(decode(floor(mins_hist/5)+1, 6,cnt,null)),2) t_30
,round(sum(decode(floor(mins_hist/5)+1, 7,cnt,null)),2) t_35
,round(sum(decode(floor(mins_hist/5)+1, 8,cnt,null)),2) t_40
,round(sum(decode(floor(mins_hist/5)+1, 9,cnt,null)),2) t_45
,round(sum(decode(floor(mins_hist/5)+1,10,cnt,null)),2) t_50
,round(sum(decode(floor(mins_hist/5)+1,11,cnt,null)),2) t_55
,round(sum(decode(floor(mins_hist/5)+1,12,cnt,null)),2) t_60
,round(sum(decode(floor(mins_hist/5)+1,13,cnt,null)),2) t_65
,round(sum(decode(floor(mins_hist/5)+1,14,cnt,null)),2) t_70
,round(sum(decode(floor(mins_hist/5)+1,15,cnt,null)),2) t_75
from (
select inst_id, mins_hist, module, count(*) / 300 cnt
from
(select inst_id, sysdate sdt,
trunc(( sysdate – to_date(to_char(sample_time,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)) * 24 * 60) mins_hist,
–trunc((sysdate – sample_time) * 24 * 60) mins_hist,
nvl(module, program) as module
from gv$active_session_history a
where sample_time >= sysdate – 1.25/24
–and sample_time <= to_date(to_char(sysdate,’YYYYMMDDHH24MI’),’YYYYMMDDHH24MI’)
) a
group by inst_id, mins_hist, module
)
group by inst_id, module
order by nvl(avrg,0) desc
)
where avrg > 0
and rownum <= 25
/

set lines 166 pages 45