Friday, 20 May 2016

HANA Monitoring Handy SQL's

Monitoring Memory Usage

Used Memory
The total amount of memory in use by SAP HANA is referred to as its Used Memory. This is the most precise indicator of the amount of memory that the SAP HANA database uses at any time

When used:  To understand the current used memory in HANA when HANA alerts shows usage greater than licensed memory. Understanding memory usage by components will help in troubleshooting and perform necessary memory clean up actions.

Display the current size of the Used Memory; you can use the following SQL statement

SELECT
        ROUND(SUM(TOTAL_MEMORY_USED_SIZE/1024/1024/1024),
        2) AS "Used Memory GB"
FROM SYS.M_SERVICE_MEMORY;

Display current used memory for Column Store Tables

SELECT
        ROUND(SUM(MEMORY_SIZE_IN_TOTAL)/1024/1024) AS "Column Tables MB Used"
FROM M_CS_TABLES;

Display current memory used breakdown by Schema

SELECT
        SCHEMA_NAME AS "Schema",
        ROUND(SUM(MEMORY_SIZE_IN_TOTAL) /1024/1024) AS "MB Used"
FROM M_CS_TABLES
GROUP BY SCHEMA_NAME
ORDER BY "MB Used" DESC;

Display memory usage by components

SELECT
        host,
        component,
        sum(used_memory_size) used_mem_size
FROM PUBLIC.M_SERVICE_COMPONENT_MEMORY
group by host,
        component
ORDER BY sum(used_memory_size) desc;

Database resident
Resident memory is the physical memory actually in operational use by a process.

SELECT SUM(PHYSICAL_MEMORY_SIZE/1024/1024/1024) "Database Resident" FROM M_SERVICE_MEMORY;

Find the total resident on each node and physical memory size

SELECT
        HOST,
        ROUND(USED_PHYSICAL_MEMORY/1024/1024/1024,
        2) AS "Resident GB",
        ROUND((USED_PHYSICAL_MEMORY + FREE_PHYSICAL_MEMORY)/1024/1024/1024,
        2) AS "Physical Memory GB"
FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION;

Find total Resident

SELECT
        T1.HOST,
        (T1.USED_PHYSICAL_MEMORY + T2.SHARED_MEMORY_ALLOCATED_SIZE)/1024/1024/1024 "Total Resident"
FROM M_HOST_RESOURCE_UTILIZATION AS T1 JOIN (SELECT
        M_SERVICE_MEMORY.HOST,
        SUM(M_SERVICE_MEMORY.SHARED_MEMORY_ALLOCATED_SIZE) AS SHARED_MEMORY_ALLOCATED_SIZE
       FROM SYS.M_SERVICE_MEMORY
       GROUP BY M_SERVICE_MEMORY.HOST) AS T2 ON T2.HOST = T1.HOST;

Maximum peak used memory
SAP HANA database tracks the highest-ever value of Used Memory reached since the database was started. In fact, this is probably the single most significant memory indicator that you should monitor as an overall indicator of the total amount of memory required to operate the SAP HANA database over a long period of time.

SELECT
        ROUND(SUM("M")/1024/1024/1024,
       2) as "Max Peak Used Memory GB"
FROM (SELECT
        SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"
       FROM SYS.M_SERVICE_MEMORY
       UNION SELECT
        SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"
       FROM M_HEAP_MEMORY
       WHERE DEPTH = 0);

Peak used memory
SAP HANA maintains a special Used Memory indicator, called the Peak Used Memory. This is useful to keep track of the peak value (the maximum, or “high water mark”) of Used Memory over time. Here is how to read the Peak Used Memory:

SELECT
        ROUND(SUM("M")/1024/1024/1024,
       2) as "Peak Used Memory GB"
