Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: [Q] Rollback segments; Small vs. large extents.
On Fri, 20 Dec 1996 09:22:27 +0100, Dana Stockler <stockler_at_ingdata.no> wrote:
>Seasons greeting to all,
>
>I have been having some problems with the rollback segments on
>the database we use for software development. The transactions
>here vary in size from 'petit' to 'humongous'.
>
>I have implemented what I consider to be a good mix of small and
>large segments. The small segments have an extent size of 100K
>and the large segments have an extent size of 1M.
>
>I have been tempted to increase the extent sizes to 1M and 5M,
>but I'm not sure of the tradeoffs involved and I don't want to
>find out the hard way.
>
>I have read a number of books and articles, and the subject of
>optimal rollback segment extent size is still rather murky for me.
>
>Question 1.
>What are the advantages of using small rollback segment extents?
>
>Question 2.
>What are the advantages of using large rollback segment extents?
>
>Question 3.
>Is there any reason why I shouldn't just create all rollback
>segments with the same large (say 5M) extent size?
>
>Thank you in advance for any and all input.
>
>Regards,
>Dana
You can also assign large transactions to specific rollback segments with 'SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment_name' as the first statement in a transaction. Read only transactions can be set no using a rollback segment at all with 'SET TRANSACTION READ ONLY' . See the SQL Language Reference Manual for details.
Sometimes SET TRANSACTION isn't feasible or practical so another tactic I have used when running large imports or other huge transactions is to offline all rollback segments except one with extents sized large enough to handle the transaction. Unfortunately, rollback segment management isn't yet dynamic enough to preclude active management of the segments. Received on Thu Dec 26 1996 - 00:00:00 CST