Re: Rollback segments

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. --
>
> -----------------------------------------------------------
>
> 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