Re: Size of Extents

Peter Emde <emde_at_ascore.asgroup.de> wrote:

>When you want to estimate "initial" and "next" extents pay attention
>to the following:
 

>- To increase the performance of your database a table should allocate
> only ONE extent!
 

>- If you allocate more than 10 extents for one table the performance
> is going down.

This is not strictly true. Oracle retrieves information by the block, not by the extent, so having one extent of 200 blocks will be the same as having 10 extents of 20 blocks as far as retrieval is concerned, particularly in heavy load situations.

Usually, a re-build of a segment into one extent will show a performance gain, but the reason is often because the data blocks are fully packed, row fragmentation is eliminated, and the high water mark is set low.

Generally speaking, choose extent sizes which are multiples of the block size, and set PCTFREE and PCTUSED to chosen values, rather than accepting defaults to try and remove row fragmentation and chaining.

Two papers I read which changed my whole outlook on extent size (from which I got most of the above info!) and which I'd reccommend to anyone are:

"Avoiding database reorganization" - Craig A. Shallahamer "Oracle 7 Server Space Management" (Rev 1.4b) - Cary V. Millsap

both can be found at http://www.europa.com/~orapub

(or at least, they were there when I last looked!)

--
Simon Holt --  System Designer/DBA
** My opinions are not nescessarily those of my employers **
Britannia Zinc Ltd. Bristol, UK. (+44) 0117 980 2510