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.

To add to what Niall said assuming you updated the statistics so the CBO sees the more compacted indexes I would expect performance improvement only on SQL statements that perform index range scans over fairly large quantities of data or perform fast full index scans since the index blocks are now more densely packed. The majority of index access is normally single block IO so reorganing a full tablespace rarely brings big performance improvements, but it does mean you proably will not need to take any space managment action for a long time to come.

HTH -- Mark D Powell -- Received on Thu Apr 03 2003 - 08:51:46 CST