Re: Attentions DBAs - Does this make sense

Murali Kazhipurath wrote:
> snip
> 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.
>

Yup. Not bad. I suggest also the following:

I wouldn't worry THAT much about fragmentation. It used to be a BAD thing in prior versions, but V7.1 onwards it's really not that dramatic (of course, EXCESSIVE fragmentation is always bad). A good rule of thumb is to load about 20% of the final data and check the sizes. You'll probably find that most of the data has gone into a small number of tables and indexes, the rest being lookups and so on. This of course depends on the design of the application. Then you could move the problem tables and indexes into their own tablespaces and leave the rest in a single separate tablespace (I'm slightly over-simplifying here, but I hope you get the principle rather than the exact words). The general principle is: isolate the problem cases where they can't interfere with the well behaved stuff and treat them one by one.

There will also probably be some tables that get created and dropped during the execution of the application(s). Put these ones in their own tablespace, you might have to fiddle application code for this as the CREATEs might need adjustment.

Then adjust the problem tables and indexes for an extrapolation of the 20% volume for their initial extent and set the NEXT extent at a value 10% of the initial, rounded off so that tables/indexes living in same tablespace get equal "chunks".

This should get you going without too much interference of "problem" cases into the rest of the database, which will allow you to treat the "problems" without having to go through hoops with the whole lot. Whatever you do, do not ignore fragmentation that occurs in indexes, this is much worse performance wise than table fragmentation!

HTH Cheers
Nuno Souto
nsouto_at_acay.com.au Received on Wed Jun 25 1997 - 00:00:00 CDT