Re: local managed ts (indizes)

"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 Received on Thu Apr 03 2003 - 06:35:25 CST