Another kind of rollback segment question

From: TECO Wizard <scottg_at_pcb011733.hb.cal.boeing.com>
Date: Mon, 22 Apr 2002 17:21:34 GMT
Message-ID: <aa1gqu$gmm$1@pcb011733.hb.cal.boeing.com>

Hello C.D.O.S Folks,

I have been reading and lurking on this newsgroup for a couple of years and I've now worked up my nerve to ask a question. =:-)

I have various scripts which I run regularly to check the gross/overall health of my databases (I have databases running 7.3.4, 8.0.6, 8.1.5, and 8.1.6 on many servers). One of the scripts contains the following SQL command:

SELECT * FROM V$WAITSTAT WHERE COUNT > 0 ORDER BY CLASS; and I get this output:

CLASS                   COUNT       TIME
------------------ ---------- ----------
data block             442326      65308
segment header            105        172
undo block                 32          5
undo header                25        604

Another script contains this SQL command:

SELECT NAME, RSSIZE/1024 RSSIZE, HWMSIZE/1024 HWMSIZE,

       AVEACTIVE/1024 AVEACTIVE, XACTS, EXTENDS, SHRINKS,
       GETS, WAITS, WRAPS

FROM V$ROLLNAME A, V$ROLLSTAT B
WHERE A.USN = B.USN
ORDER BY NAME; and I get this output:

NAME RSSIZE HWMSIZE AVEACTIVE XACTS EXTENDS SHRINKS GETS WAITS WRAPS -------- ------- ------- --------- ----- ------- ------- -------- ------- -----

R01         4152   18712      2106     0      14       2   141303       0    57
R02         4152   22872      4409     0      80      10   148559       1   131
R03         6232   58232      6482     0     130      15   151151       1   181
R04         4152   58232      8950     1     101      13   145537       1   151
R05         4152   22872      5664     0      45       5   188198       1    94
R06         4152   18712      1970     0      14       2   134128       0    58
R07         4152   22872      4122     0      31       4   181788       0    74
R08         4152   58232      7386     1      75      11   147689       1   128
R09         4152   22872      1583     0      21       4   203928       1    61
R10         4152   58232     14223     0      89      11   153919       0   140
R11         4152   58232     12943     1      67       9   147589       1   117
R12         4152   58232      3609     0      70      10   150497       1   116
R13         4152   58232     28695     1     114      13   150770       0   162
R14         4152   58232     11614     0     236      28   214872       2   293
R15         4152   58232     43191     0      75      13   151113       2   131
SYSTEM       712     712         0     0       0       0     5980       0     0

The 'undo header' from the first query has a count of 25 but the WAITS column from the second query totals to 12.

Now here are my questions:

Is it significant that these numbers differ?
Is one stat or the other more reliable/definitive/whatever? (Especially in determining when to do something about the situation.)

Thanks a lot folks. I have a lot of respect for the "Big Guns" that post on this newsgroup.

-- 
Scott Gallaher
Boeing Imperial Oracle DBA and TECO Wizard
"Quondo Omni Flunkus Mortati"

Received on Mon Apr 22 2002 - 12:21:34 CDT