Re: Estimating required space in autoallocated tablespace

Yash R. Ganthe wrote:

> Hi,
> Perhaps a DBA should think over this.
> I know the approximate number of records that will be loaded in the table
> through SQL*Loader. The tablespace is autoallocated, locally managed . Is
> it possible to estimate the space the entire data will take, before the
> loading is actually done? I am using Oracle 8.1.7. I also know the
> approximate space a single row will take. This is based on past
> performance with user managed tablespaces. I am aware that the sizes of
> extents allocated in autoallocated tablespaces is variable. That
> complicates my problem.
>
> Can somebody help?
>
> Yash

There are a lot of variables to take into account, but you could try this.

Say your row-length is 450 bytes. Say you expect a million rows. So total data to be loaded is 450000000 bytes.

Say you use 8K Oracle blocks, with 10 PCTFREE. That's 8192, less about 88 bytes for the header, less 10% for the PCTFREE space. That's 7293 useable bytes per block. So our 450000000 bytes of data could fit into 61704 blocks.

You then need to know what the autoallocate policy is, and that depends on your settings for initial and next for a segment, but by default it appears to be:

The first 16 extents will each be 64K.
Extents 17 to 79 will each be 1M. 
Extents 80 to 199 will each be 8M. 
Extent 200 is then created at 64M.

Calculators at the ready, that means the first 16 extents will occupy 1M.The next 64 extents will each be 1M each. And so on.

Our table requires 61704*8K=493632K = 49.3M of storage.

That means you'll get the first 16 extents, but then about 49 of the second set of 1M extents. In this case, you'd end up with very little wastage, since you'll have allocated about 50M to a 49.3M segment.

Not until you tip over a segment size of 1Gb will you start allocating 64M extents, so until that point, the worst that could happen would be that you'd allocate an 8M extent for the insertion of one additional row.

Regards
HJR Received on Sat Aug 31 2002 - 14:24:43 CDT