FROM (SELECT
        SUM(CODE_SIZE+SHARED_MEMORY_ALLOCATED_SIZE) AS "M"
       FROM SYS.M_SERVICE_MEMORY
       UNION SELECT
        SUM(INCLUSIVE_PEAK_ALLOCATION_SIZE) AS "M"
       FROM M_HEAP_MEMORY_RESET
       WHERE DEPTH = 0);

Memory usage in server

free –g | awk '/Mem:/ {print "Physical Memory: " $2 " GB."} /cache:/ {print "Resident: " $3 " GB."}'

Memory Cleanup: Forcing Garbage collector from Server
Login to Hana server -> open HDBAdmin.sh and navigate to Services -> Console
Select the node where the garbage collection to be triggered. Execute the below command

mm gc –f
The garbage collector will be triggered, and free up the memory. This will not unload the tables.

Resetting Monitoring Views
When Used: when testing a report or need to monitor the peak of memory usage by a SQL, monitor IO, memory objects throughput and statistics about garbage collection jobs. The below will allow to reset these statistics.

Memory allocator statistics
M_HEAP_MEMORY view contains information about memory consumption of various components in the system.
ALTER SYSTEM RESET MONITORING VIEW SYS.M_HEAP_MEMORY_RESET;

M_CONTEXT_MEMORY view contains information about memory consumption grouped by connections and/or users.

ALTER SYSTEM RESET MONITORING VIEW SYS.M_CONTEXT_MEMORY_RESET;

File access statistics
M_VOLUME_IO_STATISTICS_RESET view shows information about basic I/O operations on I/O subsystems (that is, paths).

ALTER SYSTEM RESET MONITORING VIEW SYS.M_VOLUME_IO_STATISTICS_RESET;

Memory object statistics
M_MEMORY_OBJECTS_RESET view provides information about the number and size of resources currently in the resource container and about the throughput of the resource container.

ALTER SYSTEM RESET MONITORING VIEW SYS.M_MEMORY_OBJECTS_RESET;

Garbage collection/history manager statistics
M_GARBAGE_COLLECTION_STATISTICS_RESET view shows various statistics about garbage collection jobs.

ALTERSYSTEMRESET MONITORING VIEW SYS.M_GARBAGE_COLLECTION_STATISTICS_RESET;

Schema/Tables Monitoring
Find Tables loaded into memory & delta records
When used: To see what tables are loaded to memory at any given time; If a report is running slow see if the table is loaded to memory though the tables goes on lazy loading it is a best practice to have the table loaded to memory.

SELECT
       LOADED,
       TABLE_NAME,
        RECORD_COUNT,
        RAW_RECORD_COUNT_IN_DELTA ,
        MEMORY_SIZE_IN_TOTAL,
        MEMORY_SIZE_IN_MAIN,
        MEMORY_SIZE_IN_DELTA
from M_CS_TABLES
where schema_name = 'SCHEMA'
order by RAW_RECORD_COUNT_IN_DELTA Desc

To drill down further and see what columns is not loaded /loaded please use below
Select top 100 LOADED,
HOST,
TABLE_NAME,
COLUMN_NAME,
MEMORY_SIZE_IN_TOTAL
from PUBLIC.M_CS_COLUMNS
WHERE SCHEMA_NAME = 'SCHEMA'
AND LOADED <> 'TRUE'

MERGE DELTA
See if there is delta to be merged. RAW_RECORD_COUNT_IN_DELTA will provide the delta count.
SELECT
        LOADED,
       TABLE_NAME,
        RECORD_COUNT,
        RAW_RECORD_COUNT_IN_DELTA ,
        MEMORY_SIZE_IN_TOTAL,
        MEMORY_SIZE_IN_MAIN,
        MEMORY_SIZE_IN_DELTA
from M_CS_TABLES
where schema_name = 'SCHEMA'
order by RAW_RECORD_COUNT_IN_DELTA Desc

