Consultes HANA

Tamany estimat de consum de memoria de les taules d’un esquema:

SELECT SCHEMA_NAME, TABLE_NAME, SUM(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL) Tamany, ROUND(SUM(ESTIMATED_MAX_MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "SIZE"
FROM M_CS_TABLES
WHERE SCHEMA_NAME = 'A20174_ECOALF_PRODL'
GROUP BY SCHEMA_NAME, TABLE_NAME
ORDER BY Tamany DESC

Tamany de les taules totals per base de dades (Font)

DO BEGIN 

DECLARE V_DBNAME varchar(20);
DECLARE V_RCT_SIZE float;
DECLARE V_RRT_SIZE float;

	SELECT database_name into V_DBNAME from "SYS"."M_DATABASES";
	SELECT round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024/1024, 3) into V_RCT_SIZE	FROM M_CS_TABLES;  
	SELECT round(SUM(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE) /1024/1024/1024, 3)  into V_RRT_SIZE	FROM M_RS_TABLES;  
	 
 SELECT V_DBNAME as DB_NAME,
 		V_RCT_SIZE as "Column table size",
 		V_RRT_SIZE as "Row table size",
        (V_RCT_SIZE + V_RRT_SIZE) as "All Tables GB Size"    
 FROM DUMMY;
 
END

Espai de memoria utilitzat pels esquemes d’una base de dades

DO BEGIN 

DECLARE V_DBNAME varchar(20);

	SELECT database_name into V_DBNAME from "SYS"."M_DATABASES";	

	select V_DBNAME as DB_NAME,
	       SCHEMA_NAME, 
		   sum("Column table main and delta GB Used") as "GB Used per column table"
	from
	(
	SELECT 
		SCHEMA_NAME, 
		round(sum(MEMORY_SIZE_IN_TOTAL) /1024/1024/1024, 3) AS "Column table main and delta GB Used" 
	FROM M_CS_TABLES
	GROUP BY SCHEMA_NAME 
	)
	GROUP BY SCHEMA_NAME
	ORDER BY "GB Used per column table" DESC;

END

Espai de memoria utilitzat per les taules (RowStore) dels esquemes d’una base de dades

DO BEGIN 

DECLARE V_DBNAME varchar(20);

	SELECT database_name into V_DBNAME from "SYS"."M_DATABASES";	

	select V_DBNAME as DB_NAME,
	       SCHEMA_NAME, 
		   "Row table GB Used"
	from
	(
	SELECT 
		SCHEMA_NAME, 
		round(SUM(USED_FIXED_PART_SIZE + USED_VARIABLE_PART_SIZE)/1024/1024/1024,3) AS "Row table GB Used"
	FROM M_RS_TABLES
	GROUP BY SCHEMA_NAME
	)
	ORDER BY SCHEMA_NAME, "Row table GB Used"  DESC;
		
END

Consum total de memoria de les taules (10 majors consumidors)

SELECT TOP 10 SCHEMA_NAME, "TABLE_NAME", round("MEMORY_SIZE_IN_TOTAL"/1024/1024,3) as Size_MB
FROM "SYS"."M_CS_TABLES" 
ORDER BY "MEMORY_SIZE_IN_TOTAL" DESC;