## Question and Answer

## You Asked

Hi Tom

During my exercise to find the space used by a table 'X' I find huge difference of sizes between the following 2 methods:

TAble X has the following

INitial Extent :65K

PCT_FREE :ZERO

MIN_EXTENTS : 1

X is in a Tablespace that is : Extent Management Local AUtoAllocate

When I issue the following to calculate the size of a table:

A)

select sum(bytes) from dba_segments where segment_type='TABLE'

And segment_name='x';

SUM(BYTES)

------------

6020923392 or approx 6GB

B) Your recommended way of computing allocated space:

compute sum of blocks on report

break on report

select extent_id, bytes, blocks

from user_extents

where segment_name = 'X'

and segment_type = 'TABLE';

The sum of blocks is reported to be:734976

select blocks, empty_blocks,

avg_space, num_freelist_blocks

from user_tables

where table_name = 'X';

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS

---------- ------------ ---------- -------------------

733845 0 0 0

db_block_size = 8192

734976* 8192= 6020923392

Which is similar to (A)

C)Now the following gives me a totally different number

After analyzing a sample of table x

select avg_row_len from user_tables where table_name='x';

avg_row_len

----------

100

Select count(*) from x;

-----------

110,049,060

So the total space used by the table X is:

100 * 110049060 =11 GB

In A and B I get 6GB of space used by table X but in C the total space is 11 GB. WHat is contributing to the 5GB . The overhead cannot be so huge?

Regards

TS

During my exercise to find the space used by a table 'X' I find huge difference of sizes between the following 2 methods:

TAble X has the following

INitial Extent :65K

PCT_FREE :ZERO

MIN_EXTENTS : 1

X is in a Tablespace that is : Extent Management Local AUtoAllocate

When I issue the following to calculate the size of a table:

A)

select sum(bytes) from dba_segments where segment_type='TABLE'

And segment_name='x';

SUM(BYTES)

------------

6020923392 or approx 6GB

B) Your recommended way of computing allocated space:

compute sum of blocks on report

break on report

select extent_id, bytes, blocks

from user_extents

where segment_name = 'X'

and segment_type = 'TABLE';

The sum of blocks is reported to be:734976

select blocks, empty_blocks,

avg_space, num_freelist_blocks

from user_tables

where table_name = 'X';

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS

---------- ------------ ---------- -------------------

733845 0 0 0

db_block_size = 8192

734976* 8192= 6020923392

Which is similar to (A)

C)Now the following gives me a totally different number

After analyzing a sample of table x

select avg_row_len from user_tables where table_name='x';

avg_row_len

----------

100

Select count(*) from x;

-----------

110,049,060

So the total space used by the table X is:

100 * 110049060 =11 GB

In A and B I get 6GB of space used by table X but in C the total space is 11 GB. WHat is contributing to the 5GB . The overhead cannot be so huge?

Regards

TS

## and Tom said...

An average row length of 100 is the DEFAULT row length.

I'm going to guess -- since you didn't really say -- that you are gathering stats with method-opt => null, which then sets the default row length.

You need to gather stats on at least one column for dbms_stats to set the avg row length to a non-default value:

ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user,'T',method_opt=>null);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select avg_row_len from user_tables where table_name = 'T';

AVG_ROW_LEN

-----------

100

ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user,'T');

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select avg_row_len from user_tables where table_name = 'T';

AVG_ROW_LEN

-----------

6

ops$tkyte@ORA920>

And average row length would be an EXCESSIVELY poor predictor of actual space used. The only way to do it is look at the blocks, that is all that matters.

I'm going to guess -- since you didn't really say -- that you are gathering stats with method-opt => null, which then sets the default row length.

You need to gather stats on at least one column for dbms_stats to set the avg row length to a non-default value:

ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user,'T',method_opt=>null);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select avg_row_len from user_tables where table_name = 'T';

AVG_ROW_LEN

-----------

100

ops$tkyte@ORA920> exec dbms_stats.gather_table_stats( user,'T');

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select avg_row_len from user_tables where table_name = 'T';

AVG_ROW_LEN

-----------

6

ops$tkyte@ORA920>

And average row length would be an EXCESSIVELY poor predictor of actual space used. The only way to do it is look at the blocks, that is all that matters.

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

You are right about the method opt. It was null.