Re: undo header and buffer busy wait

From: Mark D Powell <mark.powell_at_eds.com>
Date: 21 Feb 2002 06:56:06 -0800
Message-ID: <178d2795.0202210656.6e3c0024@posting.google.com>

u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1013806002.1034759521@[64.94.198.252]>...
> Thanks for Howard J. Rogers and Mark D Powell's help.
>
> But after I make our rollback segments 10 times bigger, we are still
> get the same undo header wait event, it did not show any improvment.
>
> Our database is collecting data through robot, each second we have
> about 30 rows comming, and we are using auto committing.
>
> Actually I seldom see any transaction using rollback segment, because
> it commits so quickly. But why we are getting undo header contention?
>
> Any thoughts?
>
> thanks

Question: what are you using to measure/determine you have contention? I hope you are not counting a non-zero value for 'undo header' in v$waitstat as contention. This number has meaning only when considered in relation to the number of IO requests.

Statspack produces very nice numbers for rbs information. It is very easy to set up and run manually (run spdelete, spcreate, statspack.snap, spreport). To automate running it look at spauto in $ORACLE_HOME/rdbms/admin

Here is some manual sql based either on the Performance and Tuning manual or probably Guy Harrison's Oracle SQL High Performance and Tuning book. (On a test system)

UT1> @rbs_waits

Class                   Count       Time Class Wait %
------------------ ---------- ---------- ------------
system undo block           0          0            0
system undo header          0          0            0
undo block               1407        112         .001
undo header               104        182            0


                          Hit
Name                    Ratio        Waits             Gets
-------------------- -------- ------------ ----------------
SYSTEM                100.000            0            7,094
ROLL01                100.000            3           93,292
ROLL02                100.000            1           42,022
ROLL03                100.000           35          102,245
ROLL04                100.000            1           51,281

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
/

Mark D Powell --

Received on Thu Feb 21 2002 - 08:56:06 CST