Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Rollbacks - ORA-1555

Rollbacks - ORA-1555

From: Christine Turner <christine.turner_at_ips-sendero.com>
Date: Tue, 04 Dec 2001 15:14:18 -0800
Message-ID: <F001.003D540B.20011204145028@fatcity.com>

<SPAN

class=116543622-04122001>Greetings All....
<SPAN

class=116543622-04122001> 
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

class=116543622-04122001> 
<SPAN

class=116543622-04122001>OPTIMAL 350M
<SPAN

class=116543622-04122001>minextents 7
<SPAN

class=116543622-04122001>maxextents unlimited
<SPAN

class=116543622-04122001>initial 50M
next
50M
<SPAN

class=116543622-04122001> 
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

class=116543622-04122001> 
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

class=116543622-04122001> 
<SPAN

class=116543622-04122001>Currently the stats of the rollbacks look like this:
<SPAN

class=116543622-04122001> 
data
requests-------------     
3817488
 
<SPAN

class=116543622-04122001>CLASS                   
COUNT------------------ ----------system undo 
header          0system undo 

block           0undo
header                 

3undo
block                  

1
 
<SPAN

class=116543622-04122001> 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.
 
<SPAN

class=116543622-04122001>TSPACE               TOTAL      
USED       FREE--------------- ----------

----------SV_ROLL_TSP          
2500      
1751        750
<SPAN

class=116543622-04122001> 
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.
<SPAN

class=116543622-04122001> 
Thanks
So Much, in advance....
<SPAN

class=116543622-04122001> 
<SPAN

class=116543622-04122001>Christine Received on Tue Dec 04 2001 - 17:14:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US