Re: index fragmentation

This old one keeps cropping up. Lots of extents introduces chaining on the UET$ table (assuming dictionary managed tablespaces), and *that's* the source of performance degradation. Really nothing to do with the amount of trips to the disk for the segment itself you have to do. And chaining starts happening after the sixth extent, unless you feel brave about customising sql.bsq before you create your database.

FET$ will also chain if there are more than about 500 free extents in the entire tablespace.

Locally Managed tablespaces are, as you say, immune from the problem.

Whether all that amounts to a 'good argument for a number such as 10' (or, in my case, 6), I couldn't say: the impact of the 7th extent or the 11th isn't going to be huge. But it is there.

Regards
HJR

--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------



"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:39C72DB8.D8D_at_yahoo.com...


> Roman Starek wrote:

> >

> > >

> > > but of course you were also going to quote that number of extents

> > > typically will have absolutely no impact on performance...

> > >

> >

> >         I dont think so. A have some reasons for my opinion.

> >

> > 1)      Big number of extents is problem for database. The more

> >         extents the bigger dictionary tables are. This brings

> >         big performance penalty specially for development

> >         enviroments. I know what is this about. Our developing

> >         enviroment contains 98000 objects in it.

>


> If you're using locally managed tablespaces, then you won't be hurting

> your data dictionary - they won't be there

>


> >

> > 2)      Big number of extents for index is not good for fast

> >         index scan.

> >

> As long as your extents are a multiple of the the maxium that the

> OS can return as a single unit of I/O, then there will not be a

> discernible difference.  For example if your max unit is (say) 128k,

> then

> 1000 extents of 1m will be no slower than 10 extents of 100m - you'll

> still

> end up with 8000 (1000m / 128k) trips to the disk.

>


> > 3)      Big number of extents usually mean that extents are

> >         distributed across all tablespace. This means that

> >         using index requires more work to be done through

> >         discs.

>


> This would be a legitimate concern in a single user environment, but

> typically you're going to have many users all going for index blocks all

> over

> the place, so the likelihood of ever getting contiguous access to a

> single

> disk is very low.

>


> >

> > Generally I think number of extents is not problem for small

> > number of extents < 10 maybe 20.

>


> Whilst I'm against having ridiculous numbers of extents simply in terms

> of manageability, I've yet to see a strong argument for choosing a

> number

> such as "10"

>


> >

> >                 Starous

>


> Comments inline.

>


> Cheers

> Connor

> --

> ===========================================

> Connor McDonald

> http://www.oracledba.co.uk

>


> We are born naked, wet and hungry...then things get worse