Re: Rollbacks - ORA-1555

[Stephen Andert]

Christine,   Howdy neighbor.  I've almost eliminated 1555's.  It took some trial and error, but what we ended up with in one of our bigger db's is:   30 rollback segments OPTIMAL (NULL) minextents 20 initial = next = 4MB 6GB total space   We have between 400 and 500 interactive users, several high volume batch jobs and some DSS jobs.    By getting rid of OPTIMAL, you avoid one of the more common causes of the 1555 where a rollback segment is shrunk while the data block is being used.  We schedule a shrink script to run and manually shrink all the rollback segments weekly.  At times some tasks can eat up more rbs, but since they are dba activities, we just shrink a single rollback segment at a time if it gets too big.    HTH   Stephen Andert   >>> [EMAIL PROTECTED] 12/04/01 03:50PM >>> Greetings All....   I am some what new to the list, so forgive me if I don't have the proper etiquette in addressing my issue. I have a database, 8.1.6, running on Windows NT, that currently has 5 rollback segments. The specs are as follows for each segment:   OPTIMAL 350M minextents 7 maxextents unlimited initial 50M next 50M   These segments are currently in one tablespace, for rollbacks only, which is sized at 2.5 gig, and currently the segments are taking 1.7 gig, obviously aprox 750 meg free.   I have an application, written by our developers here, which is doing a functionality called "pricing". Within this process is alot of DML (updates and deletes) with some DDL inter-mixed. There is an auto-commit feature, which is currently commiting every 1000 records. There is also a locking feature, before the actual "fetches" the application is performing for it's cursors, and the developers are currently using "select * from table for update nowait" to lock the whole table for this process. The locking is in place because this particular process can use up to 5 different sessions.   Currently the stats of the rollbacks look like this:   data requests -------------       3817488   CLASS                   COUNT ------------------ ---------- system undo header          0 system undo block           0 undo header                 3 undo block                  1    USN NAME        AVEACTIVE    OPTSIZE WAITS WRAPS EXTENDS    SHRINKS  AVESHRINK ---- ---------- ---------- ---------- ----- ----- ------- ---------- ----------    0 SYSTEM              0                0     0       0          0          0    2 SV_ROLL0            0  367001600     2     0       0          0          0    3 SV_ROLL1            0  367001600     0     0       0          0          0    4 SV_ROLL2            0  367001600     1     0       0          0          0    5 SV_ROLL3            0  367001600     0     0       0          0          0    6 SV_ROLL4            0  367001600     0     0       0          0          0   6 rows selected.   TSPACE               TOTAL       USED       FREE --------------- ---------- ---------- ---------- SV_ROLL_TSP           2500       1751        750   At times I have seen the "aveactive" column have some numeric value in it, but when the database and services are shutdown and brought back up, this number clears out.   My question is this: how much larger are these rollbacks supposed to be before I can eliminate the waits and wraps? More importantly, eliminate the undo headers and block. I have done alot of testing, with different sizing, and I feel like I'm chasing my tail. This is a major feature of our software, so it's not like it can be "ran at night" to differ to a timing issue. I have also noticed, that PMON doesn't really "shrink" appropriately, not back to a state like they are when they are first created. At this point, I guess I'm looking for some insight, advice as to what to specifically do to tune these segments a little more.   Thanks So Much, in advance....   Christine