Hi Christine,
these results are slightly differnt from the earlier
ones you .. as the latest ones had 8,48409 fewer data
requests.
I believe you are worried about the wraps.. if yes ..
dont be , cuz at some point or the other the rbs will
wrap. the definition of wrapping is controversial and
lots of confusion still exists. That said, wraps are
probably not the best metrics to help you identify rbs
issues.. here are the metrics i use to for identifying
if there are rbs issues from v$rollstat.. (excluding
v$waitstat stats as you already seem to have figured
that one it seems)
- waits (ideally you want this to be 0)
- shrink (if high means that there is dynamic
allocation and reallocation.. tune optimal.. ideally
you want your rbs to always stay at its optimal size
with 0 shrinks..)
- AVEACTIVE: you reached a maximum of about 200K. so
maybe you should try changing initial and next to 250K
with other parameters same and check if the numbers
are looking better
finally the reason i replied to the whole list and not
only to you is that i am hoping someone can come
forward and say that .. ""dude!! you have it figgured
all wrong!!"" that way at least i get to learn somin
new for sure... ;)
Thx
Deepak
PS: might also help to check v$session_wait for buffer
waits to see what "type" of blocks are being contended
for..
- Christine Turner
<christine.turner_at_ips-sendero.com> wrote:
> Hello All,
>
> I changed the rollback segments, and here are the
> results. I'm now at 20
> segments, 1 meg each, minextents 20, optimal 20.
> Here are the results after
> running the process within the application with
> auto-commits turned off...
>
> data requests
> -------------
> 2969079
>
>
> CLASS COUNT
> ------------------ ---------
> system undo header 0
> system undo block 0
> undo header 1
> undo block 0
>
>
> USN NAME AVEACTIVE OPTSIZE WAITS WRAPS
> EXTENDS SHRINKS AVESHRINK
> ---- ---------- --------- --------- ----- -----
> ------- --------- ---------
> 0 SYSTEM 0 0 0
> 0 0 0
> 2 SV_ROLL0 106086 20971520 0 1
> 0 0 0
> 3 SV_ROLL1 106086 20971520 0 1
> 0 0 0
> 4 SV_ROLL2 106086 20971520 0 1
> 0 0 0
> 5 SV_ROLL3 106086 20971520 0 1
> 0 0 0
> 6 SV_ROLL4 0 20971520 0 0
> 0 0 0
> 7 SV_ROLL5 106086 20971520 0 1
> 0 0 0
> 8 SV_ROLL6 106086 20971520 0 1
> 0 0 0
> 9 SV_ROLL7 106086 20971520 0 1
> 0 0 0
> 10 SV_ROLL8 106086 20971520 0 1
> 0 0 0
> 11 SV_ROLL9 106086 20971520 0 1
> 0 0 0
> 12 SV_ROLL10 201973 20971520 1 2
> 0 0 0
> 13 SV_ROLL11 0 20971520 0 0
> 0 0 0
> 14 SV_ROLL12 106086 20971520 0 1
> 0 0 0
> 15 SV_ROLL13 106086 20971520 0 1
> 0 0 0
> 16 SV_ROLL14 106086 20971520 0 1
> 0 0 0
> 17 SV_ROLL15 106086 20971520 0 1
> 0 0 0
> 18 SV_ROLL16 201973 20971520 0 2
> 0 0 0
> 19 SV_ROLL17 106086 20971520 0 1
> 0 0 0
> 20 SV_ROLL18 106086 20971520 0 1
> 0 0 0
> 21 SV_ROLL19 106086 20971520 0 1
> 0 0 0
>
> 21 rows selected.
>
>
> TSPACE TOTAL USED FREE
> --------------- --------- --------- ---------
> SV_ROLL_TSP 800 407 394
>
>
> This doesn't look real good to me....am I correct???
> I will try processing
> without the optimal being set to see what happens,
> while I await other
> response.
>
> thanks!
> Christine
>
>
>
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On
> Behalf Of Deepak
> Thapliyal
> Sent: Tuesday, December 04, 2001 5:12 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Rollbacks - ORA-1555
>
>
> Hi Christine,
>
> Your rollback segments look large to me based on the
> description you have given for your application. One
> way of eliminating header waits is to increase the
> number of rbs .. try this and post if this helps
> your
> stats..
>
> 1. Create total of 20 rollback segments
>
> 2. Specification for each rbs is :
> initial 1M
> next 1M
> minextents 20
> maxextents unlimited
> optimal 20M
>
> meaning that each rbs will have 20 extents initially
> and size of each rbs will be 20M initially. since
> you
> have 20 such rbs, total rbs used is 20 * 20M = 400M
>
> if you observe, the above structure uses smaller
> sized
> large number of rollback segments as from your
> description below, it looks like you have a oltp
> system with large number of transactions
>
> tell me if this helps ..
>
> Deepak
>
> --- Christine Turner
> <christine.turner_at_ips-sendero.com> wrote:
> > RE: BMC Patrol DBXray / CA UnicenterGreetings
> > 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
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Buy the perfect holiday gifts at Yahoo! Shopping.
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Deepak Thapliyal
> INET: deepakthapliyal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
>
Do You Yahoo!?
Buy the perfect holiday gifts at Yahoo! Shopping.
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Deepak Thapliyal
INET: deepakthapliyal_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Dec 05 2001 - 14:12:24 CST
