SPACE and Memory information using SQL statement
Some SQL statement consolidated to execute the following details for DB running on HANA.
1. GENERAL INFORMATION – eg DB name, OS User name, Instance name and number, Support Packages, Hosts etc.
2. PATCH HISTORY
3. SPACE INFORMATION
4. MEMORY INFORMATION
_________________________________________________________________________
Select ‘GENERAL INFORMATION:’ NAME,
‘ ‘ VALUE
FROM
DUMMY
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘Database type’,
‘HANA’
FROM
DUMMY
)
UNION ALL
( SELECT
‘Evaluation time’,
TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY/MM/DD HH24:MI:SS’)
FROM
DUMMY
)
UNION ALL
( SELECT
‘Evaluation user’,
CURRENT_USER
FROM
DUMMY
)
UNION ALL
( SELECT
‘OS user’,
VALUE || ‘ (‘ || COUNT(*) || ‘ host’ || CASE WHEN COUNT(*) = 1 THEN ” ELSE ‘s’ END || ‘)’
FROM
M_HOST_INFORMATION
WHERE
KEY = ‘os_user’
GROUP BY
VALUE
ORDER BY
VALUE
)
UNION ALL
( SELECT
‘Startup time’,
TO_CHAR(START_TIME, ‘YYYY/MM/DD HH24:MI:SS’)
FROM
M_DATABASE
)
UNION ALL
( SELECT
‘ ‘,
TO_CHAR(TO_TIMESTAMP(SUBSTR(VALUE, 1, 19), ‘YYYY-MM-DD HH24:MI:SS’), ‘YYYY/MM/DD
HH24:MI:SS’) || ‘ (‘ || HOST || ‘)’
FROM
M_HOST_INFORMATION
WHERE
KEY = ‘start_time’
ORDER BY
HOST
)
UNION ALL
( SELECT
‘Database name’,
DATABASE_NAME
FROM
M_DATABASE
)
UNION ALL
( SELECT
‘Instance name’,
VALUE
FROM
M_SYSTEM_OVERVIEW
WHERE
NAME = ‘Instance ID’
)
UNION ALL
( SELECT
‘Instance number’,
VALUE
FROM
M_SYSTEM_OVERVIEW
WHERE
SECTION = ‘System’ AND
NAME = ‘Instance Number’
)
UNION ALL
( SELECT
‘OS Name’,
VALUE
FROM
M_HOST_Information
WHERE
Key=’os_name’
)
UNION ALL
( SELECT
‘Support package stack’,
‘SPS’ || TO_CHAR(CASE
WHEN REVISION >= 90 THEN 9
WHEN REVISION BETWEEN 80 AND 89 THEN 8
WHEN REVISION BETWEEN 70 AND 79 THEN 7
WHEN REVISION BETWEEN 60 AND 69 THEN 6
WHEN REVISION BETWEEN 45 AND 59 THEN 5
WHEN REVISION BETWEEN 28 AND 44 THEN 4
WHEN REVISION BETWEEN 20 AND 27 THEN 3
WHEN REVISION BETWEEN 12 AND 19 THEN 2
WHEN REVISION BETWEEN 1 AND 11 THEN 1
END)
FROM
( SELECT
SUBSTR(VALUE, LOCATE(VALUE, ‘.’, 1, 2) + 1, LOCATE(VALUE, ‘.’, 1, 3) – LOCATE(VALUE, ‘.’,
1, 2) – 1) REVISION
FROM
M_SYSTEM_OVERVIEW
WHERE
SECTION = ‘System’ AND
NAME = ‘Version’
)
)
UNION ALL
( SELECT
‘Number of hosts’,
TO_CHAR(COUNT (DISTINCT HOST))
FROM
M_SERVICES
)
UNION ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER BY COORDINATOR_TYPE, HOST), 1, ‘Host role’, ‘ ‘),
RPAD(COORDINATOR_TYPE, 8) || ‘ (‘ || HOST || ‘)’
FROM
M_SERVICES
WHERE
SERVICE_NAME = ‘indexserver’
ORDER BY
COORDINATOR_TYPE,
HOST
)
UNION ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER BY HOST), 1, ‘Host directory’, ‘ ‘),
VALUE || ‘ (‘ || HOST || ‘)’
FROM
M_HOST_INFORMATION
WHERE
KEY = ‘sap_retrieval_path’
ORDER BY
HOST
)
UNION ALL
( SELECT
‘Timezone’,
VALUE || ‘ (‘ || COUNT(*) || ‘ host’ || CASE WHEN COUNT(*) = 1 THEN ” ELSE ‘s’ END || ‘)’
FROM
M_HOST_INFORMATION
WHERE
KEY = ‘timezone_name’
GROUP BY
VALUE
ORDER BY
VALUE
)
UNION ALL
( SELECT
‘Database log mode’,
VALUE || ‘ (default)’
FROM
M_INIFILE_CONTENTS
WHERE
SECTION = ‘persistence’ AND
KEY = ‘log_mode’ AND
LAYER_NAME = ‘DEFAULT’ AND
NOT EXISTS ( SELECT * FROM M_INIFILE_CONTENTS WHERE SECTION = ‘persistence’ AND
KEY = ‘log_mode’ AND LAYER_NAME = ‘SYSTEM’ )
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘PATCH HISTORY:’,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
TO_CHAR(INSTALL_TIME, ‘YYYY/MM/DD HH24:MI:SS’),
VERSION
FROM
M_DATABASE_HISTORY
ORDER BY
INSTALL_TIME DESC
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘FEATURE INFORMATION:’,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘Database log mode’,
VALUE || ‘ (‘ || MAP(LAYER_NAME, ‘HOST’, HOST, ‘SYSTEM’, ‘system wide’) || ‘)’
FROM
M_INIFILE_CONTENTS
WHERE
SECTION = ‘persistence’ AND
KEY = ‘log_mode’ AND
LAYER_NAME IN (‘HOST’, ‘SYSTEM’)
)
UNION ALL
( SELECT
‘Automatic log backup’,
VALUE
FROM
M_INIFILE_CONTENTS
WHERE
SECTION = ‘persistence’ AND
KEY = ‘enable_auto_log_backup’
)
UNION ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER BY REPLICATION_PATH DESC), 1, ‘Replication’, ‘ ‘),
IFNULL(REPLICATION_PATH, ‘No’)
FROM
( SELECT DISTINCT
REPLICATION_MODE || ‘ (‘ || SITE_NAME || ‘ -> ‘ || SECONDARY_SITE_NAME || ‘)’
REPLICATION_PATH
FROM
DUMMY LEFT OUTER JOIN
M_SERVICE_REPLICATION ON
1 = 1
WHERE
REPLICATION_MODE != ”
)
ORDER BY
REPLICATION_PATH DESC
)
UNION ALL
( SELECT /* SAP Note 2014148 */
‘Query result cache’,
MAX(VALUE)
FROM
M_INIFILE_CONTENTS
WHERE
FILE_NAME = ‘indexserver.ini’ AND
SECTION = ‘cache’ AND
KEY = ‘resultcache_enabled’
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘SPACE INFORMATION:’,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘Database size on disk (GB)’,
Value
FROM
M_SYSTEM_OVERVIEW
Where
Section = ‘Disk’ and Name = ‘Data’
)
UNION ALL
( SELECT
‘Log size on disk (GB)’,
Value
FROM
M_SYSTEM_OVERVIEW
Where
Section = ‘Disk’ and Name = ‘Log’
)
UNION ALL
( SELECT
‘Data backup size (GB)’,
LPAD(TO_DECIMAL(SUM(BACKUP_SIZE) / 1024 / 1024 / 1024, 12, 2), 13)
FROM
( SELECT
BACKUP_ID
FROM
M_BACKUP_CATALOG
WHERE
UTC_START_TIME =
( SELECT
MAX(UTC_START_TIME) START_TIME
FROM
M_BACKUP_CATALOG
WHERE
ENTRY_TYPE_NAME = ‘complete data backup’ AND
STATE_NAME = ‘successful’
)
) MB,
M_BACKUP_CATALOG_FILES B
WHERE
B.BACKUP_ID = MB.BACKUP_ID
)
UNION ALL
( SELECT
MAP(ROW_NUMBER () OVER (ORDER BY HOST), 1, ‘Row store size total (GB)’, ‘ ‘),
LPAD(TO_DECIMAL(SUM(ALLOCATED_SIZE) / 1024 / 1024 / 1024, 12, 2), 13) || ‘ (‘ || HOST || ‘)’
FROM
M_RS_MEMORY
GROUP BY
HOST
ORDER BY
HOST
)
UNION ALL
( SELECT
‘Column store size total (GB)’,
LPAD(TO_DECIMAL(SUM(TABLE_SIZE) / 1024 / 1024 / 1024, 12, 2), 13)
FROM
M_TABLES
WHERE
TABLE_TYPE = ‘COLUMN’
)
UNION ALL
( SELECT
‘Row store tables’,
LPAD(COUNT(*), 10) || ‘ (SAP schema: ‘ || LPAD(SUM(MAP(SUBSTR(SCHEMA_NAME, 1, 3), ‘SAP’,
1, 0)), 6) || ‘)’
FROM
TABLES
WHERE
TABLE_TYPE = ‘ROW’
)
UNION ALL
( SELECT
‘Column store tables’,
LPAD(COUNT(*), 10) || ‘ (SAP schema: ‘ || LPAD(SUM(MAP(SUBSTR(SCHEMA_NAME, 1, 3), ‘SAP’,
1, 0)), 6) || ‘)’
FROM
TABLES
WHERE
TABLE_TYPE = ‘COLUMN’
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘MEMORY INFORMATION:’,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘ ‘,
‘ ‘
FROM
DUMMY
)
UNION ALL
( SELECT
‘Memory’,
Value
From
M_SYSTEM_OVERVIEW
WHERE
SECTION = ‘Memory’ AND
NAME = ‘Memory’
)
UNION ALL
( SELECT
‘Resident Memory used’,
LPAD(TO_DECIMAL(SUM(USED_PHYSICAL_MEMORY) / 1024 / 1024 / 1024), 5)
FROM
M_HOST_RESOURCE_UTILIZATION
)
UNION ALL
( SELECT
‘HANA instance memory (used)’,
LPAD(TO_DECIMAL(INSTANCE_TOTAL_MEMORY_USED_SIZE / 1024 / 1024 / 1024), 5)
FROM
M_HOST_RESOURCE_UTILIZATION
ORDER BY
HOST
)