Re: Any Final Verdict on Nr. of Extents?

Visit website www.orapub.com, click Research/Papers and download paper 104 Oracle7 Server Space Management (oct-95) Cary V. Millsap.

It proves with scientific calculations how "bad" many extents are. What I have learned from this paper: make sure that all segments in a tablespace have initial_extent = next_extent and pctincrease =0. Organise tables and indexes in tablespaces with a proper (initial=next) extent size so that each table or index has about 100 extents at it's maximum size. Even 200 is not bad either but 100 is a good rule of thumb.

I've set up a large db where the tablespaces are called TAB_XS, TAB_S, TAB_M, TAB_L, TAB_XL, TAB_XXL and TAB_xxx for each very large tables where xxx is the abbreviation for the table from Oracle Designer. All tables in tablespace TAB_x have their indexes in IND_x. By ordering all the tables of the application by (the estimated) size you can make the split which tables have to be stored in wich tablespace and what extent size to assign to it.
In fact Oracle 8i new feature "locally managed tablespace" enforces this strategy.
After some time some reorganisations where necessary because of the actual size differs from the estimates too much - but that's live. So far I'am very satisfied with this strategy but it requires a fairly good size estimate is available before creating the objects.

Anton Buijs

Reza <reza_at_digital-dispatch.co.uk> schreef in berichtnieuws 999771696.4333.0.nnrp-09.d4f07ab4_at_news.demon.co.uk...
> Hi guys
> I've been having a hot debate with our new say DBAs, on effect of Number
> extents in an object like a table or index on performance.
> Basically I didnot agree to take a 7x24 system down for 6 hours to just
> reduce the number of extents on few huge tables and their indexes.
> I would personally believed few years ago that lower number of extents
will
> be better performance and even remember arguing this with some Oracle
Tutors
> in their lectures. Since then I have somehow revised my view due to
working
> with few production databases, and also hearing more from other DBAs in
> favor of ineffectiveness of Nr. of extents in Performance.
>
> Anyway I would appreciate if anyone could direct me to a definitive
article
> or somehow share their view on this so I can supprt my argument better or
at
> list to revise it again.
> I think somehow fragmentation problem might come to this argument as well,
> if the extents are not contiguse in the Objects table space. Any view on
> this aspect.
>
> May thanks for any help or feedback.
> Best Regards
> Reza Oskouie
>
>
>
Received on Thu Sep 06 2001 - 13:01:16 CDT