Re: Rollback segments

"Iain Wiseman" <iwiseman_at_g8labs.co.nz> wrote:
>Is there any way to measure the size of transactions. Basically
 our system
>is up and running and I need to verify whether the sizing of
 the database.If
>I could turn a monitor on this would be most helpful.
>
>I have loaded the expected number of records into a test DB and
 calculated
>the storage requirements on this basis for the tablespaces.
>
>There is a data index and audit tablespace. Where audit is
 basically an
>audit trail of all change data using the new and old value for
 an
>insert/update/delete row trigger. This is basically record data
 seperated by
>pipes.
>
>e.g
>
>CUSTOMER|B |MR | IAN | WISEMAN
>CUSTOMER|A |MR | IAIN | WISEMAN|
>
>I mention this because on an AS/400 the database gives you this
 data free
>(You need to interpret the data ). It has so for commitment
 control. Could
>the rollback segments also provide this function.
>
>I am very new to ORACLE thanks for you patience.
>
>Iain
>"Mark D Powell" <mark.powellNOmaSPAM_at_eds.com.invalid> wrote in
 message
>news:17659d10.195cf99d_at_usw-ex0102-015.remarq.com...
>> "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
 on-
>> line 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. --
>>

I do not have time to go into a lot of detail this morning, but one way to attempt to measure the size of a transaction is to run it or its major sql via sqlplus as follows:

shrink all rbs segments [alter rollback segment segname shrink;] or create a new rbs segment with only 2 small extents.

view the v$rollstat information particularily the number of extents

set the transaction to use the selected rbs segment

run the transaction

view the v$rollstat information

This will give you an idea of how much rollback a transaction generates.

Another method is to pick a transaction and count how many rows it updates then using the dba_tables statistics for average row size calculate a number. You then add some bytes for the number of index rows affected by the table updates.

I hope this helps.

• 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 Thu Aug 10 2000 - 00:00:00 CDT