Re: Rollback Segment Size Testing.

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" <ORACLE-L_at_fatcity.com> 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
> chao_ping_at_vip.163.com
> www.cnoug.org(Chinese Oracle User Group)
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: chao_ping
> INET: chao_ping_at_vip.163.com
>
> 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: 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

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: 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).