oracle-undo_check2.sql

set pages 1000
set line 1000
set feedback off
set serveroutput on 

-- UNDO Check script start
exec dbms_output.put_line('== >>>>>>>>>> UNDO CHECK SCRIPT START <<<<<<<<<< ==');
exec dbms_output.put_line(' ');

-- Check  database version
PROMPT Checking database version......
PROMPT ============
select * from v$version;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check if flashback on
PROMPT Checking if flashback on......
PROMPT ============
select flashback_on from v$database;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check  the undo parameter
PROMPT Checking the undo parameter......
PROMPT ============
show parameter undo;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check undo tablespace usage
PROMPT Checking undo tablespace usage......
PROMPT ============
SELECT d.status ,
       d.tablespace_name ,
       d.contents,
       d.extent_management,
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99999999.999') as total_size_mb,
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,
               '99999999.999') used_size_mb,
       to_char(nvl(nvl(f.bytes, 0), 0) / 1024 / 1024, '99999999.999') free_size_mb,
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),
               '990.00') as used_percent
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND d.contents='UNDO';
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check rollback segment status
PROMPT Checking rollback segment status......
PROMPT ============
select tablespace_name,status,sum(bytes)/1024/1024 mb from DBA_UNDO_EXTENTS group by tablespace_name,status;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


--Check roll segment online status
PROMPT Checking roll segment online status......
PROMPT ============
SELECT
substr(name,1,7) as rollname,
status,count(*) as cnt
FROM v$rollstat, v$rollname
WHERE v$rollstat.usn=v$rollname.usn
group by 
substr(name,1,7),status;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check TOP 20 roll segment extend status
PROMPT Checking TOP 20 roll segment extend status......
PROMPT ============
select * from (
SELECT
ds.segment_name "Seq Name",
ds.bytes "Bytes",
ds.blocks "Blocks",
ds.extents "Extents",
ds.initial_extent "Init Ext",
ds.next_extent "Next Ext",
ds.min_extents "Min Ext",
ds.max_extents "Max Ext"
FROM dba_segments ds
WHERE segment_type in ('ROLLBACK','TYPE2 UNDO') order by extents desc) where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check undo header wait
PROMPT Checking Undo Header Waits......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
 declare
  s_num_rbs number;
  s_header_wait number;
  s_waits_per_rbs number;
  e_num_rbs number;
  e_header_wait number;
  e_waits_per_rbs number;
  delta_num_rbs number;
  delta_header_wait number;
  delta_waits_per_rbs number;
  s_mydate   varchar2(200);
  e_mydate   varchar2(200);
  v_pause_secs number;
  begin  
  --Set the pause time for get 2 times change,default is 30 seconds  
  v_pause_secs:=60;
  select to_char(sysdate,'hh24:mi:ss') into s_mydate from dual;  
  select COUNT(stat.USN) ,wait.Count,round(wait.Count/COUNT(stat.USN),2) into s_num_rbs,s_header_wait,s_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
  dbms_lock.sleep(v_pause_secs);
  select to_char(sysdate,'hh24:mi:ss') into e_mydate from dual; 
  select COUNT(stat.USN),wait.Count,round(wait.Count/COUNT(stat.USN),2) into e_num_rbs,e_header_wait,e_waits_per_rbs from V$WAITSTAT wait, V$ROLLSTAT stat where stat.Status = 'ONLINE' and wait.Class = 'undo header' and stat.USN > 0 group by wait.Count;
  select (e_num_rbs-s_num_rbs),(e_header_wait-s_header_wait),to_char(round(nvl(decode((e_waits_per_rbs-s_waits_per_rbs),0,null,(e_waits_per_rbs-s_waits_per_rbs)),0),2),'fm999999990.999999999') into delta_num_rbs,delta_header_wait,delta_waits_per_rbs  from dual;
    dbms_output.put_line(s_mydate||'==> At start time:');
    dbms_output.put_line('Number of Rollback segments:'||s_num_rbs||' ,Number of Undo header waits:'||s_header_wait||' ,Number of header wait per Rollback segment:'||s_waits_per_rbs);
    dbms_output.put_line(e_mydate||'==> At end time:');
    dbms_output.put_line('Number of Rollback segments:'||e_num_rbs||' ,Number of Undo header waits:'||e_header_wait||' ,Number of header wait per Rollback segment:'||e_waits_per_rbs);
    dbms_output.put_line('==== Change during '||v_pause_secs||' Seconds:====');
    dbms_output.put_line('Delta of Rollback segments:'||delta_num_rbs||' ,Delta of Undo header waits:'||delta_header_wait||' ,Delta of header wait per Rollback segment:'||delta_waits_per_rbs);
  end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check latch: undo global data
