Calculate actual space usage for tables or indexes (dbms_space)

Tuesday, November 4, 2008

There are several ways to calculate actual size of tables or indexes. In my example I'll use procedure object_space_usage from DBMS_SPACE package.


dbms_space.object_space_usage (
object_owner IN VARCHAR2,
object_name IN VARCHAR2,
object_type IN VARCHAR2,
sample_control IN NUMBER,
space_used OUT NUMBER,
space_allocated OUT NUMBER,
chain_pcent OUT NUMBER,
partition_name IN VARCHAR2 DEFAULT NULL,
preserve_result IN BOOLEAN DEFAULT TRUE,
timeout_seconds IN NUMBER DEFAULT NULL);



I will create testing table with 1000000 rows using script from T. Kyte pages. On this table I will also create several indexes.



Script is shown below.
create table msutic.bigtab
as
select rownum id, a.*
from all_objects a
where 1=0
/

Table created.


alter table msutic.bigtab nologging;

Table altered.


declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append*/
into msutic.bigtab
select rownum, a.*
from all_objects a
where rownum <= 1000000;

l_cnt := sql%rowcount;

commit;

while (l_cnt < l_rows)
loop
insert /*+APPEND */ into msutic.bigtab
select rownum+l_cnt,
OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,
CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,
GENERATED,SECONDARY,NAMESPACE,EDITION_NAME
from msutic.bigtab
where rownum <= l_rows - l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/


alter table msutic.bigtab add constraint
bigble_pk primary key(id);


create index owner_idx on msutic.bigtab(owner);


create index name_idx on msutic.bigtab(object_name,subobject_name);


analyze table msutic.bigtab compute statistics;


select count(*) from msutic.bigtab;

COUNT(*)
----------
1000000
When I query size of segments:
select segment_name, segment_type, bytes/1024 size_K
from dba_segments
where segment_name = 'BIGTAB' or
segment_name in (select index_name from dba_indexes
where table_name='BIGTAB' and table_owner='MSUTIC');


SEGMENT_NAME    SEGMENT_TYPE           SIZE_K
--------------- ------------------ ----------
BIGBLE_PK       INDEX                   17408
OWNER_IDX       INDEX                   19456
NAME_IDX        INDEX                   41984
BIGTAB          TABLE                  131072
Now I'll delete 333333 rows and use procedure dbms_space.object_space_usage to calculate used space.
system@TEST11> delete from msutic.bigtab where mod(id,3)=0;

333333 rows deleted.

commit;

analyze table msutic.bigtab compute statistics;
I will use script from Morgan's Library:
set serveroutput on

DECLARE
su NUMBER;
sa NUMBER;
cp NUMBER;
BEGIN
dbms_space.object_space_usage('&segment_owner', '&segment_name', 
'&segment_type', NULL, su, sa, cp);

dbms_output.put_line('Space Used: ' || TO_CHAR(su)/1024||' K');
dbms_output.put_line('Space Allocated: ' || TO_CHAR(sa)/1024 ||' K');
dbms_output.put_line('Chained Percentage: ' || TO_CHAR(cp));
END;
/
Usage estimations: BIGTAB Space Used: 88236.12890625 K Space Allocated: 131072 K Chained Percentage: 0 BIGBLE_PK Space Used: 11857.6826171875 K Space Allocated: 17408 K Chained Percentage: 0 OWNER_IDX Space Used: 13180.302734375 K Space Allocated: 19456 K Chained Percentage: 0 NAME_IDX Space Used: 29232.814453125 K Space Allocated: 41984 K Chained Percentage: 0 Now I'll rebuild table and indexes just to check those estimations.
system@TEST11> alter table msutic.bigtab move;

Table altered.

system@TEST11> alter index msutic.bigble_pk rebuild;

Index altered.

system@TEST11> alter index system.owner_idx rebuild;

Index altered.

system@TEST11> alter index system.name_idx rebuild;

Index altered.
Now to check size of segments:
select segment_name, segment_type, bytes/1024 size_K
from dba_segments
where segment_name = 'BIGTAB' or
segment_name in (select index_name from dba_indexes
where table_name='BIGTAB' and table_owner='MSUTIC');

SEGMENT_NAME      SEGMENT_TYPE     SIZE_K
-------------------------- ------------------ ----------
BIGBLE_PK              INDEX                   12288
BIGTAB                 TABLE                   90112
OWNER_IDX              INDEX                   13312
NAME_IDX               INDEX                   28672
BIGTAB Actual: 90112k Estimation: 88236.12k Difference: -2,1 % BIGBLE_PK Actual: 12288k Estimation: 11857.68k Difference: -3.5% OWNER_IDX Actual: 13312k Estimation: 13180.30 Difference: -0,98% NAME_IDX Actual: 28672k Estimation: 29232.81 Difference: +1.01% Procedure DBMS_SPACE.OBJECT_SPACE_USAGE could be very useful for estimating actual space usage. Clean up:
system@TEST11> drop table msutic.bigtab purge;

Table dropped.


Share/Bookmark

4 comments:

  • s sanjai

    When i tried to get the query to calculate space utilized by tables, majority of people suggested "Select Table_Name, Num_Rows*Avg_Row_Len From User_Tables" . This value differs from the value returned by the procedure. So i am confused. Please let me know, which one would result correct space "utilized" by tables.

  • s sanjai

    Please help me. Thanks in advance..

  • Marko Sutic

    Hi sanjai,

    if you have fresh statistics gathered than I recommend also to use "Select Table_Name, Num_Rows*Avg_Row_Len From User_Tables". You will receive estimation of how many space is used.

    With dbms_space.object_space_usage you will also get estimation, not 100% accurate actual space utilized.

    Check "show_space" procedure from Tom Kyte: http://tinyurl.com/c3udpd3

    Regards,
    Marko

  • rachelle
    This comment has been removed by a blog administrator.
  • Post a Comment