Home » RDBMS Server » Server Utilities » Estimate tablespace growth while loading data using sqlldr (Oracle 10g WindowsXP)
Estimate tablespace growth while loading data using sqlldr [message #509904] Wed, 01 June 2011 10:06 Go to next message
catchmss
Messages: 5
Registered: December 2009
Junior Member
Hello All,

We load large amount of data into multiple tables using sqlldr.
Amount of data that we need to load varies according to the situation. We want to estimate the tablespace usage growth due to this data load, so we can verify/extend the tablespaces before the data load. Though, setting to autoextend will help in this case, We want to avoid extending the tablespace during sqlldr executing due to performance.

Our initial attempt was to note the tablespace size before and after executing the sqlldr and use the delta. But this delta was not consistent in different environments for the same amount of data. Different environments mean different oracle servers, different existing sizes of tablespaces, One data file Vs multiple data files etc.

So my question is , How do we reliably estimate how much tablespace we need for the given amount of data?

Any other valuable inputs regarding this topic?
Re: Estimate tablespace growth while loading data using sqlldr [message #509905 is a reply to message #509904] Wed, 01 June 2011 10:12 Go to previous messageGo to next message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>So my question is , How do we reliably estimate how much tablespace we need for the given amount of data?
>Any other valuable inputs regarding this topic?

External_file_size * Fudge_Factor = approximate_TS_consumed.
Above "assumes" NO indexes on tables being loaded.
INDEX size/growth needs to be separate calculation.
Re: Estimate tablespace growth while loading data using sqlldr [message #509909 is a reply to message #509905] Wed, 01 June 2011 10:14 Go to previous messageGo to next message
catchmss
Messages: 5
Registered: December 2009
Junior Member
BlackSwan wrote on Wed, 01 June 2011 11:12
>So my question is , How do we reliably estimate how much tablespace we need for the given amount of data?
>Any other valuable inputs regarding this topic?

External_file_size * Fudge_Factor = approximate_TS_consumed.
Above "assumes" NO indexes on tables being loaded.
INDEX size/growth needs to be separate calculation.


Thanks for the quick answer.

What is the Fudge_Factor?
Some of the tables have indexes. How do I estimate the INDEX size/growth?
Re: Estimate tablespace growth while loading data using sqlldr [message #509927 is a reply to message #509909] Wed, 01 June 2011 10:51 Go to previous message
BlackSwan
Messages: 22688
Registered: January 2009
Senior Member
>What is the Fudge_Factor?
It is a numerical value that you obtain from empirical testing.

>Some of the tables have indexes. How do I estimate the INDEX size/growth?
answer depends upon size/width of column(s) that comprise the index.
Previous Topic: sqlloader
Next Topic: move objects from one database to another
Goto Forum:
  


Current Time: Thu Aug 21 05:14:34 CDT 2014

Total time taken to generate the page: 0.15070 seconds