PROMPT Checking TOP 20 "latch: undo global data"......
PROMPT ============
select * from (
select to_char(end_interval_time,'yyyy-mm-dd hh24:mi'),
       nvl(round((c.time_waited_micro - lag(time_waited_micro)
        over(order by c.snap_id)) /
       decode((c.total_waits - lag(total_waits) over(order by c.snap_id)),0,null,(c.total_waits - lag(total_waits) over(order by c.snap_id)))/1000,2),0) as avg_wait_time_ms
  from dba_hist_system_event c, dba_hist_snapshot dd
 where event_name = 'latch: undo global data' and c.instance_number=dd.instance_number
   and c.snap_id = dd.snap_id and c.instance_number=1
   and c.snap_id >= 1 and end_interval_time>=trunc(sysdate-7)
   order by 1 desc)
   where avg_wait_time_ms<>0 and rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check location of undo latch undo global data 
PROMPT Checking location of undo latch undo global data 
PROMPT ============
select * from v$latch_misses where SLEEP_COUNT>0 and  parent_name like 'undo global data%' order by sleep_count desc;  
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check undo buffer busy wait in ASH
PROMPT Checking most 20 recently undo buffer busy wait in ASH......
PROMPT ============
select * from (
 select to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') as sample_time,
 sql_id,event,TOP_LEVEL_SQL_ID,p1 as file_id,p2 as block_id, p3 as reason  from v$active_session_history 
 where event='buffer busy waits'
   and p1 in (select file_id from DBA_ROLLBACK_SEGS where segment_name<>'SYSTEM')
   and sample_time>=trunc(sysdate-7)
   order by sample_time desc)
   where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');   


-- Check most recent ORA-1555 count
PROMPT Checking most recent ORA-1555 count......
PROMPT ============
select * from (
select to_char(end_time,'yyyy-mm-dd hh24:mi:ss') end_time,
x.unexpiredblks,x.unxpblkrelcnt,x.unxpblkreucnt,
x.expiredblks,x.expblkrelcnt,x.expblkreucnt,
x.ssolderrcnt 
from DBA_HIST_UNDOSTAT x
where x.ssolderrcnt>0
order by end_time desc)
where rownum<=20;  
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check the status of TOP 20 session which open transaction
PROMPT Checking the status of TOP 20 session which open transaction
PROMPT ============
PROMPT Check the status of TOP 20 session which open transaction......
select * from (   
select b.sid,  
       b.SERIAL#,  
       b.USERNAME,
       b.status as session_status,
       a.STATUS as trx_status,  
       b.MACHINE,  
       b.sql_id,  
       a.START_TIME as trx_start_time,
       a.USED_UBLK as used_undo_blks, 
       a.USED_UREC as used_undo_records,
       a.START_UBAFIL as used_undo_file_id, 
       a.START_UBABLK as used_undo_block_id
  from v$transaction a, v$session b  
 where a.ses_addr = b.saddr order by USED_UBLK desc)
 where rownum<=20;  
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check TOP session current wait event
PROMPT Checking TOP session current wait event
PROMPT ============
select * from (   
select b.sid,  
       b.SERIAL#, event,p1text,p1,p2text, p2
  from v$transaction a, v$session b  
 where a.ses_addr = b.saddr order by USED_UBLK desc)
 where rownum<=1;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');


