Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback Segment Philosophy

RE: Rollback Segment Philosophy

From: Kevin Lange <klange_at_ppoone.com>
Date: Tue, 20 Jul 2004 13:13:31 -0500
Message-ID: <ED1256BD4F253C44B1627B2D365A334F0528FB4C@ppoone1.ppoone.com>


Thanks for the info Dan.

Most of what you said is how I have always set things up as well.

We started out with 9 rollback segments at 20 megs each. With this setup we did not have any 'snapshot too old' errors or issues. Our system was mostly many users running fast transactions. It could handle everything.

As the buisness started to grow we had to increased the rollbacks to 16 and their size to 40 megs due to running out of space on the rollback segments.

Once again our buisness has grown. Instead of just users running fast transactions we also have nightly edi load processes that mass-insert a lot of data. We are again starting to have rollback segments running out of space.

So, instead of simply throwing more space and more rollbacks and larger rollbacks at the problem I thought I would ask you all what you thought of it.

-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink_at_Sun.COM] Sent: Tuesday, July 20, 2004 1:05 PM
To: oracle-l_at_freelists.org
Subject: Re: Rollback Segment Philosophy

Kevin,

"I Undo Therefore I AUM" ;)

What size and number are the rollback segments and how large is the tablespace?
Is there problematic sql that is causing these problems?

Let's assume that the sql is good and it is a space-related issue. You need to allocate more space to rollback segments. (Don't you just love my keen grasp of the obvious?). Where to allocate more space is the key. Basic approach is to size your rollback segments as a mirror of your transaction. If the transactions are many and small, use many, small rollback segments. If the transactions are few and large, use few and large rollback segments. As a baseline, I have used 20 rollback segments each sized about 10 megs to start.

Contrary to popular opinion, I set OPTIMAL so that a rogue transaction or two don't have lingering impact.

In terms of operation, if you see header waits in v$rollstat, you need to add rollback segments. This means that too many transactions were trying to access the structures in the undo header at the same time.

You might want to peruse my papers on undo at www.optimaldba.com/library.html and Tim Gorman's "Cats, Dogs and ORA-1555s" at
www.evdbt.com.

Regards,
Daniel Fink

Kevin Lange wrote:
> Evening;
> Is there 'accepted' philosophy on Rollback Segments and how many and how
> big they should be ?
>
> We are running an 8.1.7.4 database on Solaris 5.9. The database is a
hybrid
> Transactional with some long running processes.
>
> We have always made it a point to have a limited number of rollback
segments
> using a limited amount of space. But, the issue of no space no longer
> applies.
>
> Since we are starting to get rollback issues due to running out of space
on
> our rollback segments we have decided to reopen our analysis of our
rollback
> design.
>
> I was just wondering if any of you would mind sharing your philosophy on
> Rollbacks .
>
> Thanks
>
> Kevin
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Jul 20 2004 - 13:10:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US