Re: Looking for Rules on the Use (size) of ROLLBACK during UPDATE, DELETE, INSERT

Annie, the actual amount of rollback segment space needed by a transaction depends on the total number of rows affected by the DML, if there are indexes on these rows that are affected, the distribution of the rows in the table, and the commit frequency. The first time a block is changed the entire block is copied to rbs. The next update to the block results in vector information being copied to the rbs. So if on a table of 1000 blocks a DML operation that affected one row in every block with no commit, the entire table would be in rbs plus any affected index blocks. If on the other hand your operation on the same number of rows only affects rows in the first 100 blocks then your rbs area is much smaller.

Normally, I tell developers that they need to limit their transactions to a specific size in bytes for the data. This size corresponds to the min_extents * initial (which = next) or one pre-allocated rbs segment for my instances. On one production system I have set this to 4M and on another 50M. The sizing of rbs segments depends on the environment and the application. Your DBA should be sizing his/her segmengs based on the average transaction size and known batch jobs.

You may want to look into the set transaction use rollback segment seg_name command if you have a transaction that affects a lot more data than is normal for your application.

This got to be a little bit long, but I hope it answers your question.    

annie <annie_at_concept.fr> >>
I can't manage to calculate how many rbs space I need when I know the actions on my tables during DDL changes.

Example :
a table of 162+49MB extents, is supposed to be used up to 162 at least and 49MB at most.
If I apply an UPDATE on each row of this table (without any test), I would anticipate that RSB should use the useful space for a copy of the table and so, at least 162MB because I do not bother to calculate the real use of the second extent.

Some DBA told me once that he use the rule RBS=2*(updated object size) 4* for delete and 2* for an insert.

I do not understand the 2's or 4's and I experienced less than the size of the size of the first extent.

So, I am confused.
Could anybody enlights this for me ?
Thank you
Annie

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Wed Dec 30 1998 - 16:13:03 CST