Re: Sizing Tablespaces for Indexes

Stan Novinsky (stan_novinsky_at_jhuapl.edu) wrote:
> I have been reading several Oracle books on how to claculate
> the correct size for the index tablespace that will contain the
> indexes for my tables. It seems that each author has a
> differnet method. Is there a simple yet robust method
> of doing this as far as calculating the DB file size, initial,
> and next extents sizes ???

You can take two approches toward sizing objects. 1. You have enough disk space; so size so that you would

    never call an extent; ie. make the initial extent big     enough for holding all the data.
    With indexes you have to rebuild them once a while since     fragmentation is introduced.
    In this case calling an extent would be an exception.     So have the tablespace size = (sum of all initial extents +

          max(next-extent of all index) ) * {factor, eg. 1.5 }

2. Although having lesser extents gives better performance;

    Oracle would perform fine as long as extents are less than     five. Sizing would be tricky in this case though.     You are expecting the objects to have five extents in this     case; but not all the objects would have five extents.     so you can size the tablespace as:
    (sum(initial extents ) + sum( upto 5 extents -- take into

                                 account pct increase ).
                        ) * {factor}
        

If you have enough disk space, method 1 is simpler.

The tables that are dynamic -- make ample room in the tablespace for extents if disk space allows.

The tables that are static -- size them just enough for holding the data in initial extent and give some room for expansion. Group such indexes into a tablespace and make the tablespace big enough to hold the initial extents. MAKE SURE YOU WOULD NEVER CALL AN EXTENT IF U DO THIS. These are strictly my opinions and I would be very interested in how others would handle the scenerio.

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************