Re: Table and index Extent Sizing ?

Check out the oracle white paper (on technet) titled "How to stop defragmenting and
start living" (or something similar). It details an approach to sizing tables. It points out
that with less than 4096 extents, there is no significant performance degradasion.

Being from the old school that understood that everything HAD to be in 1 extent for
various reasons, I had difficulty believing this. So, I created a table with over 2000
extents (I can't remember the extact number).

With select, delete or update statements, I could NOT measure the difference.
In fact, using parallel query, I was able to see an improvement with these

statements. However, with insert statements, the is potentially a HUGE difference.
This will occur if the table is constantly extending. Space allocation is "expensive"
in oracle.

There are however, differing opinions here. I recently asked Rich Niemic of
TUSC software if multiple extents were really a problem. Here is his reply:

    Yes ... this is a problem... but, not as bad on all types of queries. It

    is mostly a cost on inserts when you need to get the next extent. If you

    have a large table that keeps extending ... then do an ALTER TABLE...NEXT,
    making the next extent large enough so that it stops growing extents. There

    are other costs, but minor costs if you are only querying the data.

The tests I did a few years ago bear out this answer. Make certain that you choose
an appropriate extent size. Tables should get a new extent on an infrequent basis.
I try to limit this to once per week. However, I don't get too excited if on occasion
it happens a coplu times in one day. Others say once per month or even once per year.
As long as it's not several times per hour, you'll likely not notice the difference.

Conclusion:

Whichever scheme you choose, there are trade offs. For my money, answer is to
stick with the Oracle white paper (or locally managed tablespaces with uniform
extent sizing). It simplifies maintenance and doesn't waste significant disk space.
Performance of inserts isn't as good, but if done properly, I seriously doubt you
can measure the difference with a stop watch.

yakub wrote:

> I have updated an algorythm that computes the initial and next extents
> with respect to space management of data and indexes . That is, I
> compute for the Space Available for Data (SAD) based on average
> rowsize, PctFree and blocksize.
>
> Question 1: Does anyone have an algorthym that can be used to compute
> the initial and next extents sizes?
> Question 2: Should one also strive to get all of the data into one
> extent?
>
> Please explain.
> Thanks,
> Yakub
  Received on Mon Feb 26 2001 - 16:38:09 CST