set pages 1000
set line 1000
set feedback off
set serveroutput on
-- UNDO Check script start
exec dbms_output.put_line('== >>>>>>>>>> UNDO CHECK SCRIPT START <<<<<<<<<< ==');
exec dbms_output.put_line(' ');
-- Check database version
PROMPT Checking database version......
PROMPT ============
select * from v$version;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check if flashback on
PROMPT Checking if flashback on......
PROMPT ============
select flashback_on from v$database;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check the undo parameter
PROMPT Checking the undo parameter......
PROMPT ============
show parameter undo;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check undo tablespace usage
PROMPT Checking undo tablespace usage......
PROMPT ============
SELECT d.status ,
d.tablespace_name ,
d.contents,
d.extent_management,
to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') as total_size_mb,
to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
'99999999.999') used_size_mb,
to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') free_size_mb,
to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
'990.00') as used_percent
FROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND d.contents='UNDO';
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check rollback segment status
PROMPT Checking rollback segment status......
PROMPT ============
select tablespace_name,status,sum(bytes)/1024/1024 mb from DBA_UNDO_EXTENTS group by tablespace_name,status;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
--Check roll segment online status
PROMPT Checking roll segment online status......
PROMPT ============
SELECT
substr(name,1,7) as rollname,
status,count(*) as cnt
FROM v$rollstat, v$rollname
WHERE v$rollstat.usn=v$rollname.usn
group by
substr(name,1,7),status;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check TOP 20 roll segment extend status
PROMPT Checking TOP 20 roll segment extend status......
PROMPT ============
select * from (
SELECT
ds.segment_name "Seq Name",
ds.bytes "Bytes",
ds.blocks "Blocks",
ds.extents "Extents",
ds.initial_extent "Init Ext",
ds.next_extent "Next Ext",
ds.min_extents "Min Ext",
ds.max_extents "Max Ext"
FROM dba_segments ds
WHERE segment_type in ('ROLLBACK','TYPE2 UNDO') order by extents desc) where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check undo header wait
PROMPT Checking Undo Header Waits......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
declare
s_num_rbs number;
s_header_wait number;
s_waits_per_rbs number;
e_num_rbs number;
e_header_wait number;
e_waits_per_rbs number;
delta_num_rbs number;
delta_header_wait number;
delta_waits_per_rbs number;
s_mydate varchar2(200);
e_mydate varchar2(200);
v_pause_secs number;
begin
--Set the pause time for get 2 times change,default is 30 seconds
v_pause_secs:=60;
select to_char(sysdate,'hh24:mi:ss') into s_mydate from dual;
select COUNT(stat.USN) ,wait.Count,round(wait.Count/COUNT(stat.USN),2) into s_num_rbs,s_header_wait,s_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
dbms_lock.sleep(v_pause_secs);
select to_char(sysdate,'hh24:mi:ss') into e_mydate from dual;
select COUNT(stat.USN),wait.Count,round(wait.Count/COUNT(stat.USN),2) into e_num_rbs,e_header_wait,e_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
select (e_num_rbs-s_num_rbs),(e_header_wait-s_header_wait),to_char(round(nvl(decode((e_waits_per_rbs-s_waits_per_rbs),0,null,(e_waits_per_rbs-s_waits_per_rbs)),0),2),'fm999999990.999999999') into delta_num_rbs,delta_header_wait,delta_waits_per_rbs from dual;
dbms_output.put_line(s_mydate||'==> At start time:');
dbms_output.put_line('Number of Rollback segments:'||s_num_rbs||' ,Number of Undo header waits:'||s_header_wait||' ,Number of header wait per Rollback segment:'||s_waits_per_rbs);
dbms_output.put_line(e_mydate||'==> At end time:');
dbms_output.put_line('Number of Rollback segments:'||e_num_rbs||' ,Number of Undo header waits:'||e_header_wait||' ,Number of header wait per Rollback segment:'||e_waits_per_rbs);
dbms_output.put_line('==== Change during '||v_pause_secs||' Seconds:====');
dbms_output.put_line('Delta of Rollback segments:'||delta_num_rbs||' ,Delta of Undo header waits:'||delta_header_wait||' ,Delta of header wait per Rollback segment:'||delta_waits_per_rbs);
end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check latch: undo global data
PROMPT Checking TOP 20 "latch: undo global data"......
PROMPT ============
select * from (
select to_char(end_interval_time,'yyyy-mm-dd hh24:mi'),
nvl(round((c.time_waited_micro - lag(time_waited_micro)
over(order by c.snap_id)) /
decode((c.total_waits - lag(total_waits) over(order by c.snap_id)),0,null,(c.total_waits - lag(total_waits) over(order by c.snap_id)))/1000,2),0) as avg_wait_time_ms
from dba_hist_system_event c, dba_hist_snapshot dd
where event_name = 'latch: undo global data' and c.instance_number=dd.instance_number
and c.snap_id = dd.snap_id and c.instance_number=1
and c.snap_id >= 1 and end_interval_time>=trunc(sysdate-7)
order by 1 desc)
where avg_wait_time_ms<>0 and rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check location of undo latch undo global data
PROMPT Checking location of undo latch undo global data
PROMPT ============
select * from v$latch_misses where SLEEP_COUNT>0 and parent_name like 'undo global data%' order by sleep_count desc;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check undo buffer busy wait in ASH
PROMPT Checking most 20 recently undo buffer busy wait in ASH......
PROMPT ============
select * from (
select to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') as sample_time,
sql_id,event,TOP_LEVEL_SQL_ID,p1 as file_id,p2 as block_id, p3 as reason from v$active_session_history
where event='buffer busy waits'
and p1 in (select file_id from DBA_ROLLBACK_SEGS where segment_name<>'SYSTEM')
and sample_time>=trunc(sysdate-7)
order by sample_time desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check most recent ORA-1555 count
PROMPT Checking most recent ORA-1555 count......
PROMPT ============
select * from (
select to_char(end_time,'yyyy-mm-dd hh24:mi:ss') end_time,
x.unexpiredblks,x.unxpblkrelcnt,x.unxpblkreucnt,
x.expiredblks,x.expblkrelcnt,x.expblkreucnt,
x.ssolderrcnt
from DBA_HIST_UNDOSTAT x
where x.ssolderrcnt>0
order by end_time desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check the status of TOP 20 session which open transaction
PROMPT Checking the status of TOP 20 session which open transaction
PROMPT ============
PROMPT Check the status of TOP 20 session which open transaction......
select * from (
select b.sid,
b.SERIAL#,
b.USERNAME,
b.status as session_status,
a.STATUS as trx_status,
b.MACHINE,
b.sql_id,
a.START_TIME as trx_start_time,
a.USED_UBLK as used_undo_blks,
a.USED_UREC as used_undo_records,
a.START_UBAFIL as used_undo_file_id,
a.START_UBABLK as used_undo_block_id
from v$transaction a, v$session b
where a.ses_addr = b.saddr order by USED_UBLK desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check TOP session current wait event
PROMPT Checking TOP session current wait event
PROMPT ============
select * from (
select b.sid,
b.SERIAL#, event,p1text,p1,p2text, p2
from v$transaction a, v$session b
where a.ses_addr = b.saddr order by USED_UBLK desc)
where rownum<=1;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check TOP session wait event history
PROMPT Checking TOP session wait event history
PROMPT ============
select * from (
select to_char(sample_time,'hh24:mi:ss') as sample_time,sid,serial#,sql_id,event,p1text,p1,p2text,p2 from v$active_session_history c,
(select * from (select b.sid,b.serial# from
v$transaction a, v$session b
where a.ses_addr = b.saddr
order by USED_UBLK desc) where rownum<=1) x
where c.SESSION_ID=x.sid and c.SESSION_SERIAL#=x.serial#
order by sample_time desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check TOP session "db file sequential read" average wait time
PROMPT Checking TOP session "db file sequential read" average wait time......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
declare
v_top_used_ublk_sid number;
v_top_used_ublk_serial# number;
s_chktm varchar2(200);
s_tm_waited_micro number;
s_total_waits number;
s_avg_wait number;
e_chktm varchar2(200);
e_tm_waited_micro number;
e_total_waits number;
e_avg_wait number;
v_delta_avg_wait varchar2(200);
v_pause_secs number;
v_cycle_cnt number;
begin
--Set the pause time for get 2 times change,default is 3 seconds
v_pause_secs:=3;
v_cycle_cnt:=20;
dbms_output.put_line('TOP rolling back session "db file sequential read" avg_wait_time_ms is: ');
select sid,serial# into v_top_used_ublk_sid,v_top_used_ublk_serial# from (select b.sid,b.serial# from v$transaction a, v$session b where a.ses_addr = b.saddr order by USED_UBLK desc) where rownum<=1;
for i in 1 .. v_cycle_cnt loop
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),time_waited_micro,total_waits,a.AVERAGE_WAIT into s_chktm,s_tm_waited_micro,s_total_waits,s_avg_wait from v$session_event a where a.sid=v_top_used_ublk_sid and event='db file sequential read';
dbms_lock.sleep(v_pause_secs);
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),time_waited_micro,total_waits,a.AVERAGE_WAIT into e_chktm,e_tm_waited_micro,e_total_waits,s_avg_wait from v$session_event a where a.sid=v_top_used_ublk_sid and event='db file sequential read';
select to_char(round((e_tm_waited_micro-s_tm_waited_micro)/(e_total_waits-s_total_waits)/1000,2),'fm999999990.999999999') into v_delta_avg_wait from dual;
dbms_output.put_line(e_chktm||': '|| v_delta_avg_wait);
end loop;
end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check system level "db file sequential read" average wait time
PROMPT Checking Check system level "db file sequential read" average wait time......
PROMPT ============
select * from (
select 'SystemLevel_AllDatafile:'||event_name as event_name,to_char(end_interval_time,'yyyy-mm-dd hh24:mi') end_interval_time,
round((c.time_waited_micro - lag(time_waited_micro)
over(order by c.snap_id)) /
decode((c.total_waits - lag(total_waits) over(order by c.snap_id)),0,null,(c.total_waits - lag(total_waits) over(order by c.snap_id)))/1000,2) as avg_wait_time_ms
from dba_hist_system_event c, dba_hist_snapshot dd
where event_name = 'db file sequential read' and c.instance_number=dd.instance_number
and c.snap_id = dd.snap_id and c.instance_number=1
and c.snap_id >= 1 and end_interval_time>=trunc(sysdate-7)
order by 2 desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
-- Check the transaction rollback estimate time
PROMPT Checking the transaction rollback estimate time......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
declare
l_start number;
l_end number;
mydate varchar2(200);
est_time varchar2(200);
v_pause_secs number;
begin
--Set the pause time for get 2 times change,default is 30 seconds
v_pause_secs:=60;
select sum(ktuxesiz) into l_start from x$ktuxe x ,v$transaction b, v$session a where a.taddr=b.addr and x.ktuxeusn=b.XIDUSN and x.ktuxeslt=b.XIDSLOT and ktuxesqn=b.XIDSQN and a.status='KILLED' and b.STATUS='ACTIVE';
dbms_lock.sleep(v_pause_secs);
select sum(ktuxesiz) into l_end from x$ktuxe x ,v$transaction b, v$session a where a.taddr=b.addr and x.ktuxeusn=b.XIDUSN and x.ktuxeslt=b.XIDSLOT and ktuxesqn=b.XIDSQN and a.status='KILLED' and b.STATUS='ACTIVE';
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into mydate from dual;
select
substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),9,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),' ')-9)||'Days '||
substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),12,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),':')-12)||'Hours '||
substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),15,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),':',1,2)-15)||'Mins '||
substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),18,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),'.',1,1)-18)||'Secs'
into est_time
from dual;
dbms_output.put_line(mydate||'==> Base on '||v_pause_secs||'Secs calculated, Estimate remaining trx rollback time is ......:'|| est_time);
end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
exec dbms_output.put_line('== >>>>>>>>>> UNDO CHECK SCRIPT END <<<<<<<<<< ==');