Forcing delta Merge
UPDATE SCHEMA.COLUMN_STATISTICS MERGE DELTA INDEX;
Smart merge
UPDATE <table_name> MERGE DELTA INDEX WITH PARAMETERS ('SMART_MERGE'='ON')
Find Auto Merge On
select TABLE_NAME, AUTO_MERGE_ON from SYS.TABLES

Find Compression
When used: To see the uncompressed size and the compression ratio in HANA for the loaded tables.

SELECT top 100 "SCHEMA_NAME",
sum("DISTINCT_COUNT") RECORD_COUNT,
sum("MEMORY_SIZE_IN_TOTAL") COMPRESSED_SIZE,
sum("UNCOMPRESSED_SIZE") UNCOMPRESSED_SIZE,
(sum("UNCOMPRESSED_SIZE")/sum("MEMORY_SIZE_IN_TOTAL")) as COMPRESSION_RATIO,
100*(sum("UNCOMPRESSED_SIZE")/sum("MEMORY_SIZE_IN_TOTAL")) as COMPRESSION_PERCENTAGE
FROM "SYS"."M_CS_ALL_COLUMNS"
GROUP BY "SCHEMA_NAME"
having sum("UNCOMPRESSED_SIZE") >0
ORDER BY UNCOMPRESSED_SIZE DESC ;
To go on a detail level and identify what type of compression is applied on each column and the ratio please use below
select
        COLUMN_NAME,
        LOADED,
        COMPRESSION_TYPE,
        MEMORY_SIZE_IN_TOTAL,
        UNCOMPRESSED_SIZE,
        COMPRESSION_RATIO_IN_PERCENTAGE as COMPRESSION_FACTOR
from M_CS_COLUMNS
where schema_name = 'SCHEMA'

Forcing compression on a table
update SCHEMA.COLUMN_STATISTICS  with parameters ('OPTIMIZE_COMPRESSION' = 'TRUE');

Find which node is active
to find which node your session is connected to
SELECT
        HOST,
        PORT,
        CONNECTION_ID
FROM M_CONNECTIONS
WHERE OWN = 'TRUE';

Expensive Statements
Ensure the expensive statement trace is ON
When used: To troubleshoot a report failure or a sql failure and understand why it failed. Also to monitor the expensive sqls executed in HANA. Identify the ways for performance optimization.
Find expensive statements for errors
SELECT
       "HOST",
        "PORT",
        "CONNECTION_ID",
        "TRANSACTION_ID",
        "STATEMENT_ID",
        "DB_USER",
        "APP_USER",
        "START_TIME",
        "DURATION_MICROSEC",
        "OBJECT_NAME",
        "OPERATION",
        "RECORDS",
        "STATEMENT_STRING",
        "PARAMETERS",
        "ERROR_CODE",
        "ERROR_TEXT",
        "LOCK_WAIT_COUNT",
        "LOCK_WAIT_DURATION",
        "ALLOC_MEM_SIZE_ROWSTORE",
        "ALLOC_MEM_SIZE_COLSTORE",
        "MEMORY_SIZE",
        "REUSED_MEMORY_SIZE",
        "CPU_TIME"
FROM  "PUBLIC"."M_EXPENSIVE_STATEMENTS"
WHERE ERROR_CODE > 0
ORDER BY START_TIME DESC;
Finding expensive statements executed by User
SELECT
       "HOST",
        "PORT",
        "CONNECTION_ID",
        "TRANSACTION_ID",
        "STATEMENT_ID",
        "DB_USER",
        "APP_USER",
        "START_TIME",
        "DURATION_MICROSEC",
        "OBJECT_NAME",
        "OPERATION",
        "RECORDS",
        "STATEMENT_STRING",
        "PARAMETERS",
        "ERROR_CODE",
        "ERROR_TEXT",
        "LOCK_WAIT_COUNT",
        "LOCK_WAIT_DURATION",
        "ALLOC_MEM_SIZE_ROWSTORE",
        "ALLOC_MEM_SIZE_COLSTORE",
        "MEMORY_SIZE",
        "REUSED_MEMORY_SIZE",
        "CPU_TIME"
