Re: Size of Extents

In article <5451oo$86u_at_soap.news.pipex.net> jb85_at_dial.pipex.com (Simon) writes:
>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.

I'm sure the fellow was referring to the likelihood that if the extents are added 10 blocks at a time, there will be fragmentation. In such a case, it would not be the same, especially if you have a number of full table scans that don't happen often enough to take advantage of cacheing.

A couple of systems I DBA on are extremely and unpredictably volatile, and the only answer to that is to not even try to predict the extent usage, just exp/imp the things when someone complains (luckily, that someone is usually me, and these are not what you would normally think of as production systems).

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

-- 
Joel Garry               joelga_at_rossinc.com               Compuserve 70661,1534
These are my opinions, not necessarily those of Ross Systems, Inc.   <> <>
%DCL-W-SOFTONEDGEDONTPUSH, Software On Edge - Don't Push.            \ V /
panic: ifree: freeing free inodes...                                   O