Re: index fragmentation

Absolutely right!! You should see how bad things get when you have a table with over 160,000 extents. This happened to me once by accident. There were simply too many trips to the UET$ table and the recursive SQL was killing me!

Thanks,
Brian

"Howard J. Rogers" wrote:
>
> 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
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================