doc

    Name:   trantbl.sql

    Author: Mark Gurry


The following scripts list the number of rollbacks performed on the 
transaction tables.

Script#

(i)  'transaction tables consistent reads - undo records applied' is the 
total # of undo records applied to rollback transaction tables only. It
should be < 10% of the total number of consistent changes

(ii) 'transaction tables consistent read rollbacks' is the number of times
 the transaction tables were rolled back. It should be less than 0.1 % of 
 the value of consistent gets. 

If either of these scenarios occurs, consider creating more rollback 
segments, or a greater number of extents in each rolback segment. A 
rollback segment equates to a transaction table and an extent is like a 
transaction slot in the table. 

#

select 'Tran Table Consistent Read Rollbacks > 1% of Consistent Gets'aa,
          'Action: Create more Rollback Segments'
  from v$sysstat
where decode (name,'transaction tables consistent read rollbacks',value)
                       * 100
                        /
      decode (name,'consistent gets',value) > 0.1
  and name in ('transaction tables consistent read rollbacks',
                   ‘consistent gets')
  and value > 0; 

select 'Undo Records Applied > 10% of Consistent Changes' aa,
       'Action: Create more Rollback Segments'
  from v$sysstat
 where decode 
     (name,'transaction tables consistent reads - undo records applied'
                   ,value) 
                    * 100
                     /
       decode (name,'consistent changes',value) > 10  
   and name in 
        ('transaction tables consistent reads - undo records applied', 
                 'consistent changes')
   and value > 0;