Just to follow up on myself really, although the question referred to an
index tablespace (and Mark and Richard in particular) have hit the nail on
the head with issues for such tablespaces, I deliberately referred to
objects rather than indexes since the logic should apply pretty much
irrespective of the type of segment in the tablespace.
--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Mark D Powell" <Mark.Powell_at_eds.com> wrote in message
news:2687bb95.0304030651.33f44b3d_at_posting.google.com...
> "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 Fri Apr 04 2003 - 13:25:18 CST
