Re: Rollback segments (Oracle 6)

In article <2gsl9o$b0e_at_Joanna.Wes.Army.Mil>, u4imcehb_at_apollo.wes.army.mil (Ernest Boswell) writes:
|>Can someone shed some light on how Oracle 6 handles rollback segments. Our
|>DBA (commonly known as Don't Bother to Ask) tells me that once a rollback
|>segment is used, it isn't freed again until the database is shutdown and
|>restarted. This doesn't make sense, but I seem to get a lot of "out of
|>rollback space"-type errors. I'm working with a database about 300M in size,
|>primary table having _at_800,000 rows. This is a scientific database (water-
|>quality) in the building process, so there are frequent database-wide updates.
|>The rollback space is about 250 M, but I get rollback errors even when I'm
|>operating against only a subset of the database. Any help/ideas/advice?
|>
|>Thanks in advance,
|>
|>
|>Harry Boswell u4imcehb_at_apollo.wes.army.mil
|>Computer Scientist
|>Information Technology Laboratory
|>U.S. Army Corps of Engineers
|>
|>"select std_disclaimer from opinions_not_shared_by_employer"
|>
Well, I think the first thing to do it's to look what's happens on the database and particularly on Rollback segments. Perhaps there are CONTENTION problems. Rollback segments are used to manage transaction and consistency read notions and to restore the database after an instancy incident. A database can have many Rollback segments. Every one is an ensemble of Oracle blocks. Every Rollback segment block contains rollback registrations. A registration contains all the information to cancel a transaction. The SGA contains Rollback blocks (Rollback buffers). These buffers are very often sollicited by concurrency transactions, so there's contention risk. The mesure of Rollback buffers contention can be done in two stages : Stage 1 :

- under SQLDBA type the command : MONITOR STATISTICS CACHE
- calculate the rapport : buffer busy waits/(db block gets+consistent gets)
- if the rapport > 15% there's probably contention on SGA buffers. To know

   exactly on what type of buffer there's contention go to stage 2. Stage 2 :
- consult the V$WAITSTAT table.
- if the number of waits on undo segment header and undo block is very greatest

   than the data block one --> the Rollback buffers are the ones which cause     the more contention at the SGA buffers level. SO YOU MUST INCREASE THE     NUMBER OF ROLLBACK SEGMENTS.
Hope this help and excuse me for my poor English !

-- 
------------------------------------------------------------------------------
   _/_/_/_/    _/_/_/_/   _/_/_/_/   _/_/_/_/                  I use
  _/_/          _/_/       _/_/       _/_/               ORACLE v6.0.36.5.2
 _/_/          _/_/       _/_/       _/_/                       on        
_/_/_/_/ .  _/_/_/_/ .   _/_/ .   _/_/_/_/ .            DEC RISC ULTRIX v4.3
------------------------------------------------------------------------------
Yves NOEL   -   Database Administrator         Mail : Yves.Noel_at_univ-lille1.fr
C.I.T.I. (batiment M4)                             
Universite des Sciences & Technologies de Lille      Phone : (33)  20.43.42.70
59655 Villeneuve d'Ascq Cedex - FRANCE               Fax   : (33)  20.43.66.25 
------------------------------------------------------------------------------