-- Check TOP session wait event history
PROMPT Checking TOP session wait event history
PROMPT ============
select * from (
select to_char(sample_time,'hh24:mi:ss') as sample_time,sid,serial#,sql_id,event,p1text,p1,p2text,p2 from v$active_session_history c,
(select * from (select b.sid,b.serial# from 
v$transaction a, v$session b  
where a.ses_addr = b.saddr 
order by USED_UBLK desc) where rownum<=1) x
where c.SESSION_ID=x.sid and c.SESSION_SERIAL#=x.serial#
order by sample_time desc)
where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check TOP session "db file sequential read" average wait time
PROMPT Checking TOP session "db file sequential read" average wait time......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
 declare
  v_top_used_ublk_sid number;
  v_top_used_ublk_serial# number;
  s_chktm varchar2(200);
  s_tm_waited_micro    number;
  s_total_waits   number;
  s_avg_wait   number;
  e_chktm varchar2(200);
  e_tm_waited_micro    number;
  e_total_waits   number;
  e_avg_wait   number;
  v_delta_avg_wait varchar2(200);
  v_pause_secs number;
  v_cycle_cnt number;
    begin  
  --Set the pause time for get 2 times change,default is 3 seconds  
  v_pause_secs:=3;
  v_cycle_cnt:=20; 
 dbms_output.put_line('TOP rolling back session "db file sequential read" avg_wait_time_ms is: '); 
 select sid,serial# into v_top_used_ublk_sid,v_top_used_ublk_serial#  from (select b.sid,b.serial# from v$transaction a, v$session b  where a.ses_addr = b.saddr order by USED_UBLK desc) where rownum<=1;  
 for i in 1 .. v_cycle_cnt loop
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),time_waited_micro,total_waits,a.AVERAGE_WAIT into s_chktm,s_tm_waited_micro,s_total_waits,s_avg_wait from v$session_event a where a.sid=v_top_used_ublk_sid and event='db file sequential read';
   dbms_lock.sleep(v_pause_secs);
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),time_waited_micro,total_waits,a.AVERAGE_WAIT into e_chktm,e_tm_waited_micro,e_total_waits,s_avg_wait from v$session_event a where a.sid=v_top_used_ublk_sid and event='db file sequential read';
   select to_char(round((e_tm_waited_micro-s_tm_waited_micro)/(e_total_waits-s_total_waits)/1000,2),'fm999999990.999999999') into v_delta_avg_wait from dual;
   dbms_output.put_line(e_chktm||':  '|| v_delta_avg_wait);
 end loop;
 end;
/   
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

-- Check system level "db file sequential read" average wait time
PROMPT Checking Check system level "db file sequential read" average wait time......
PROMPT ============
select * from (
select 'SystemLevel_AllDatafile:'||event_name as event_name,to_char(end_interval_time,'yyyy-mm-dd hh24:mi') end_interval_time,
       round((c.time_waited_micro - lag(time_waited_micro)
        over(order by c.snap_id)) /
       decode((c.total_waits - lag(total_waits) over(order by c.snap_id)),0,null,(c.total_waits - lag(total_waits) over(order by c.snap_id)))/1000,2) as avg_wait_time_ms
  from dba_hist_system_event c, dba_hist_snapshot dd
 where event_name = 'db file sequential read' and c.instance_number=dd.instance_number
   and c.snap_id = dd.snap_id and c.instance_number=1
   and c.snap_id >= 1 and end_interval_time>=trunc(sysdate-7)
   order by 2 desc)
   where rownum<=20;
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');   



-- Check the transaction rollback estimate time
PROMPT Checking the transaction rollback estimate time......
PROMPT (Note:Need to wait 60Secs)
PROMPT ============
 declare
  l_start number;
  l_end    number;
  mydate   varchar2(200);
  est_time   varchar2(200);
  v_pause_secs number;
  begin  
  --Set the pause time for get 2 times change,default is 30 seconds  
  v_pause_secs:=60;  
  select sum(ktuxesiz) into l_start from x$ktuxe x ,v$transaction b, v$session a where a.taddr=b.addr and  x.ktuxeusn=b.XIDUSN and x.ktuxeslt=b.XIDSLOT and ktuxesqn=b.XIDSQN and a.status='KILLED' and b.STATUS='ACTIVE';
    dbms_lock.sleep(v_pause_secs);
   select sum(ktuxesiz) into l_end from x$ktuxe x ,v$transaction b, v$session a where a.taddr=b.addr and  x.ktuxeusn=b.XIDUSN and x.ktuxeslt=b.XIDSLOT and ktuxesqn=b.XIDSQN and a.status='KILLED' and b.STATUS='ACTIVE';
   select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into mydate from dual;
   select 
    substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),9,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),' ')-9)||'Days '||
    substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),12,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),':')-12)||'Hours '||
    substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),15,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),':',1,2)-15)||'Mins '||
    substr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),18,instr(to_char(numtodsinterval((l_end/((l_start -l_end)/v_pause_secs)),'second')),'.',1,1)-18)||'Secs'
    into est_time
   from dual;
    dbms_output.put_line(mydate||'==> Base on '||v_pause_secs||'Secs calculated, Estimate remaining trx rollback time is ......:'|| est_time);
  end;
/
exec dbms_output.put_line(' ');
exec dbms_output.put_line(' ');

exec dbms_output.put_line(' ');
exec dbms_output.put_line('== >>>>>>>>>> UNDO CHECK SCRIPT END <<<<<<<<<< ==');