Re: Contention in RBS Header?

From: Mark D Powell <mark.powell_at_eds.com>
Date: 15 Apr 2002 12:56:09 -0700
Message-ID: <178d2795.0204151156.6abd0775@posting.google.com>

rfray_at_vianetworks.co.uk (Russell Fray) wrote in message news:<3cbad40f.263782018_at_news.u-net.com>...
> Hi All,
>
> Firstly, apologies if I'm posting to the wrong group - if you could
> point me to a more appropriate group I'd be extremely grateful (I've
> purposely avoided cross posting!)
>
> I've been asked to investigate whether there is contention in the
> Rollback Segment Headers but I'm not sure of the best views to look in
> to get this info.
>
> Could somebody give me a few pointers regarding which views & columns
> I should be looking at and what I should look for? Alternatively if
> there are any scripts?
>
> Any (useful!) replies will be gratefully received.
>
> Thanks,
> Russ.

Russ, you should probably check the Performance and Tuning Manual for your version as this manual has provided the queries to do this in the past.

For results like:
UT1> @rbs_waits

Class                   Count       Time Class Wait %
------------------ ---------- ---------- ------------
system undo block           0          0            0
system undo header          0          0            0
undo block                  0          0            0
undo header                 0          0            0


                          Hit
Name                    Ratio        Waits             Gets
-------------------- -------- ------------ ----------------
SYSTEM                100.000            0              347
ROLL01                100.000            0            1,369
ROLL02                100.000            0            2,737
ROLL03                100.000            0            1,375
ROLL04                100.000            0            1,325

try:
set echo off
rem file: rbs_waits.sql
rem SQL*Plus script to show rollback segment waits. rem
rem 19980204 m d powell New Script.
rem
set pagesize 60

select class  "Class",
       count  "Count",
       time   "Time" ,
       ( round(count / b.read_ct * 100,3) ) "Class Wait %"
  from v$waitstat w, (select  sum(value) read_ct
                        from  v$sysstat
                       where  name in ('db block gets',
                                       'consistent gets') ) b
 where class in (
                 'system undo header',
                 'system undo block',
                 'undo header',
                 'undo block'
                )

 order by class
/
column HR format 990.999 heading 'Hit|Ratio' column waits format 999,999,990
column gets format 999,999,999,990
select substr(name,1,20) "Name" ,

       round( ( 100 - (waits/gets) ), 4 )  HR,
       waits              "Waits" ,
       gets               "Gets"
  from v$rollstat rs,
       v$rollname rn

 where rs.usn = rn.usn
/

HTH -- Mark D Powell -- Received on Mon Apr 15 2002 - 14:56:09 CDT