Re: Attentions DBAs - Does this make sense

In article <5ogshq$82t_at_blush.jps.net>, Murali Kazhipurath <murali_at_jps.net> writes
>
>I understand from an Oracle bulletin that there are two types of TbaleSpace
>fragmentation. The bulltin reads:
>
><<<< There are two types of fragmentation. The tablespace may have two pieces of
>free space but in between the two, there is a permanent object. There is no
>way to coalesce those two free extents. Another type of fragmentation occurs
>when the tablespace has two pieces of free space that are adjacent to each
>other, but they are separate. With this type of fragmentation, Oracle's SMON
>will coalesce the two extents into one large extent of free space. This
>automatic coalescing is new in Oracle7 and on. >>>
>
>I am in the process of setting up storage parameters for tables in a database.
> My idea is to allocate a large INITIAL EXTENT (based on the expected data)
> and NEXT EXTENT of size 1M. I am planning to keep the size of
>NEXT EXTENT 1M for all tables since this will aid the reusage of them when
>they are free. This might be an overkill for small tables, but it maintains
>consistency.
>
>Does this seem ok? Will this help avoid fragmentation to some extent? Please
>advice.
>
>Thanks,
>
>Murali
>
>
>--------------------------------------------------------------------------------
>---------------------------
>Murali Kazhipurath
>Lead Technical Consultant
>Kaustubam Systems, Inc. (Database and Internet Solutions Provider)
>Phone: (916) 773 1918
>--------------------------------------------------------------------------------
>---------------------------
>

This approach is recommended for large databases - data warehouses in particular. As you suggest, if objects are dropped their deallocated extents will all be the correct size for other extending objects to grab. Fragmentation will still occur, but the fragments will be more easily re-useable.

1M next extent is a lot for small tables, but they may never extend. Anyway, you can address this by having smaller objects in a separate tablespace, where the next extent is always 64K or something.

Hope this helps.

-- 
[Mark Wilson    mark_at_foulscreche.demon.co.uk    ]
[                                               ]
["Life is like a thin sliver of light           ]
[between two immensities of darkness"           ]
[Father Ted                                     ]