Oracle的进程，如果在os层面kill -9了，那么在v$transaction中是看不到的，需要在v$fast_start_transactions中去看。可以用下面2种方法预估回滚时间：
方法一：（利用v$fast_start_transaction）
set pages 1000
set line 1000
set feedback off
set serveroutput on
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 300 seconds
v_pause_secs:=600;
select sum(undoblockstotal-undoblocksdone) into l_start from v$fast_start_transactions where state='RECOVERING';
dbms_lock.sleep(v_pause_secs);
select sum(undoblockstotal-undoblocksdone) into l_end from v$fast_start_transactions where state='RECOVERING';
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;
/
方法二：（利用x$ktuxe）
set pages 1000
set line 1000
set feedback off
set serveroutput on
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:=600;
select sum(ktuxesiz) into l_start from x$ktuxe where KTUXECFL ='DEAD';
dbms_lock.sleep(v_pause_secs);
select sum(ktuxesiz) into l_end from x$ktuxe where KTUXECFL ='DEAD';
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;
/
Oralce undo健康检查脚本（这个脚本是基于进程没在os层面kill -9的情况下）:
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 all roll segment online or offline status
PROMPT Checking Check all roll segment online or offline status......
PROMPT ============
select substr(segment_name,1,7) as rollname,status,count(*) from dba_rollback_segs
group by substr(segment_name,1,7),status
order by 1;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');
--Check online roll segment 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 order by 1;
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 varchar2(200);
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),4) 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),4) 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),4),'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') as end_interval_time,
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_ms0 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 <<<<<<<<<< ==');
结果输出样例：
== >>>>>>>>>> UNDO CHECK SCRIPT START <<<<<<<<<< ==
Checking database version......
============
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Checking if flashback on......
============
FLASHBACK_ON
------------------
NO
Checking the undo parameter......
============
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change string 11.2.0.3
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string MYUR_UNDOTS1
Checking undo tablespace usage......
============
STATUS TABLESPACE_NAME CONTENTS EXTENT_MANAGEMENT TOTAL_SIZE_MB USED_SIZE_MB FREE_SIZE_MB USED_PERCENT
--------- ------------------------------ --------- ----------------- ------------- ------------- ------------- ------------
ONLINE MYUR_UNDOTS1 UNDO LOCAL 377855.922 289643.172 88212.750 76.65
Checking rollback segment status......
============
TABLESPACE_NAME STATUS MB
------------------------------ --------- ----------
MYUR_UNDOTS1 ACTIVE 141022.687
MYUR_UNDOTS1 UNEXPIRED 141347.375
MYUR_UNDOTS1 EXPIRED 7261.9375
Checking Check all roll segment online or offline status......
============
ROLLNAME STATUS COUNT(*)
---------------------------- ---------------- ----------
SYSTEM ONLINE 1
_SYSSMU OFFLINE 111
_SYSSMU ONLINE 193
Checking roll segment online status......
============
ROLLNAME STATUS CNT
---------------------------- --------------- ----------
SYSTEM ONLINE 1
_SYSSMU ONLINE 193
Checking TOP 20 roll segment extend status......
============
Seq Name Bytes Blocks Extents Init Ext Next Ext Min Ext Max Ext
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
_SYSSMU97_1452782954$ 1461008465 17834576 13002 131072 65536 2 32765
_SYSSMU122_2745965143$ 4810997760 587280 225 131072 65536 2 32765
_SYSSMU148_3535832903$ 3428974592 418576 209 131072 65536 2 32765
_SYSSMU111_2840967137$ 2153906176 262928 203 131072 65536 2 32765
_SYSSMU151_2337694042$ 2728525824 333072 202 131072 65536 2 32765
_SYSSMU34_1398498963$ 3234988032 394896 194 131072 65536 2 32765
_SYSSMU130_2801025066$ 3738959872 456416 191 131072 65536 2 32765
_SYSSMU27_1232623801$ 1709309952 208656 188 131072 65536 2 32765
_SYSSMU86_2060358707$ 1989279744 242832 183 131072 65536 2 32765
_SYSSMU132_3132320204$ 2629959680 321040 181 131072 65536 2 32765
_SYSSMU229_966176448$ 1388445696 169488 177 131072 65536 2 32765
_SYSSMU95_2146542047$ 2875326464 350992 170 131072 65536 2 32765
_SYSSMU182_2198377807$ 1901199360 232080 170 131072 65536 2 32765
_SYSSMU168_3933786344$ 2468478976 301328 169 131072 65536 2 32765
_SYSSMU36_1398498981$ 2364669952 288656 165 131072 65536 2 32765
_SYSSMU71_1398526988$ 2231500800 272400 164 131072 65536 2 32765
_SYSSMU68_1398526988$ 1105330176 134928 160 131072 65536 2 32765
_SYSSMU128_2100380268$ 2093088768 255504 158 131072 65536 2 32765
_SYSSMU77_1398526988$ 1848770560 225680 156 131072 65536 2 32765
_SYSSMU5_1398481724$ 1836187648 224144 153 131072 65536 2 32765
Checking Undo Header Waits......
(Note:Need to wait 60Secs)
============
15:49:48==> At start time:
Number of Rollback segments:193 ,Number of Undo header waits:3002541 ,Number of header wait per Rollback segment:15557.2073
15:50:48==> At end time:
Number of Rollback segments:193 ,Number of Undo header waits:3002552 ,Number of header wait per Rollback segment:15557.2642
==== Change during 60 Seconds:====
Delta of Rollback segments:0 ,Delta of Undo header waits:11 ,Delta of header wait per Rollback segment:0.0569
Checking TOP 20 "latch: undo global data"......
============
END_INTERVAL_TIME AVG_WAIT_TIME_MS
------------------------------ ----------------
2021-01-05 15:45 0.07
2021-01-05 15:15 0.07
2021-01-05 15:00 0.09
2021-01-05 14:30 0.09
2021-01-05 14:15 0.12
2021-01-05 14:01 0.09
2021-01-05 13:45 0.14
2021-01-05 13:30 0.14
2021-01-05 12:15 0.16
2021-01-05 12:00 0.01
2021-01-05 11:45 0.01
2021-01-05 11:16 0.1
2021-01-05 10:45 0.12
2021-01-05 10:15 0.08
2021-01-05 09:15 0.1
2021-01-05 09:00 0.09
2021-01-05 08:45 0.07
2021-01-05 08:30 0.1
2021-01-05 08:15 0.09
2021-01-05 07:15 0.09
Checking location of undo latch undo global data
============
PARENT_NAME WHERE NWFAIL_COUNT SLEEP_COUNT WTR_SLP_COUNT LONGHOLD_COUNT LOCATION
---------------------------------------------------------------- -------------------------------------------------------------------------------- ------------ ----------- ------------- -------------- --------------------------------------------------------------------------------
undo global data ktusm_stealext_2 0 135018 15624 0 ktusm_stealext_2
undo global data kturax 0 30779 161 0 kturax
undo global data ktudba: KSLBEGIN 0 3517 105824 0 ktudba: KSLBEGIN
undo global data ktusmupst: KSLBEGIN 0 3363 42119 0 ktusmupst: KSLBEGIN
undo global data ktusm_stealext: KSLBEGIN 0 1260 52 0 ktusm_stealext: KSLBEGIN
undo global data ktucof: at start 0 888 0 0 ktucof: at start
undo global data ktudnx:child 0 181 4375 0 ktudnx:child
undo global data ktuGetRetentionDuration 0 50 184 0 ktuGetRetentionDuration
undo global data kturdc: KSLBEGIN 0 46 2 0 kturdc: KSLBEGIN
undo global data ktufrbs_2 0 35 3613 0 ktufrbs_2
undo global data ktucmt: ktugd_cuux 0 17 20 0 ktucmt: ktugd_cuux
undo global data ktusmasp: ktugd_tuux 0 16 188 0 ktusmasp: ktugd_tuux
undo global data ktusmasp1r_2 0 16 1 0 ktusmasp1r_2
undo global data ktubnd:child 0 7 1193 0 ktubnd:child
undo global data ktusmofxu_1: kslgetl 0 5 0 0 ktusmofxu_1: kslgetl
undo global data ktusmasp: ktugd_suux 0 4 222 0 ktusmasp: ktugd_suux
undo global data kturimugur: child 0 3 159 0 kturimugur: child
undo global data ktur set recov bit 0 1 1401 0 ktur set recov bit
undo global data ktusmstf: KSLBEGIN 0 1 0 0 ktusmstf: KSLBEGIN
undo global data ktugfb 0 1 4 0 ktugfb
Checking most 20 recently undo buffer busy wait in ASH......
============
SAMPLE_TIME SQL_ID EVENT TOP_LEVEL_SQL_ID FILE_ID BLOCK_ID REASON
------------------- ------------- ---------------------------------------------------------------- ---------------- ---------- ---------- ----------
2021-01-05 13:45:31 b5tq6435p6j2h buffer busy waits 5t28uchjqpyfm 379 56945 311
Checking most recent ORA-1555 count......
============
END_TIME UNEXPIREDBLKS UNXPBLKRELCNT UNXPBLKREUCNT EXPIREDBLKS EXPBLKRELCNT EXPBLKREUCNT SSOLDERRCNT
------------------- ------------- ------------- ------------- ----------- ------------ ------------ -----------
2021-01-05 14:05:41 12264016 0 0 4562368 0 0 1
2021-01-05 04:35:41 23274256 0 0 3026176 0 0 1
2021-01-04 23:35:41 10506808 0 0 6716640 0 0 2
2021-01-04 19:25:41 7081704 0 0 10747248 0 0 2
2021-01-04 16:45:41 10124984 0 0 8884456 0 0 2
2021-01-04 14:45:41 13386056 0 0 5830840 0 0 1
2021-01-01 05:25:41 30404640 0 0 5128 24064 0 1
2020-12-31 16:25:41 7833920 0 0 0 286248 0 1
2020-12-31 16:15:41 7601424 2176 0 1024 1407272 0 1
2020-12-31 16:05:41 7869856 3712 0 0 1624664 0 1
2020-12-31 14:05:41 8484032 0 0 3840 390376 0 1
2020-12-31 13:25:41 8726240 128 7 384 909016 0 1
2020-12-31 10:15:41 2637488 0 0 1024 4240 0 1
2020-12-31 10:05:41 2376080 0 0 2304 8192 0 1
2020-12-31 09:55:41 1727392 62512 217548 1920 282904 0 6
2020-12-31 09:45:41 1799848 75680 237159 384 729008 0 2
2020-12-31 09:35:41 1768536 138864 635921 0 1038272 0 3
2020-12-31 09:25:41 1900664 8488 7888 1024 1167528 0 1
2020-12-31 09:15:41 1965392 336840 138558 4360 187072 0 3
2020-12-31 09:05:41 2009656 596888 239303 128 393464 0 5
Checking the status of TOP 20 session which open transaction
============
Check the status of TOP 20 session which open transaction......
SID SERIAL# USERNAME SESSION_STATUS TRX_STATUS MACHINE SQL_ID TRX_START_TIME USED_UNDO_BLKS USED_UNDO_RECORDS USED_UNDO_FILE_ID USED_UNDO_BLOCK_ID
---------- ---------- ------------------------------ -------------- ---------------- ---------------------------------------------------------------- ------------- -------------------- -------------- ----------------- ----------------- ------------------
3610 25245 XXSOP_DS KILLED ACTIVE INT-DATBS-APP-MYH02.cor.com 5bs1vzzr3m9kb 12/31/20 00:33:36 8120807 553978103 194 1281245
160 36767 APPS ACTIVE ACTIVE erpweb01 9hdxps2qs833a 01/04/21 22:15:42 12457 1033332 265 2144972
3774 15169 APPS INACTIVE ACTIVE erpweb01 0at8gkgbkkcgq 01/05/21 10:25:14 400 4883 266 1351119
3749 39593 APPS INACTIVE ACTIVE erpweb01 01/05/21 10:26:02 196 2394 101 174811
3741 40557 APPS INACTIVE ACTIVE erpweb01 01/05/21 10:24:20 196 2394 263 3577957
326 60877 APPS INACTIVE ACTIVE erpweb01 d6hshqtpbq8hd 01/05/21 14:42:52 184 6490 263 1396378
3906 21291 APPS INACTIVE ACTIVE erpweb01 01/05/21 12:15:59 125 4608 263 682791
5829 20083 APPS INACTIVE ACTIVE erpweb01 d6hshqtpbq8hd 01/05/21 15:03:57 112 6552 266 199044
3918 43601 APPS INACTIVE ACTIVE erpweb01 01/05/21 12:15:42 95 3302 265 2095
3364 12905 APPS INACTIVE ACTIVE erpweb01 01/05/21 10:01:12 67 2511 100 2741973
4949 1317 APPS INACTIVE ACTIVE erpweb01 01/05/21 15:04:07 53 3409 103 230247
4028 52161 APPS INACTIVE ACTIVE erpweb01 01/05/21 14:10:40 44 1608 379 2902566
4738 8485 APPS INACTIVE ACTIVE erpweb01 01/05/21 15:19:07 31 385 265 397579
1721 55907 APPS INACTIVE ACTIVE erpweb01 01/05/21 14:57:36 31 1900 264 147309
282 31081 APPS INACTIVE ACTIVE erpweb01 01/04/21 13:34:00 30 336 266 2072663
5680 33197 APPS INACTIVE ACTIVE erpweb01 01/04/21 14:23:30 23 1447 266 912864
3687 54157 APPS INACTIVE ACTIVE erpweb01 01/05/21 15:42:48 23 1483 194 225767
40 15329 APPS INACTIVE ACTIVE erpweb01 01/05/21 14:21:37 17 993 263 1239880
3996 34287 APPS INACTIVE ACTIVE erpweb01 01/05/21 15:41:36 16 791 33 3143889
7 22381 APPS INACTIVE ACTIVE erpweb01 01/05/21 14:14:01 12 655 100 2656172
Checking TOP session current wait event
============
SID SERIAL# EVENT P1TEXT P1 P2TEXT P2
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ----------
3610 25245 db file sequential read file# 248 block# 3558020
Checking TOP session wait event history
============
SAMPLE_TIME SID SERIAL# SQL_ID EVENT P1TEXT P1 P2TEXT P2
----------- ---------- ---------- ------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------------------------------------------------------- ----------
15:50:52 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557828
15:50:51 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558340
15:50:50 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557765
15:50:49 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558213
15:50:48 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557638
15:50:47 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558150
15:50:46 3610 25245 5bs1vzzr3m9kb file# 100 block# 2022778
15:50:45 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558023
15:50:44 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558343
15:50:43 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557832
15:50:42 3610 25245 5bs1vzzr3m9kb file# 249 block# 3551428
15:50:41 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557705
15:50:40 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558153
15:50:39 3610 25245 5bs1vzzr3m9kb file# 100 block# 2022819
15:50:38 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557962
15:50:37 3610 25245 5bs1vzzr3m9kb file# 100 block# 2022834
15:50:36 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557643
15:50:35 3610 25245 5bs1vzzr3m9kb file# 100 block# 2022843
15:50:34 3610 25245 5bs1vzzr3m9kb file# 248 block# 3558475
15:50:33 3610 25245 5bs1vzzr3m9kb file# 248 block# 3557836
Checking TOP session "db file sequential read" average wait time......
(Note:Need to wait 60Secs)
============
TOP rolling back session "db file sequential read" avg_wait_time_ms is:
2021-01-05 15:50:57: 0.68
2021-01-05 15:51:00: 0.44
2021-01-05 15:51:03: 0.41
2021-01-05 15:51:06: 0.76
2021-01-05 15:51:09: 0.67
2021-01-05 15:51:12: 0.71
2021-01-05 15:51:15: 0.77
2021-01-05 15:51:18: 0.47
2021-01-05 15:51:21: 0.38
2021-01-05 15:51:24: 0.49
2021-01-05 15:51:27: 0.44
2021-01-05 15:51:30: 0.29
2021-01-05 15:51:33: 0.51
2021-01-05 15:51:36: 0.44
2021-01-05 15:51:39: 0.5
2021-01-05 15:51:42: 0.34
2021-01-05 15:51:45: 0.35
2021-01-05 15:51:48: 0.43
2021-01-05 15:51:51: 0.38
2021-01-05 15:51:54: 0.41
Checking Check system level "db file sequential read" average wait time......
============
EVENT_NAME END_INTERVAL_TIME AVG_WAIT_TIME_MS
-------------------------------------------------------------------------------- ----------------- ----------------
SystemLevel_AllDatafile:db file sequential read 2021-01-05 15:45 0.34
SystemLevel_AllDatafile:db file sequential read 2021-01-05 15:30 0.42
SystemLevel_AllDatafile:db file sequential read 2021-01-05 15:15 0.16
SystemLevel_AllDatafile:db file sequential read 2021-01-05 15:00 0.26
SystemLevel_AllDatafile:db file sequential read 2021-01-05 14:45 0.27
SystemLevel_AllDatafile:db file sequential read 2021-01-05 14:30 0.21
SystemLevel_AllDatafile:db file sequential read 2021-01-05 14:15 0.47
SystemLevel_AllDatafile:db file sequential read 2021-01-05 14:01 0.48
SystemLevel_AllDatafile:db file sequential read 2021-01-05 13:45 0.46
SystemLevel_AllDatafile:db file sequential read 2021-01-05 13:30 0.42
SystemLevel_AllDatafile:db file sequential read 2021-01-05 13:15 0.4
SystemLevel_AllDatafile:db file sequential read 2021-01-05 13:00 0.35
SystemLevel_AllDatafile:db file sequential read 2021-01-05 12:45 0.25
SystemLevel_AllDatafile:db file sequential read 2021-01-05 12:30 0.29
SystemLevel_AllDatafile:db file sequential read 2021-01-05 12:15 0.37
SystemLevel_AllDatafile:db file sequential read 2021-01-05 12:00 0.22
SystemLevel_AllDatafile:db file sequential read 2021-01-05 11:45 0.32
SystemLevel_AllDatafile:db file sequential read 2021-01-05 11:30 0.22
SystemLevel_AllDatafile:db file sequential read 2021-01-05 11:16 0.26
SystemLevel_AllDatafile:db file sequential read 2021-01-05 11:00 0.08
Checking the transaction rollback estimate time......
(Note:Need to wait 60Secs)
============
2021-01-05 15:52:55==> Base on 60Secs calculated, Estimate remaining trx rollback time is ......:22Days 04Hours 49Mins 37Secs
== >>>>>>>>>> UNDO CHECK SCRIPT END <<<<<<<<<< ==
SQL>
附：sql脚本undo_roll_segment_checking