Re: Number of extents related with performance?

In our last gripping episode "Howard J. Rogers" <howardjr_at_www.com> wrote:
> 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
>
>

The old 'hard and fast' rules for extents are as follows:

2k block size         121 extents
4k block size         249 extents
8k block size         505 extents

There is some logic to the extent maximums; starting with the 2k block size (the minimum allowed) the next block size will contain (2*old extents) + 7:

4k max = 2(121) + 7 = 242 + 7 = 249
8k max = 2(249) + 7 = 498 + 7 = 505

Extrapolating to a 16K block size should provide a maximum of 1,017 extents (a value I have not verified, although the previous values posted have been validated).

This may not be a rigorous mathematical proof (it has been quite some time since I have dealt with Eigenvalues) but the calculation provides the correct number of expected extents given the 'power of 2' block sizes usually implemented.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com
http://www.deja.com/