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
>>> christine.turner_at_ips-sendero.com 12/04/01 03:50PM
>>>
<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 Wed Dec 05 2001 - 10:38:09 CST
