聚合国内IT技术精华文章,分享IT技术精华,帮助IT从业人士成长

Oralce undo健康检查脚本

2021-01-05 16:56 浏览: 1305 次 我要评论(0 条) 字号:

Oralce undo健康检查脚本:

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 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 <<<<<<<<<< ==');

结果输出样例:

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



网友评论已有0条评论, 我也要评论

发表评论

*

* (保密)

Ctrl+Enter 快捷回复