Re: 2 Databases - the same data - different size

Sorry, I did not see your reply sooner. When you export with the default of compress=y then on import Oracle attempts to allocate 100% of the allocated space for the object as the initial extent on the import. So if you have a table that once extented to 10 one megabyte extents then half of the data was deleted from it on import it is going to be given a 10 meg initial extent even though it would now fix in 5 one meg extents. If you precreate the objects and import with ignore=y then you get the space parameters you set, and if you change the object storage parameters before exporting then this will effect the space calculations Oracle performs for the import.

In article <39a535e6$0$7433_at_businessnews.de.uu.net>,   "Steitz Adolf" <adolf.steitz_at_also.de> wrote:
> Hello again,
>
> very good answer, but I still do not understand, because
>
> We made an export (COMPRESS = Y) from proddta and first an import to
> testdta.
>
> You wrote:
> > 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.
>
> Thats ok and i understand this.
> But, after the first import, testdta was the same size as proddta (8
 GB).
>
> Only when we changed the EXTENT sizes, made an export from and import
 to
> testdta, the size decreased to 3,3 GB.
>
> And that is, what i do not understand.
>
> Any idea?
>
> Thank you!
> Adolf
>
> Mark D Powell <markp7832_at_my-deja.com> schrieb in im Newsbeitrag:
> 8o39e7$hte$1_at_nnrp1.deja.com...
> > 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 --
> >

--
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.