Re: Auto Extent Size

In article <388DEC9F.525FEED4_at_assigncorp.com>, Mohamed Buhari <mbuhari_at_assigncorp.com> wrote:
> The default values are :
> initial extent = 5 time your oracle block size
> next extent = 5 times your oracle block size
> pctincrease = 50
> minextents = 2
> maxextents = 121
> It is not recomended to use oracles default rule. So your thumb
> rule is
> based on your own experience.Since your operation is insert
> intensive, I
> would suggest the following. Assume your tablesapce is 1GB
> CREATE TABLE .....
> STORAGE ( INITIAL 500K next 500 K minextents 10 maxextents
> UNLIMITED
> PCTINCREASE 1 PCTUSED 60 PCTFREE 15 FREELISTS 2 );
> If U have a muti CPU system, make FREELISTS to the number of CPU's
> to avoid
> freelist contention. Make pctincrease between 1 and 5 to enable
> automatic
> coalesing by smon.
> Mohamed Buhari
> oracle dba

Well, yes and no! The data and index TABLESPACE default storage parameter pctincrease should be set to 1, to allow automatic coalescing; almost NEVER set the pctincrease for an object (table, index) greater than zero (there is an exception, but will not go into that here). SMON does not coalesce TABLES, it only coalesces TABLESPACES. As far as initial and next extent sizes, these should be set to a multiple of 5 * data block size. In other words 5 data blocks, 500 data blocks or 25000 data blocks, depending on expected size of table or index. Using these multiples will reduce "swiss cheese" fragmentation in your tablespace, which can only be removed by a reorg. The arbitrary number of 500K that Mohamed suggests is only appropriate for a database with a block size of 2k, where 500K = 250 blocks, which is divisible by 5. The figure for PCTUSED should also be determined by an analysis of how many rows you can get per block so that you are not maintaining free lists for blocks that you can only get 1 or 2 rows into. Higher PCTUSED saves space and compacts the data nicely; but, you have to consider the cost in free list maintenance. The PCTFREE must be determined by how the application is using the data. If it is just going to be inserted and never updated, why waste space; use a PCTFREE of 1. On the other hand, if many initially null columns are later going to be updated with data, a PCTFREE of 15 may not be enough and you will end up with chained rows (a real performance hit). Adding additional FREELISTS will not buy you anything unless you have multiple transactions doing concurrent inserting on the same table. To sum up, it is very hard to recommend appropriate storage settings to someone without knowing the answers to all the variables. I would be very wary of such recommendations if the right questions haven't been asked.

hth

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!