Re: Percent Increase Behavior

From my original reply to a request and included in the response
>>

 , but the exact amount of space used depends  on the size of the freespace extent that is used to allocate the space for
 the extent and how much is left after subtracting the requested amount.  If the amount of space left in the free space extents is under 5 blocks it
 is usually added to your request.
<<

The only manual I have at home to check is the Oracle 7 concepts manual which describes extent allocation in Chaper 3 under 'How Extents Are Allocated for Segments' which is page 3-11 in my copy. Note sub-sections b and c which is where what I said came from plus it is my experience that this is how Oracle allocates space.

I believe that the mention of pctfree in the reply is a typo as pctfree is not directly related to the allocation of additional extents and ignored it. I do not see where what you are saying really differs from my reply. The original question was on how space was actually allocated since the extents being used did not match the calculated size of the next extent.

Our shop has long ago changed pctincrease to zero for all application tables and indexes, and made the next extent equal to the initial extents to reduce freespace fragmentation. We do not use only one size for a tablespace though as was recommended by the Oracle instructor in the performance and tunning class I attended. This is not practical in our case and is not necessary as long as the large (say 100M) objects are separated from the small (say 10M) and below objects. But every shop is a little different.

One more point, if not now then in the future (sizing posts from Oracle employees have stated that ), the Oracle blocksize may affect the allocation of extents. On our (test) system using 4K Oracle blocks we can request and get 16K next sizes. On our 2K Oracle block system the 16K request usually comes back as 20K.
I think we sometimes get 16K back, probably when a free extent of exactly 16K exists. These are 7.0.15 and 7.1.6 systems.

Mark Powell -- The only advise that counts is the advise that you follow so follow your own advise Received on Sat Mar 08 1997 - 00:00:00 CST