| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback Segment Philosophy
Kevin,
The following is copied from one of my postings on AskTom.
They're my words, not Tom's, but he did seem to agree w/ me.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
1.) Determine the length of time of your longest running query.
2.) Determine the total volume of undo your database writes
in that length of time.
3.) That number should be the total size of the sum of
the OPTIMAL of all your rollback segments.
4.) Determine the largest number of concurrent transactions
you will encounter, and divide by 4. That's the number
of rollback segments you need.
5.) Take the number from step 3 and divide by number in
step 4, that's the size of OPTIMAL for your rollback segments. =20
You may want to put a little (say, 5%) padding
in the number from step 3, to avoid resize thrashing.
(If you inadvertently make OPTIMAL just slightly too small,
your rollback segments will shrink/wrap themselves to
death, and you'll get a flood of ORA-1555.)
The above should properly size your rollback segments=20 to avoid ORA-1555.
6.) What's the largest peak transaction volume your database needs to support? =20
7.) How many concurrent peak transactions do you expect? 8.) Multiply number from step 6 by number from step 7. 9.) Total size of your rollback tablespace should belarge enough to contain the sum of the OPTIMALs of all your rollback segments (from step 3) plus the number from step 8.
The above should allow you to properly size the rollback
tablespace to avoid ORA-1650.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
The above was written w/ 8i and before in mind. For 9i, it's a bit simpler. Just set UNDO_RETENTION to be greater than the time in #1 above and make sure the UNDO tablespace is large enough to contain all the undo generated in that that time.
General comments: I'm not a believer in a "BIG" rollback segment. Size the rollback to the database's needs and be done with it. =20 You should never have to SET TRANSACTION USE ROLLBACK SEGMENT ....
My opinion, based on my experience,
Hope it helps,
-Mark
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Post Hoc Ergo Propter Hoc"
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Lange
Sent: Tuesday, July 20, 2004 1:36 PM
To: 'oracle-l_at_freelists.org'
Subject: Rollback Segment Philosophy
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
-- 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:43:34 CDT
 |
 |