Re: Number of extents related with performance?

Extent numbers don't really matter from the segment's point of view in ordinary, random usage. If you're picking up the odd record here, and the odder record there, do you care if the blocks those records in are adjacent and contiguous? Short answer: probably not.

But if you are doing full table scans (which is what you're doing when retrieving all rows), then of course having contiguous extents is going to offer some improvements (particularly since Oracle reads multiple blocks at a time during such scans -see db_block_multiread_count). But the requirement here is that the extents should be contiguous -again, it makes precious little difference whether you have a 1000 contiguous extents or 10, it's the contiguous nature of the extents that's the important thing.

That said, excessive number of extents can introduce performance degradation, but only because the data dictionary's segments are sized to expect around half a dozen extents, and any more than that introduces chaining on the relevant data dictionary cluster. There's also the matter that excessive numbers of extents require storage in more segment extent map blocks, which take more i/o to read, though I suspect you wouldn't notice it with only a few thousand extents.

My standard advice is: if you size a segment properly, 1 extent is good; try and keep it under half a dozen at worst; and if you are having a particularly bad day, don't allow the number of extents to exceed the old (Oracle 6) hard limits for extent numbers -i.e, 121 for 2K blocks, 255 for 4K blocks, 504 for 8K blocks and so on.

Regards
HJR "Jimmy" <anonymous_at_anonymous.com> wrote in message news:3A4C4F06.110C4CB6_at_anonymous.com...
> Hi all,
>
> I've read news and documents about the effect on the performance
> when the number of extents of object is increased. Most of it (even on
> Oracle Metalink) just mentioned that the performance is not related with
> the number of extents (except dropping and truncating tables). However,
> when I query the table that has about 800000 rows (about 200M storage
> spaces) with over 100 extents (extent size is 32K), it takes about 10
> second to return the number of rows. When I increase the extent size to
> 64M (number of extents is now 3), it just takes 2 seconds.
>
> Could anyone tell me is there any relationship between the performance
> and the number of extents of an object? (in my case, both tables are
> newly created, so no fragmentation) It seems that, as my case, decrease
> the number of extents can increase the query performance.
>
> Thanks,
> David
Received on Thu Dec 28 2000 - 15:38:12 CST