Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 01, 2003 - 11:04 am UTC

Last updated: March 01, 2003 - 12:49 pm UTC

Version: 9201

Viewed 1000+ times

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

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.




Rating

  (1 rating)

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

Comments

A reader, March 01, 2003 - 12:49 pm UTC

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



More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library