---CDB CPU/time
with t as
(select max(t.snap_id) over() max_snap_id,
t.snap_id,
t.stat_name,
((max(value)
over(partition by t.stat_name order by t.snap_id
rows BETWEEN unbounded preceding AND unbounded following)) -
(min(value)
over(partition by t.stat_name order by t.snap_id
rows BETWEEN unbounded preceding AND unbounded following))) value
from dba_hist_sys_time_model t, dba_hist_snapshot snap
where t.stat_name in ('DB CPU', 'DB time')
and t.snap_id = snap.snap_id
and snap.end_interval_time between
to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and
to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')
)
select t.stat_name,
decode(t.stat_name,
'DB CPU',
round(t.value / 1000000 / 60, 2),
'DB time',
round(t.value / 1000000 / 60, 2),
0) time
from t
where t.snap_id = t.max_snap_id;
--Cbuffer hit
with r as
(select max(s.snap_id) over() max_snap_id,
s.stat_name,
s.snap_id,
((max(value)
over(partition by s.stat_name order by s.snap_id
rows BETWEEN unbounded preceding AND unbounded following)) -
(min(value)
over(partition by s.stat_name order by s.snap_id
rows BETWEEN unbounded preceding AND unbounded following))) value
from dba_hist_sysstat s, dba_hist_snapshot snap
where s.snap_id = snap.snap_id
and s.stat_name in
('physical reads direct', 'physical reads', 'session logical reads')
and snap.end_interval_time between
to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and
to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')),
rr as
(select max(decode(r.stat_name, 'session logical reads', r.value, 0)) lr,
max(decode(r.stat_name, 'physical reads', r.value, 0)) pr,
max(decode(r.stat_name, 'physical reads direct', r.value, 0)) prd
from r
where r.snap_id = r.max_snap_id)
select round(rr.pr / 3600, 2) physical_reads_ps,
round((1 - (rr.pr - rr.prd) / rr.lr) * 100, 2) buffer_hit
from rr;
--event1
select
max(sum(nvl(case
when event = 'log file sync' then
round(times, 2)
end,
0))) as "log file sync",
max(sum(nvl(case
when event = 'log file parallel write' then
round(times, 2)
end,
0))) as "log file parallel write",
max(sum(nvl(case
when event = 'db file sequential read' then
round(times, 2)
end,
0))) as "db file sequential read",
max(sum(nvl(case
when event = 'db file parallel write' then
round(times, 2)
end,
0))) as "db file parallel write",
max(sum(nvl(case
when event = 'direct path read' then
round(times, 2)
end,
0))) as "direct path read"
from (select t.snap_id,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,
event_name event,
decode(sign(total_waits - lag(total_waits, 1)
over(partition by event_name order by t.snap_id)),
-1,
(lag(time_waited_micro, 1)
over(partition by event_name order by t.snap_id)) /
(lag(total_waits, 1)
over(partition by t.instance_number,
event_name order by t.snap_id)),
0,
0,
(time_waited_micro - lag(time_waited_micro, 1)
over(partition by event_name order by t.snap_id)) /
(total_waits - lag(total_waits, 1)
over(partition by event_name order by t.snap_id))) / 1000 as times
from dba_hist_system_event t, dba_hist_snapshot b
where t.snap_id = b.snap_id
and b.begin_interval_time between
to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and
to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')
and b.instance_number = t.instance_number
and event_name in ('db file sequential read',
'log file sync',
'direct path read',
'log file parallel write',
'db file parallel write')
and t.instance_number = 1)
where times > 0
group by snap_id, stime
order by snap_id;
---event2 for latch and enq
select event,times from (
select event,round(avg(times),2) times from
(
select t.snap_id,
to_char(b.begin_interval_time, 'yyyy-mm-dd hh24:mi:ss') stime,
event_name event,
decode(sign(total_waits - lag(total_waits, 1)
over(partition by event_name order by t.snap_id)),
-1,
(lag(time_waited_micro, 1)
over(partition by event_name order by t.snap_id)) /
(lag(total_waits, 1)
over(partition by t.instance_number,
event_name order by t.snap_id)),
0,
0,
(time_waited_micro - lag(time_waited_micro, 1)
over(partition by event_name order by t.snap_id)) /
(total_waits - lag(total_waits, 1)
over(partition by event_name order by t.snap_id))) / 1000 as times
from dba_hist_system_event t, dba_hist_snapshot b
where t.snap_id = b.snap_id
and b.begin_interval_time between
to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and
to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')
and b.instance_number = t.instance_number
and (event_name like'%enq%' or event_name like'%latch%')
and t.instance_number = 1 )
where times is not null
and times <>0
group by event
order by 2 desc) where times<>0;
---maximum PGA allocated
select ROUND(max(value) / 1024 / 1024 / 1024, 2) PGA_G
from dba_hist_pgastat p, dba_hist_snapshot s
where name = 'maximum PGA allocated'
and s.end_interval_time between
to_date('&begin_time', 'YYYY-MM-DD HH24:MI:SS') and
to_date('&end_time', 'YYYY-MM-DD HH24:MI:SS')
and p.snap_id = s.snap_id;
---fra
select * from v$flash_recovery_area_usage;
--undostat
select to_char(begin_time, 'yyyymmdd hh24'),
sum(trunc(undoblks * 8192 / 1024 / 1024))
from v$undostat
where begin_time between trunc(sysdate - 1 / 24, 'hh24') and
trunc(sysdate, 'hh24')
group by to_char(begin_time, 'yyyymmdd hh24');
---hard parse
with t1 as
(
select s.instance_number,s.snap_id,to_char(t.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') time,stat_name,value from dba_hist_sysstat s ,dba_hist_snapshot t
where
s.instance_number=1
and s.stat_name='parse count (hard)'
and s.snap_id=t.snap_id and t.instance_number=1
order by 1 desc
),
t2 as
(
select s.instance_number,s.snap_id,to_char(t.begin_interval_time,'yyyy/mm/dd hh24:mi:ss') time,stat_name,value from dba_hist_sysstat s ,dba_hist_snapshot t
where
s.instance_number=1
and s.stat_name='parse count (hard)'
and s.snap_id=t.snap_id and t.instance_number=1
order by 1 desc
)
select t1.instance_number,t1.snap_id,t1.time,t1.stat_name,(t1.value-t2.value) value from t1,t2 where t1.snap_id=t2.snap_id+1
order by t1.snap_id desc
oracle升级监控脚本
最新推荐文章于 2023-02-07 14:16:14 发布