While you’re developing any enterprise applications under HANA Server, you must have came across such tables which are not of use and we should delete to avoid memory consumption of a HANA server.But it can’t be performed without any validation which will cause major issues to performance of web applications developed.
Hence, We have taken following parameters into consideration while listing out the the table names.
- Last Updated Time or the time when any DML operation performed on that table
- Memory Count
- Record Count
- Where used dependencies
List of predefined tables in HANA used for listing.
- M_TABLES
- M_CS_TABLES
- M_CS_ALL_COLUMNS
- “SYS”.”OBJECT_DEPENDENCIES”
- M_CE_CALCSCENARIOS
Here is the list of Queries which we wrote to identify whether the table is used or not
1.Below is the query to list the unwanted/unused tables. Based on the requirement related to memory/rowcount/last modify time , you could make changes to query.
SELECT t1.table_name,t1.record_count,t2.memory_size_in_total,t3.last_modify_time FROM
M_TABLES as t1
INNER JOIN M_CS_TABLES as t2 ON t1.table_name= t2.table_name
INNER JOIN M_TABLE_STATISTICS as t3 ON t2.table_name=t3.table_name ;
2.Here is one more query which may help you to get whether the table is used or not . These query may not give perfect result because it provides runtime data for every column based tables
SELECT t1.table_name,t1.RecentModifiedColumnCount,t2.TotalColumnCount,t1.LA_Year,t1.LA_Month,t1.LA_Day,
CASE
WHEN ((t1.LA_Year IS null) AND (t1.LA_Month IS null) AND (t1.LA_Day IS null) AND (t1.RecentModifiedColumnCount=t2.TotalColumnCount)) THEN ‘Unused’
WHEN ((t1.LA_Year IS NOT null) AND (t1.LA_Month IS NOT null) AND (t1.LA_Day IS NOT null) AND (t1.RecentModifiedColumnCount!=t2.TotalColumnCount)) THEN ‘Used’
END AS TableStatus
FROM (
SELECT TABLE_NAME,COUNT(COLUMN_NAME) AS RecentModifiedColumnCount, EXTRACT(YEAR FROM Last_access_time) AS LA_Year, EXTRACT(MONTH FROM Last_access_time) AS LA_Month,
EXTRACT(DAY FROM Last_access_time) AS LA_Day FROM M_CS_ALL_COLUMNS
WHERE schema_name=’XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ /*Enter your schema name here*/
GROUP BY TABLE_NAME,EXTRACT(YEAR FROM Last_access_time),EXTRACT(MONTH FROM Last_access_time), EXTRACT(DAY FROM Last_access_time)
) AS t1
INNER JOIN
(SELECT table_name,COUNT(column_name) AS TotalColumnCount FROM M_CS_ALL_COLUMNS WHERE schema_name=’XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ /*Enter your schema name here*/
GROUP BY table_name) as t2
ON t1.table_name=t2.table_name;
3.Query to check where used list, You can also check where used list using Where used browser. Path: Right click on the table > Click on Where used Browser.
SELECT BASE_OBJECT_NAME,DEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE FROM “SYS”.”OBJECT_DEPENDENCIES”
WHERE BASE_SCHEMA_NAME = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’ /*Enter your schema name here*/;
4.Query to check memory consumption by calculation views (returns result in byte)
select SUM(memory_size) from M_CE_CALCSCENARIOS where memory_size!=’0′
Though there were no detailed procedures to do this work, I explored a lot of contents in sap help portal to do this. I guess following the above steps you could get solution in some hours, for which I had spent days to explore multiple tables and their columns.
