on 01-10-2013 4:30 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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.
Oh, and I did make sure that the stats update ran on this table was a full 'Compute', not an 'Estimate'.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.