Re: Rollback segments

To the other two parts of the questions... ;-)

On 3 Sep 98 15:50:23 GMT, "Kevin Gray" <kevin.gray_at_andata.co.uk> wrote:

>
>Hi there
>
>I am writing some SQL scripts with Unix shell as well to do some very large
>updates (could be moving 3.5million records in insert/select combination).
>
>Can anyone provide answers to the following.
>
>1. Is it possible to have a single insert/select run without requiring a
>rollback segment to use?
>2. Is it possible to use multiple rollback segments for a single
>insert/select transaction?

This one is ... no. Sorry., But your next question is the best way to spread the load.

>3. Is it possible to use multiple files in a single rollback segment so
>that the rollback segment can be split over several files and, possibly,
>disks?

Actually, yes. Rollback segments are defined in tablespaces, and tablespaces can and usually -do- reside in several files, and over several disk devices. You could have the large rollback be over three devices, but I don't think you'll see parallel writes done to the rollback segment anyway. (Not sure on this point)

>
>Also, does anyone know exactly the best way to work out how much space to
>allow for rollback segments.
>

Depends on the type and size of transactions in the system. If the system is running a lot of short transactions, you want small rollbacks to eliminate contention. But for large transactions like the system you describe, that might need to hold 10000 records of information at a time (in one rollback, because each transaction only uses one), the best way to set it up is ...

# segments = 2 x max # transactions / ( block size / 32 )

size of OLTP segments = # bytes in transaction (fudged a little)

Then for large inserts, updates, ... set up a rollback 4 or five times the size of the small ones and use SET TRANSACTION USE ROLLBACK SEGMENT statement to force Oracle to use the larger rollback for "known" large transactions.  

• sooz

>Any help would be very much appreciated.
>
>Thanks.
>
>Regards
>
>Kevin Gray
>
>Kevin Gray
>Senior Consultant
>The Customer Engagement Company, UK
>Email: kevin.gray_at_h2engage.co.uk
>WWW: http://www.h2engage.co.uk/

sooz_at_pobox.com Received on Fri Sep 04 1998 - 00:00:00 CDT