RE: Rollbacks - ORA-1555

From: Steve McClure <smcclure_at_usscript.com>
Date: Wed, 05 Dec 2001 12:09:51 -0800
Message-ID: <F001.003D606D.20011205114521@fatcity.com>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Christine,

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>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.

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Oh the link is <a
href="http://www.evdbt.com/CatsDogsORA01555.doc">Cats Dogs and ORA-01555

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>Steve McClure

<span

style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'>

-----Original
Message-----
From: root_at_fatcity.com
[mailto:root_at_fatcity.com]On Behalf Of Christine Turner
Sent: Tuesday, December 04, 2001
2:50 PM
To: Multiple recipients of list
ORACLE-L
Subject: Rollbacks - ORA-1555

<span

style='font-size:12.0pt'>

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Greetings All....

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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:

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>OPTIMAL 350M

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>minextents 7

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>maxextents unlimited

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>initial 50M

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>next 50M

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Currently the stats of the rollbacks look like this:

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>data requests

3817488

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>

CLASS

COUNT

------------------ ----------

system undo header 0
system undo block 0
undo

header

3
undo

block

<span

style='font-size:10.0pt;font-family:Arial;color:blue'> 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

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>6 rows selected.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>

TSPACE              
TOTAL      
USED       FREE

--------------- ---------- ---------- ---------- SV_ROLL_TSP
2500
1751 750

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>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.

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Thanks So Much, in advance....

<span

style='font-size:10.0pt;font-family:Arial;color:blue'>Christine Received on Wed Dec 05 2001 - 14:09:51 CST