Re: How to choose initial and next in re-org?

hope_smith_at_hotmail.com wrote in message <7j914q$i7p$1_at_nnrp1.deja.com>...
>As a newbie, I am going to re-organize a database. As a step of re-org,
>I will recreate tablespaces. I just do not know what the initial and
>next storage size should be chosen so that each segment will be in one
>extent after re-org? Thanks in advance!
>
>Hope.
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

OK,

I think you're getting a bit confused about tablespace extents, segments and objects. Here is an all-from-memory late-Friday-afternoon, just worked 13 hours yesterday and didn't get much sleep, whistle-stop introduction to the wonderful world of Oracle storage, and with that disclaimer..... :-)

The storage parameters for a tablespace basically just serve as defaults for objects created in that tablespace. If you create a table or index that doesn't include a storage clause Oracle will use the one you specified when you created the tablespace - and if you didn't then you'll get the standard 10Kb/10Kb/50% setting for tables and 20Kb/20Kb/50% for indexes.

For this reason, some DBAs (with evil streaks) like to specify a default storage clause on the tablespace that is bigger than the tablespace itself, so that any object creation attempt without a storage clause immediately fails,
therby forcing users to think about how much storage they need.

The PCTINCREASE setting does have one other affect though, if it set to zero it will prevent SMON from coalescing the freespace within the tablespace, making it less likely that the space used by deleted data will be reused. You
can manually coalesce the space, or you can set up a OS job or an internal Oracle job to do it instead, but many DBAs just set it to 1 to minimize the chance of exponential table growth and retain automatic freespace coalescing.

Setting the INITIAL and NEXT storage clause to multiples of the same number also increases the chance that Oracle will be able to reuse freed space, whi ch
is why many use multiples of 8, such as 64Kb, 256Kb, 1Mb, 4Mb ect. - plus it's
easy to remember, and usually ties in nicely with the operating system's storage characteristics. Avoiding freespace fragmentation can be a issue with
systems that experience a high volume of updates and deletes.

Segments are the sum of the database extents, of whatever size and in whatever
tablespace, that are allocated to a single object. Whilst many DBAs like to have all of an object in a single extent, it is not as important to performance
as many believe, and in some cases a moderate number of mismatched extent sizes
can yield worse performance than a high number of evenly sized extents.

To calculate how much space you need for an object, Oracle provides a huge great
long calculation than encompasses block header, column headers, row sizes, row
counts and just about everything else. Nearly as accurate and a whole lot easier
is the method of counting the number of database blocks allocated to an object
(DBA_TABLES) and multiplying it by the DB_BLOCK_SIZE (V$PARAMETER). The resulting
number is a good approximation of how much space is required, including all of
the Oracle overhead.

Easier still is to do an export with compression of extents, and then use the
INDEXFILE option to pull the storage clauses out in ASCII form. Importing the
compressed Export file ensures that all imported objects end up in a single extent, and if memory serves the NEXT is set to 50% of INITIAL.

As with all Oracle parameters, the settings to use will depend on how much data
you want to store, and how that data will be used. Do you need an OLTP or a DSS?
and the hardware may influence your decision too.

... and probably having been of no help at all, I'm going home for the weekend.

HTH Graham Received on Fri Jun 04 1999 - 16:31:39 CDT