Re: number of extents question

Hi Jan,

Option 2.

Number of extents per se matters not. Even in Dictionary Managed tablespaces, the number of extents you are mentioning would not warrant the tables being rebuilt.

Now with Locally Managed Tablespace it matters even less as the DD overheads are no longer applicable. Potentially thousands of extents would have minimal performance impact.

However some issues such as objects affected by quotas or objects with automatic segment space management may cause you to not go too ballistic with extent numbers. Common practice is still to have tablespaces where objects with appropriate uniform sizes are limited to hundreds rather than thousands or tens of thousands of extents.

But would I rebuild a table if it had 10 or 20 or 100 extents. No.

One last point. Having one large extent may very well be the *worst* value to set a table to with respect to performance. Because if you don't take advantage of O/S striping, that one extent must then fit on one file on one device. Compare that to reading the same table in many extents, on many devices and parallel query will blow the one extent table away due to the evil of contention.

And one last last point. The *correct* answer to how many extents an object should have is "as many extents as you *plan* that object to have". Sounds a rather odd, non technical answer but if that's the number of extents you end up having, life is sweet.

Cheers

Richard

"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:alli1a$1ppeaj$1_at_ID-152732.news.dfncis.de...
> Does it or does it not
> matter (that is: decreasing my performance)
> having 1, 10 or 200 extents for one index segment (and BTW, same question
> for tables) ?
>
> I have read some books and posts, but I find two opposite opinions:
>
> 1. It does matter, so I should set INITIAL to how big it is now and set
 NEXT
> to what I expect how it may grow,
> and I should reorganize whenever a segment gets beyond 10 extents.
>
> 2. It does not matter at all, just keep segments same sized (e.g. 1M) to
> avoid fragmentation.
>
> I am asking this because right now I am reorganizing some 100 indexes, all
> of different initial and next size,
> and half on my way now
> I see that the maximum allocable extent of the index TS has *decreased*
 from
> 8M to 6M now.
> Even coalesce after every rebuild does not have much effect.
>
> So I am asking myself if it would not be better to reorganize indexes with
> initials that would *not* start with
> 80M, but to have initial and next of about 10M ...
>
> My system is:
> Oracle EE 8.1.7 on AIX 4.3.3
>
> Any comments and tips from are helpful.
> ThanX in advance,
>
> Jan
>
>
>
>
Received on Tue Sep 10 2002 - 19:41:24 CDT