Oracle的进程，如果在os层面kill -9了，那么在v$transaction中是看不到的，需要在v$fast_start_transactions中去看。可以用下面2种方法预估回滚时间：
方法一：（利用v$fast_start_transaction）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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的情况下）:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 |
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_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 <<<<<<<<<< =='); |
结果输出样例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 |
== >>>>>>>>>> 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
一条评论
麻雀虽小五脏俱全