Re: Extent Management 101

On 8 Aug 2004 08:03:47 -0700, Michael <melliott42_at_yahoo.com> wrote:

> Hello,
>
> Though I have read about Extents in various books, as a new Oracle DBA
> using 8.17 I still do not grasp how I need to manage them.

You don't. Provided you create your tablespaces with the clause "extent management local", that's all you need to do.

IE,

create tablespace DATA
datafile 'c:\somewhere\data01.dbf' size 100m extent management local;

> 1. Basically, what are common Extent related issues and what are the
> actions as a DBA I need to know to address them?

In 8i, with locally managed extents, there are no common issues, and there are no actions needed to address them. In the older, dictionary-managed, tablespaces (still the default in 8i, so insist on that 'extent management local' clause to over-ride) you could suffer from 'tablespace fragmentation', whereby different segments allocated their space in differently-sized extents. Subsequent drops or moves of such segments would leave 'holes' of free space behind... that were themselves sized differently from the space allocations the segments still in existence might want to use. Hence, you ended up with a lot of pieces of free space that couldn't actually be used, and you therefore ended up wasting a lot of physical disk space. It wasn't a performance issue, merely a waste of space issue. Completely cured by using locally managed tablespaces.

> 2. Any absolute Do's and Dont's on managing Extents?

One absolute don't: don't. Don't expend effort on managing extents. Create locally managed tablespaces, and let Oracle do it all for you.

There is one variation to that advice: you can simply say 'extent management local' or you can say 'extent management local uniform size xM'. A lot of regulars here prefer the 'uniform size' flavour of Locally Managed Tablespace (LMT). However, there's very little in it, and for ease of management and peace of mind, the unadorned flavour (actually known as 'auto-allocated LMT') works just as well. If you insist on using the uniform size flavour, then simply stick to about 5 standard extent sizes: 64K, 1MB, 8MB, 64MB and 256MB are my suggestions. Do not try to have uniform allocations of 72K, and 183K and 1077K and 8952K and 23K and 113K and on and on... because then you are just behaving as though locally managed tablespaces had never been invented. The essence of LMT, of whatever flavour, is: leave well alone.

Regards
HJR Received on Sun Aug 08 2004 - 17:24:24 CDT