FROM  "PUBLIC"."M_EXPENSIVE_STATEMENTS"
WHERE STATEMENT_STRING LIKE '%NAIRV%'

CONNECTIONS
Find running connections

SELECT "HOST", "PORT", "CONNECTION_ID", "TRANSACTION_ID", "START_TIME", "IDLE_TIME", "CONNECTION_STATUS", "CLIENT_HOST", "CLIENT_IP", "CLIENT_PID", "USER_NAME", "CONNECTION_TYPE", "OWN", "IS_HISTORY_SAVED", "MEMORY_SIZE_PER_CONNECTION", "AUTO_COMMIT", "LAST_ACTION", "CURRENT_STATEMENT_ID", "CURRENT_OPERATOR_NAME", "FETCHED_RECORD_COUNT", "AFFECTED_RECORD_COUNT", "SENT_MESSAGE_SIZE", "SENT_MESSAGE_COUNT", "RECEIVED_MESSAGE_SIZE", "RECEIVED_MESSAGE_COUNT", "CREATOR_THREAD_ID", "CREATED_BY", "IS_ENCRYPTED", "END_TIME", "PARENT_CONNECTION_ID", "CLIENT_DISTRIBUTION_MODE", "LOGICAL_CONNECTION_ID", "CURRENT_SCHEMA_NAME", "CURRENT_THREAD_ID"
FROM "PUBLIC"."M_CONNECTIONS"
WHERE  CONNECTION_STATUS = 'RUNNING'
ORDER BY "START_TIME" DESC
 Resetting Connections

Find the connection
SELECT CONNECTION_ID, IDLE_TIME
FROM M_CONNECTIONS
WHERE CONNECTION_STATUS = 'IDLE' AND CONNECTION_TYPE = 'Remote'
  ORDER BY IDLE_TIME DESC
Disconnect Session

ALTER SYSTEM DISCONNECT SESSION '203927';
ALTER SYSTEM CANCEL SESSION '237048';
Find owners of objects

SELECT * FROM "PUBLIC"."OWNERSHIP" WHERE SCHEMA='SCHEMA'

Find Granted Privileges for Users
SELECT * FROM PUBLIC.GRANTED_PRIVILEGES
WHERE GRANTEE_TYPE = 'USER' AND GRANTOR = 'NAIRV'
PASSWORD Policy
Disable password policy on a user, this is used when you don’t want the policy to be applied on a user. This will set to lifetime.
ALTER USER USER DISABLE PASSWORD LIFETIME

Audit Policy
Configure
Enable global auditing
alter system alter configuration ('global.ini',
       'SYSTEM')
set ('auditingconfiguration',
       'global_auditing_state' ) = 'true' with reconfigure;
Set the auditing file type
       alter system alter configuration ('global.ini','SYSTEM')
       set ('auditingconfiguration'
       ,'default_audit_trail_type' ) = 'CSVTEXTFILE'
       with reconfigure;
aduit target path
       alter system alter configuration ('global.ini','SYSTEM')
       set ('auditingconfiguration'
       ,'default_audit_trail_path' ) = 'path'
        with reconfigure;
Find the policy implemented
Select * from public.audit_policies;

To enable/ disable global auditing
-- change the configuration for setting the audit
alter system alter configuration ('global.ini',
       'SYSTEM')
set ('auditingconfiguration',
       'global_auditing_state' ) = 'true' with reconfigure;

Add audit policy
CREATE AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 AUDITING SUCCESSFUL DROP TABLE LEVEL CRITICAL;
Policy enable/disable
ALTER AUDIT POLICY Audit_EDW_DM_DROPTABLE_H00 ENABLE;

CHECK AFL PAL FUNCTIONS ARE INSTALLED
SELECT * FROM SYS.AFL_FUNCTIONS WHERE PACKAGE_NAME='PAL';

Source: scn.sap.com