Re: Rollback segments

"Iain Wiseman" <bibble_at_ibm.net> wrote:
> I have a database which updates states of vouchers. The
 database is quite
>large but mostly only queried for balances.There is also a
 voucher
>generation program that generate 100,000 vouchers.
>
>How do you decide the appropriate number and size of rollback
 segments?
>
>Iain
>

In general all rollback segments on your system must be large enough to hold all the rollback data generated by the largest transaction ran by your application plus the amount of space needed by the number of other average transactions that are likely to be assigned to the same rbs segment at the same time. You can limit the size of the rbs segments by specifying that transaction over a certain size are not supported and a commit must be made a part of the job when more than this amount of data is generated.

You can use the set transaction use rollback segment segname command to assign large transactions to specific large rollback segments to allow the majority of your rbs segments to be defined at a normal transaction size. Because Oracle does not provide a means to prevent the use of any on-line rbs segment except by specific jobs/users it may be desirable to keep the large segment(s) offline except when in use.

OLTP General rules of thumb:
Each extent in a rollback segment should be at least large enough to hold 4 average transactions X 2 (4 is the default number of transactions per rbs segment, 2 is a safty factor)

Each segment should have a minextents of 20

The next extent and initial extent size should match

Optimal should be set at minextents * next number of bytes

The maxents parameter needs to be at least large enough to allow the segment to extend enough to hold the largest transaction that your system must be able to support without procedures calling for using a set transaction statement.

You need at least 5 rbs segments, one large normally offline segment for special jobs and dba maintenance plus 4 regular online  segments for the system.

The number of normally online rbs segments is appoximately X where X >= the maximum value of (select count(*) from v$transaction divided by the number of transactions per rbs segment). example 12 transactions/4 = 3 segments. You may want to use the average number instead of max as rbs segments can normally handle the odd extra transaction since in an OLTP the length of the average transaction is very short. Some people make the mistake of basing the number of rbs segments off of concurrent sessions, v$session, intead of transactions, v$transactions.

• Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice. --

  ---

Got questions? Get answers over the phone at Keen.com. Up to 100 minutes free!
http://www.keen.com Received on Wed Aug 09 2000 - 00:00:00 CDT