Doc


       Name:    numrollb.sql                          

       Author:  Mark Gurry

       This scripts lists how effectively the rollback segments have been set up .

#

column nl newline;
set pagesize 9999
set echo off
set feedback off
set verify off
set linesize 100

spool numrollb.lis                    
ttitle off
select 'Rollback Segments are not using dedicated tablespaces. This often hinders performance.'
  from dual
where 0 < 
(select count(*) from dba_tablespaces
  where tablespace_name in
   (select tablespace_name from dba_segments 
     where segment_type like 'RO%'
       and tablespace_name != 'SYSTEM'
    INTERSECT
    select tablespace_name from dba_segments 
     where segment_type not like 'RO%'))
/

select  segment_name, tablespace_name 
  from  dba_rollback_segs
where   0 < 
(select count(*) from dba_tablespaces
  where tablespace_name in
   (select tablespace_name from dba_segments 
     where segment_type like 'RO%'
       and tablespace_name != 'SYSTEM'
    INTERSECT
    select tablespace_name from dba_segments 
     where segment_type not like 'RO%'))
/

set heading off
select 'You have had a number of rollback segment waits. Try adding '|| sum(decode(waits,0,0,1)) nl,
       'rollback segments to avoid rollback header contention. '
 from v$rollstat
/
ttitle 'Rollback Segment Activity Since the Instance Started'  
set heading on

select usn "Rollback Table", Gets, Waits , xacts "Active Transactions"
  from v$rollstat
/
ttitle 'Total Number of Rollback Waits Since the Instance Started' 
select class, count 
 from v$waitstat
where class like '%undo%'
/


spool off