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..
- Create total of 20 rollback segments
- 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).
Received on Tue Dec 04 2001 - 18:28:11 CST
