Re: Rollback Segment Philosophy
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 20 Jul 2004 12:04:35 -0600
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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
