RE: Rollbacks - ORA-1555

[Steve McClure]

Title: RE: BMC Patrol DBXray / CA Unicenter

Christine,   Here is a white paper I recently found while chasing down another link on this site.  I wish I had read something like this 4 years ago, when I was a developer first trying to figure out what to do about snapshot too old problems.  I too once tried to tame the tiger with ever increasingly large rollback segments.  It doesn’t really touch on how to specifically prevent the error, but it certainly explains the problem much better than I can.  After reading Stephen’s response, I think I need to brush up on rollback management, because I always just tell the developers to figure out a way to accomplish their task, and avoid the error on their own.   Oh the link is Cats Dogs and ORA-01555   Steve McClure     -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christine Turner Sent: Tuesday, December 04, 2001 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Rollbacks - ORA-1555   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