oracle升级监控脚本

---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