Checking HANA Table sizes in SAP PO

[former_member303368]

Hello All,

We have SAP PO 7.4 deployed on SAP HANA SP 09, and the SAP PO is growing fast, and it grew by 200 GB over the last 2 days. The current size of the data volume is showing almost 500 GB just for the PO system. This is HANA tenant database installation.

The total memory used by this system show about 90 GB RAM.  However, I just don't how to get the list of the table with SIZE. I looked the view CS_M_TABLE, which is showing all the tables which are using the memory, but that does not still add up though. I need to get the list of all the physical table size so I can understand to see which table is growing fast and try to come with something that would explain why we are seeing about 500 GB of database size for a SAP Java PO System.

Thanks for all the help.

Kumar

[Former Member · Accepted solution]

Hello,

As very simple bit of SQL that you can adapt to your needs.

select table_name, round(table_size/1024/1024) as MB, table_type FROM SYS.M_TABLES where table_size/1024/1024 > 1000 order by table_size desc

select * from M_CS_TABLES where memory_size_in_total > 1000 order by memory_size_in_total desc

It's just a basic way of looking at things but at least it will give you all tables greater than 1GB.

I would imagine others will probably come up with something a bit more eloquent and perhaps better adapted to your needs.

Cheers,

A.

[former_member303368 · Accepted solution]

HI Amertjit,

Thanks for the quick response.  The first select statement seems to be only showing for only for non SAP Schema tables. I am looking to get the table size for SAP<SID>DB scehma, which is where all the XML is being stored. 

the M_CS_TABLES only showing the size which are in the memory. It does not show actual physical size of the tables.

Any other options?

[Former Member · Accepted solution]

Hello Kumar,

Attached to OSS note 1969700 - SQL statement collection for SAP HANA you will find a very useful collection of SQL scripts.

I think HANA_Tables_LargestTables is one that will interest you.

KR,

Amerjit

[Former Member · Accepted solution]

I agree to Amerjit that SQL: "HANA_Tables_LargestTables" (SAP Note 1969700) should be able to provide the best overview in terms of disk size vs. current memory size vs. maximum memory size and in terms of table vs. index vs LOB size. Be aware that the runtime of this SQL statement can be significant (a few minutes).

If the disk size is significantly larger than the memory size, you can check SAP Note 1999997 ("What are typical reasons for significant size differences in memory vs. on disk?"). The same SAP Note also quickly discussed the analysis SQL statement mentioned above in "How can the tables with the highest memory consumption be determined?".