cancel
Showing results for 
Search instead for 
Did you mean: 

Why is the table free space reported so different between dba_tables and dbms_space?

Former Member
0 Kudos

Hi

I have been checking the amount of free space in various tables to see what we could reorg. I used the SQL statement in Note 821687 - FAQ: Space utilization and fragmentation in Oracle to do this, which reads entries from dba_tables.

However, this relies on up to date statistics having been ran on the tables. There are some tables where (for various reasons) we have locked the stats, so we cannot get up to date information on the free space using this method.

I then came across dbms_space which I believe doesn't require up to date stats, but rather does a table scan? I used the SQL statement in Note 1295200 - Oracle 10g or higher: Space statistics based on DBMS_SPACE.

However, the results seemed wildly different, so I ran this on some tables that we HAD ran up to date stats on. However, the results are still significantly different!

Using dba_tables:

TABLE_NAME    NUM_ROWS ROWLEN  BLOCKS        NET_MB      GROSS_MB     WASTED_MB TABLE_LAST_ANALYZED LONG_RAW_COLUMN_

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

EDI40                       74399346    230            72265613         17186        518817        501630                2012-12-28 12:49:38           153

i.e. 17GB actually used (NET_MB) of a 519GB table, 502GB wasted.

Using dbms_space:

GROSS_MB   NET_MB     UNUSED_MB  CHAIN_PERCENT

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

564607.00  349972.96  214634.04            14

i.e. 350GB of a 565GB table used, 215GB wasted.

There's a large difference there! Which one is right?!?! Why such a huge difference?

Thanks

Ross

Accepted Solutions (0)

Answers (2)

Answers (2)

ACE-SAP
Active Contributor
0 Kudos

As stated by Ross the difference can come from statistics, DBA tables info are refreshed by stats, DBMS package get up to date info from the segments.

Former Member
0 Kudos

Hi Oliver, I think you missed my point. Both the original question and the extra update were posted by me (Ross). I HAVE updated the stats on table EDI40 with a 'compute', so they are accurate.

So why do I still get such a huge difference? Which one is correct?

ACE-SAP
Active Contributor
0 Kudos

Sorry I did not checked the names

Wich DBMS_SPACE function are you using ?

There at least two for getting info on space

 

dbms_space.space_usage

dbms_space.unused_space

The difference could come from HWM (high water mark) didn't you delete some data from these tables already ?

From what I remember DBA tables count all blocks bellow HMW (formated ones) as used, the DBMS package are more accurate and do nout count free block bellow HWM.

I've been playing arround that subject and gave up because the "real free space" is too hard to manage, it is all a problem of blocks, free ones, free ones bellow HWM (which AFAIR are the only ones counted in DBA tables) and partially used/free ones.

Former Member
0 Kudos

No probs Olivier, thanks for the reply.

The first statement querying dba_tables is:

SELECT * FROM

(SELECT

    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,

    NUM_ROWS,

    AVG_ROW_LEN ROWLEN,

    BLOCKS,

    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,

    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *

      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,

    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -

      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"

  FROM DBA_TABLES

  WHERE

    NUM_ROWS IS NOT NULL AND

    OWNER LIKE 'SAP%' AND

    PARTITIONED = 'NO' AND

    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)

  ORDER BY 7 DESC)

WHERE ROWNUM <=20;

The second uses DBMS_SPACE.OBJECT_SPACE_USAGE_TBF:

SELECT

  TO_CHAR(SPACE_ALLOCATED / 1024 / 1024,

    999990.99) GROSS_MB,

  TO_CHAR(SPACE_USED / 1024 / 1024, 999990.99) NET_MB,

  TO_CHAR((SPACE_ALLOCATED - SPACE_USED) / 1024 / 1024,

    999990.99) UNUSED_MB,

  CHAIN_PCENT CHAIN_PERCENT

FROM

  TABLE(DBMS_SPACE.OBJECT_SPACE_USAGE_TBF('<owner>',

    '<table_name>', 'TABLE', NULL ));

Yes lots of data has been deleted from the table.

But surely if I've deleted data below the HMW I will be able to reclaim that space by doing a reorg?

In which case the dba_tables figure would be more realistic?


Thanks

Ross

ACE-SAP
Active Contributor
0 Kudos

After a massive deletion you need to reorg if you want the space you have freed to be available to other segments from the same tablespace.

Reorg will also "compact" the data avoiding to have an high number of almost empty blocks (not good for perf, many blocks to read).

I wouldn't trust the first query at all, trying to find (I would say guess) segment size from:

average row length * number of rows

is far too optimistic!

In the note SAP states that this query is made to:

determine the tables with the - in absolute figures - largest unused space

and not to compute the segment size.

Former Member
0 Kudos

Oh, and I did make sure that the stats update ran on this table was a full 'Compute', not an 'Estimate'.