[HANA] (Embedded Statistics Service) HOST_LOAD_HISTORY_SERVICE View

- Reference : https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/8ed14f3ad2124d99a13475407acf2a01.html

SAP Help Portal

 

This view contains information only for the last 42 days. The collection interval is 3600 seconds.

-> 10초별 데이터 쌓임

Structure

Column nameData typeDescription

SNAPSHOT_ID	TIMESTAMP	Timestamp ID (UTC)
SERVER_TIMESTAMP	TIMESTAMP	Processing Timestamp (local time)
INDEX	VARCHAR(5000)	Index
ACTIVE_SQL_EXECUTOR_COUNT	BIGINT	Number of active SqlExecutors
ACTIVE_THREAD_COUNT	BIGINT	Number of active threads
BLOCKED_TRANSACTION_COUNT	BIGINT	Number of blocked SQL transactions
COMMIT_ID_RANGE	BIGINT	Range between newest and oldest active Commit ID
CONNECTION_COUNT	BIGINT	Number of open SQL connections
CPU	BIGINT	CPU used by Service
CS_MERGE_COUNT	BIGINT	Number of merge requests
CS_READ_COUNT	BIGINT	Number of read requests (select)
CS_UNLOAD_COUNT	BIGINT	Number of column unloads
CS_WRITE_COUNT	BIGINT	Number of write requests (insert,update,delete)
DATA_BACKUP_WRITE_SIZE	BIGINT	Bytes written to data backup
DATA_BACKUP_WRITE_TIME	BIGINT	Time used for writing to data backup
DATA_READ_SIZE	BIGINT	Bytes read from data area
DATA_READ_TIME	BIGINT	Time used for reading from data area
DATA_WRITE_SIZE	BIGINT	Bytes written to data area
DATA_WRITE_TIME	BIGINT	Time used for writing to data area
HANDLE_COUNT	BIGINT	Number of open handles
HOST	VARCHAR(64)	Host name
LOG_BACKUP_WRITE_SIZE	BIGINT	Bytes written to log backup
LOG_BACKUP_WRITE_TIME	BIGINT	Time used for writing to log backup
LOG_READ_SIZE	BIGINT	Bytes read from log area
LOG_READ_TIME	BIGINT	Time used for reading from log area
LOG_WRITE_SIZE	BIGINT	Bytes written to log area
LOG_WRITE_TIME	BIGINT	Time used for writing to log area
MEMORY_ALLOCATION_LIMIT	BIGINT	Memory allocation limit for Service
MEMORY_USED	BIGINT	Memory used by Service
MVCC_VERSION_COUNT	BIGINT	Number of active MVCC versions
PENDING_SESSION_COUNT	BIGINT	Number of pending requests
PING_TIME	BIGINT	Duration of Service ping request (THREAD_METHOD='__nsWatchdog'). This request includes the collection of service specific KPI's
PORT	INTEGER	Internal port
RECORD_LOCK_COUNT	BIGINT	Number of acquired record locks
STATEMENT_COUNT	BIGINT	Number of finished SQL statements
SWAP_IN	BIGINT	Bytes read from swap by Service (column 12(majflt) in /proc/<pid>/stat * sysconf(_SC_PAGESIZE))
SYSTEM_CPU	BIGINT	OS Kernel/System CPU used by Service
TIME	TIMESTAMP	KPI collection timestamp
TOTAL_SQL_EXECUTOR_COUNT	BIGINT	Total number of SqlExecutors
TOTAL_THREAD_COUNT	BIGINT	Total number of threads
TRANSACTION_COUNT	BIGINT	Number of open SQL transactions
TRANSACTION_ID_RANGE	BIGINT	Range between newest and oldest active Transaction ID
WAITING_SQL_EXECUTOR_COUNT	BIGINT	Number of waiting SqlExecutors
WAITING_THREAD_COUNT	BIGINT	Number of waiting threads

 

 

분당 데이터 수집 Query 

select
       to_varchar(TIME,'YYYYMMDD-HH24MI') TIMEMI
     , HOST
     , avg(ACTIEV_SQL_EXECUTOR_COUNT)
     , avg(ACTIVE_THREAD_COUNT)
     , avg(ADMISSION_CONTROL_ADMIT_COUNT)
     , avg(ADMISSION_CONTROL_QUEUE_SIZE)
     , avg(ADMISSION_CONTROL_REJECT_COUNT)
     , avg(ADMISSION_CONTROL_WAIT_TIME)
     , avg(BLOCKED_TRANSACTION_COUNT)
     , avg(COMMIT_ID_RANGE)
     , avg(CONNECTION_COUNT)
     , avg(CPU)
     , avg(CS_MERGE_COUNT)
     , avg(CS_READ_COUNT)
     , avg(CS_UNLOAD_COUNT)
     , avg(CS_WRITE_COUNT)
     , avg(DATA_BACKUP_WRITE_SIZE)
     , avg(DATA_BACKUP_WRITE_TIME)
     , avg(DATA_READ_SIZE)
     , avg(DATA_READ_TIME)
     , avg(DATA_WRITE_SIZE)
     , avg(DATA_WRITE_TIME)
     , avg(EXTERNAL_CONNECTION_COUNT)
     , avg(EXTERNAL_TRANSACTION_COUNT)
     , avg(HANDLE_COUNT)
     , avg(INTERNAL_CONNECTION_COUNT)
     , avg(INTERNAL_TRANSACTION_COUNT)
     , avg(LOG_BACKUP_WRITE_SIZE)
     , avg(LOG_BACKUP_WRITE_TIME)
     , avg(LOG_READ_SIZE)
     , avg(LOG_READ_TIME)
     , avg(LOG_WRITE_SIZE)
     , avg(LOG_WRITE_TIME)
     , avg(MEMORY_ALLOCATION_LIMIT)
     , avg(MEMORY_USED)
     , avg(MUTEX_COLLISION_COUNT)
     , avg(MVCC_VERSION_COUNT)
     , avg(PENDING_SESSION_COUNT)
     , avg(PING_TIME)
     , avg(READ_WRITE_LOCK_COLLISTION_COUNT)
     , avg(RECORD_LOCK_COUNT)
     , avg(STATEMENT_COUNT)
     , avg(SWAP_IN)
     , avg(SYSTEM_CPU)
     , avg(TOTAL_SQL_EXECUTOR_COUNT)
     , avg(TOTAL_THREAD_COUNT)
     , avg(TRANSACTION_COUNT)
     , avg(USER_TRANSACTION_COUNT)
     , avg(WAITING_SQL_EXECUTOR_COUNT)
     , avg(WAITING_THREAD_COUNT)
from 
      _SYS_STATISTICS.HOST_LOAD_HISTORY_SERVICE_BASE
where
      port = 30003
  and time between to_timestamp('20190708-120000','YYYYMMDD-HH24MISS') 
               and to_timestamp('20190708-130000','YYYYMMDD-HH24MISS')
group by
      to_varchar(TIME,'YYYYMMDD-HH24MI')
    , host;