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
-------------
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