Re: local managed ts (indizes)

Richard Foote wrote:
>
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message
> news:3e8c1df5$0$21982$ed9e5944_at_reading.news.pipex.net...
> > "mkoster" <member14794_at_dbforums.com> wrote in message
> > news:2724210.1049368080_at_dbforums.com...
> > >
> > > hi,
> > >
> > > oracle 8.1.7
> > > aix 4.3.3 rs/6000
> > >
> > > on my testserver i have rebuild the indizes in an local-managed
> > > tablespace.
> > > this shrink the tablespace from 6,7 gb to 3,7 gb.
> > >
> > > what do vou mean about the performance, can i count on a better
> > > performance ???
> >
> >
> > see http://www.dbazine.com/jlewis8.html for a thorough discussion.
> >
> > One thing that has occurred to me that *may* result in better performance
> > and that I haven't seen discussed elsewhere is to do with extent sizes. In
> > general it makes sense to pick extent sizes that are equal to n*multiblock
> > read count where n is an integer. Choosing to use uniform extent sizes
> means
> > that you can enforce this rather than have odd extra reads here and there
> > when scanning the objects in the tablespace. However as with the other
> > performance benefits (that I as well as others have probably overstated in
> > the past) this is an indirect effect of choosing to use an LMT and should
> in
> > most cases be minimal.
> >
> > I haven't quantified this but I would be surprised if overall a well
> > configured LMT database outperformed a well configured DMT database by
> more
> > than say 5-10%. I'd be amazed if end-users noticed performance
> improvements
> > of less than 50% by contrast. The advantages lie mainly in ease of
> > administration and in avoiding costly reorganisations.
> >
>
> Hi Niall,
>
> I agree. In fact there shouldn't be *any* performance improvements at all
> between a well configured DMT vs. LMT tablespace. Assuming that indexes are
> primarily accessed via a unique or range scan, it's the efficiency of the
> logical structure of the index that's important, not the size or number of
> extents per se. If an index is frequently accessed via a FFS, then there
> would be some benefits in sizing the extents as you've described. Also when
> an index requires a new extent, the grabbing of the extent would be somewhat
> more efficient but this (should be) a somewhat rare event in a well
> configured DMT.
>
> It's the administration, sizing(maybe) and general DD maintenance operations
> that would feel the most benefits.
>
> Cheers
>
> Richard

agreed. Even with FFS, I remember playing with comparing full scans with best case (extent = physical IO) and worst case (extent = physical IO + 1 block) to see what the difference was.

On a file system based db, it was virtually zero so I'm assuming the file system (ufs) made some 'smart' caching decisions for me. I didn't try it on raw

connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"