Zhu Chao,
Please be aware that rollback segments serve two major purposes:
* recover individual transactions (i.e. rollback)
* provide before-image information for read-consistency
The information you are getting from V$ROLLSTAT and STATSPACK is accurate at
measuring the first purpose only. Until Oracle9i and the V$UNDOSTAT view
that accompanies the use of UNDO tablespaces, it is very difficult to
estimate how much space is necessary to satisfy the needs of SQL statements
requiring before-image information in order to complete successfully without
the ORA-01555 "snapshot too old" error message.
First of all, the size or number of extents in a rollback segment does not
affect the performance of SQL statements. Of course, if the size of the
extents is extremely small and the number of extents varies wildly up and
down, then the sheer overhead of extent maintenance might be a performance
factor. Since you are examining V$ROLLSTAT and STATSPACK, you have info as
to whether this is happening in hand (i.e. columns EXTENDS and SHRINKS in
V$ROLLSTAT)...
So, since there is no adverse impact related to the number and size of
extents for RBS (other than that mentioned above, easily verifiable), and
since reducing the amount of space in your RBS can increase the probability
of incurring the ORA-01555 error, I'd like to suggest that the only
advantage you can gain by reducing the size or number of extents is saving
space. Is space a concern?
Summary: be careful and be alert for the incidence of the ORA-01555 error
message. Since only end-users become aware of it (i.e. it is not logged in
"alert.log" or in trace files), you might want to activate an AFTER
SERVERERROR database-event trigger (if you are running 8i or above) to log
occurrances of it. Otherwise, you'll need to be certain that end-users have
a reliable feedback mechanism to alert you to any changes in the frequency
of ORA-01555.
All in all, I think you should leave things alone, unless there is a
quantifiable problem.
Just some things to consider...
-Tim
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 12, 2002 5:59 AM
> hi, list friends:
> I am interested in that
topic:http://www.ioug.org/ioug_s/repository_pkg.doc?v_tech_content_dtl_phy_i
d=7666
> I am also considering whether too large rbs segment size does have adverse
impact on database performance. In my production (OLTP) i configured rbs to
be 1m*20 extents. But from v$rollstat and statspack, the average active size
is 1.5M. I am considering whether it is too large and shrink it to 128k*20
will help.(but hard to find out the performance gains as it is a big
system).
> If anyone in this list have IOUG membership, can you send me a copy? And i
hope friends have experience on this topic can share your experience
>
>
>
>
>
> Regards
> zhu chao
> Eachnet DBA
> 86-21-32174588-667
> [EMAIL PROTECTED]
> www.cnoug.org(Chinese Oracle User Group)
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: chao_ping
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
