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';
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
No comments:
Post a Comment