CODEWITHSUNDEEP
oracle-database
Shawn
Shawn

Reputation: 5260

Oracle calculate actual usage size and stats gathering

Each time an ETL job finished I have to collect the actual used space for each table (including its index spaces), as well as the row count.

The bytes column from user_segments and user_extents only indicates the space allocated. So I used the following to get each table size

  Dbms_Space.object_space_usage (
  object_owner    => v_owner,
  object_name     => c.name,
  object_type     => c.typ,
  sample_control  => NULL,
  space_used      => v_space_used_out,
  space_allocated => v_space_allocated_out,
  chain_pcent     => v_chain_pcent_out);
  v_space_used := v_space_used+v_space_used_out;
  v_space_allocated := v_space_allocated+v_space_allocated_out;

looping through each indexes and the table itself.

My question is before I run the above code is it necessary to run? 

EXEC DBMS_STATS.GATHER_TABLE_STATS(v_owner,v_table_name);

Why and/or why no?

Thanks,

Upvotes: 1

Views: 712

Answers (1)

Robert Dupuy
Robert Dupuy

Reputation: 857

It is not necessary to run EXEC DBMS_STATS.GATHER_TABLE_STATS(v_owner,v_table_name) ahead of running DBMS_SPACE.OBJECT_SPACE_USAGE

I ran Dbms_Space.object_space_usage, got the results. And then inserted a lot of data into a table, and ran Dbms_Space.object_space_usage a second time.

The results were updated to the new, larger values, without running GATHER_TABLE_STATS.

We know from Oracle DBMS_SPACE documentation that this package requires the analyze privilege. However, merely running OBJECT_SPACE_USAGE on a table, does not update the last_analyzed column of all_tables.

So from observation, DBMS_SPACE.OBJECT_SPACE_USAGE appears to do its own analysis of the table, getting correct space usage. It does not rely on updated stats being collected by DBMS_STATS nor does it update the stats.

Just to further prove the point:

SQL> EXEC DBMS_STATS.delete_table_stats('SOMEOWNER','SOMETABLE');

PL/SQL procedure successfully completed.


SELECT LAST_ANALYZED 
FROM ALL_TABLES 
WHERE OWNER = 'SOMEOWNER' AND TABLE_NAME = 'SOMETABLE';

LAST_ANALYZED
------------------


SQL> 
DECLARE
 su NUMBER;
 sa NUMBER;
 cp NUMBER;
BEGIN
  dbms_space.object_space_usage('SOMEOWNER', 'SOMETABLE', 'TABLE',
  NULL, su, sa, cp); 
  dbms_output.put_line('Space Used: ' || TO_CHAR(su));
  dbms_output.put_line('Space Allocated: ' || TO_CHAR(sa));
  dbms_output.put_line('Chained Percentage: ' || TO_CHAR(cp));
    END;
/

Space Used: 1055374677
Space Allocated: 1073741824
Chained Percentage: 0

Upvotes: 3

Related Questions