Unused Space In Tables

[Former Member]

Hello,

I would like to show how much unused space we have in our tables and indexes. The result should contain the following information:

Tablename Size (MB) Unused (MB) Unused (%)

-

---

ZTEST1 1.234,00 500,00 40

ZTESZ1~Z01 400,00 100,00 25

...

I have to do it with SAP tools and would like to know if there are already any reports (like RSORAVDV) which I can use.

Can you help?

Thanks,

Walter

[anindya_bose]

Below SQL command will provide you information about the Space wasted in for tables...

SQL> 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 <=50; -

---

> this will select top 50 tables with defragmentation.

[fidel_vales]

Hi,

The mentioned script is taken from SAP Note 821687 "FAQ: Space utilization and fragmentation in Oracle" (nice to mention the source)

The script is based on the statistics, in order to work "properly" the statistics must be "up to date" (the only case where "up to date" statistics are really necessary)

Unfortunately, the OP already mentioned that he does not has CBO statistics on all tables => bad, very bad as Oracle 10g do not support the rule base optimizer and not having statistics will cause dynamic sampling to be used.

I'd like to know why the need of calculate such "fragmentation", in normal cases the mentioned script would be enough (even with the mentioned limitation)

[Former Member]

Hello Michael,

thanks for your quick response.

Unfortunately I have to do the calculation for hundreds of our tables and not all of them do have CBO statistics. Also I have to do it from within SAP, so SQLplus is not an option.

Can you think of any other method to solve this challenge?

Take care,

Walter

[Former Member]

I see, for the indexes you can have a look at report RSORAISQ: [712098 - RSORAISQ: Index Storage Quality Management|https://service.sap.com/sap/bc/bsp/spn/sapnotes/index2.htm?numm=712098]

The storage quality is quite good to determine the fragmentation. I am not aware of any similar report for tables so far, anyone else here? But index fragmentation often comes from deleted rows which could mean the table has free space as well. But in tables the free space is reused pretty good, when there are new rows inserted.

Regards

Michael

Edit: btw, i suggest you look at the biggest tables only, i don't see any sense in looking at hundreds of tables...

[stefan_koehler]

Hello Walter,

> Unfortunately I have to do the calculation for hundreds of our tables and not all of them do have CBO statistics

What is the reason for the incomplete table statistics? I guess, you will use Oracle 9i or 10g.

> Also I have to do it from within SAP, so SQLplus is not an option.

No problem, just use "native sql" in your abap code.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/content.htm

You can automate the whole procedure with a small abap program.

Here is just another solution which may fit better to your question, but you will also have to write a little abap program with native sql:

SQL> create table ztest2(a number);
Table created.

..........
insert two values
..........

SQL> select distinct(a) from ztest2;
         A
----------
         1
         2

SQL> select count(a) from ztest2 where a = 1;
  COUNT(A)
----------
      1536

SQL> select count(a) from ztest2 where a = 2;
  COUNT(A)
----------
      1280

SQL> delete from ztest2 where a = 1;
1536 rows deleted.


SQL> select LAST_ANALYZED, AVG_ROW_LEN, NUM_ROWS from DBA_TABLES where TABLE_NAME = 'ZTEST2';
LAST_ANAL AVG_ROW_LEN   NUM_ROWS
--------- ----------- ----------

SQL> exec dbms_space.space_usage('TEST','ZTEST2', 'TABLE', :unf, :unfb, :fs1, :fs1b, :fs2, :fs2b, :fs3, :fs3b, :fs4, :fs4b, :full, :fullb);
SQL> print unf unfb fs4  fs4b fs3 fs3b fs2 fs2b fs1  fs1b full fullb;
       UNF         UNFB          FS4          FS4B          FS3          FS3B          FS2         FS2B          FS1         FS1B        FULL       FULLB
----------   ----------   ----------    ----------   ----------    ----------   ----------   ----------   ----------   ----------  ----------  ----------
         0            0            1          8192            0             0            4        32768            0            0           0           0

SQL> select LAST_ANALYZED, AVG_ROW_LEN, NUM_ROWS from DBA_TABLES where TABLE_NAME = 'ZTEST2';
LAST_ANAL AVG_ROW_LEN   NUM_ROWS
--------- ----------- ----------

This PL/SQL package only works with tablespaces that are LMT and using ASSM:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#CACIGFDB

Regards

Stefan

[Former Member]

Hello Walter,

Did you get a solution for this? I'm looking for the exact requirement, but, no luck with any information that I got. If this requirement is solved, kindly let us know the approach.

Thanks,

DK

[Former Member]

Hi

One easy way is to use ST05 -> Enter SQL Statement -> select * from ztest1 -> Explain

Then click on the table name, and you will see the statistics. Now you can calculate the real used size:

Number of rows * Average row length

Or directly in SQLplus:

SQL> select table_name, bytes, bytes - ( num_rows * avg_row_len ) "UNUSED_SPACE"
     from dba_tables where table_name = 'ZEST1';

Both methods need correct CBO stats to work.

Best regards

Michael