Re: 2 Databases - the same data - different size

In article <39a4c5ae$0$3337_at_businessnews.de.uu.net>,   "Steitz Adolf" <adolf.steitz_at_also.de> wrote:
> Hello,
>
> we're running OneWorld from J.D.Edwards.
>
> I have now 2 tablespaces with exactly the same tables and amount of
 data.
>
> The first tablespace (proddta) is 8GB.
> Some initial and next extent sizes from tables of this tablespaces are
> changed from that, what J.D.Edwards recommend.
>
> I made an export form proddta and an import into testdta. Size of
 testdta
> was also 8GB.
> Then I changed the inital and next extent size of every table back to
 that,
> what J.D.Edwards recommends.
>
> Then i made an export form testdta, dropped testdta, recreated
 testdta and
> made an import.
> Now, testdta has 3,3 GB!!!
>
> In my opinion, something is wrong with parameter for proddta.
> Otherwise I couldn't reduce the size from 8GB down to 3,3 GB.
>
> Can anyone tellme, where i have to look.
>
> every table has: pctfree = 10%
> pctused = 40%
> pctincrease = 0%
>
> WIN/NT, Oracle Version 8.0.5
>
> Thank you!!
>
> Adolf
>

You might be correct but you should not just assume production is over allocated because:

When you imp the data the resulting table blocks are packed up to pctfree while the tables you exported from may contain unused space that once held a now deleted row. With the pctused set at a wasteful value of 40 I would expect this.

Also any work/reporting type tables that hold 60,000 rows after one run, may only hold 30,000 rows after the next run and may have a lot of empty blocks below the high water mark which would be in a non-initial extent while on imp the hwm would reflect the actual then existing data.

Indeed some of your production tables may have been allocated enough space to hold 1 to N years worth of expected data growth. Adding a disk is not a cheap easy task because to add a disk you may need to also add a controller and you may have to buy another cabinet to hold the disk unit so allocating for the storage up-front is a legitimite action.

Unless you actually run enough batch processing to simulate the production activity you really can not reliably determine too much about your production system from looking at your freshly imported test system. There are too many behind the scenes factors to be considered.

Instead look at production. Calculate the space needs for each object based on actual row counts, average row size, expected growth, and usage patterns like insert only, monthly purge, truncated with daily batch etc... and calculate what sizing should be. This is a lot of work made more difficult by your application being a canned package, but I have worked with several such packages and have found that the vendor often has no clue to how much space their application will require at our site or what objects actually use the space.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.