HANA DB 참조 쿼리
hana db monitoring handy sql
https://www.scribd.com/document/319167030/Hana-Monitoring-Handy-Sqls#download
관리 쿼리
-- TABLE List
select * from tables
select * from public.tables
select * from SYS.M_TABLES where table_name like 'DP_TC_PRM_EXCEL_TO_DB_TYPE%'
-- COLUMN 정보 확인
select * from table_columns where column_name = 'COMPANY_CODE'
select schema_name, table_name, column_name, NUMA_NODE_INDEXES from table_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
-- DB 계정 및 로그인 이력 확인
SELECT * FROM "SYS"."USERS"
WHERE USER_NAME LIKE '%A664074%'
-- GC Blocker 찾기
select *
from m_prepared_statements
where statement_id = (
select current_statement_id
from m_connections
where connection_id = (
select connection_id
from m_transactions
where min_mvcc_snapshot_timestamp = (
select min(Value)
from m_mvcc_tables
where name = 'MIN_SNAPSHOT_TS'
)
and connection_id > 0
)
)
-- GC Blocker 상세 찾기
select *
from m_session_context
where connection_id = (
select connection_id
from m_transactions
where min_mvcc_snapshot_timestamp = (
select min(Value)
from m_mvcc_tables
where name = 'MIN_SNAPSHOT_TS'
)
and connection_id > 0
)
다음 경우 추가 확인 필요
IDLE_TIME_SEC > 3600 Sec = 적절한 조치(Blocker 찾아서 확인 후 처리가 필요함
VERSION_COUNT > 1,000,000 = 시스템 전체적인 성능저하 발생
< 10,000 = 무시가능
MVCC Blocker Statement에 blocker를 찾지 못하면 MVCC blocker transaction으로 찾아야 함
GC Issue의 해결책
Query의 cancel 수행
ALTER SYSTEM CANCEL SESSION <the blocker connection ID>
Kill the connections
ALTER SYSTEM DISCONNECT SESSION 'CONNECTION_ID'
Connection ID를 찾지 못하거나 위의 명령이 효과가 없는 경우 Transaction kill 필요
hdbcons 'transaction cancel <the blocker transaction ID>'
Indexserver restart 또는 replaction tableover 수행
-- 시스템 뷰에서 열별 메모리 사용량 정보를 검색
select * from M_CS_COLUMNS where TABLE_NAME = 'CM_PUR_ORG_TYPE_MAPPING'
-- and SCHEMA_NAME='<SCHEMA_NAME>';
-- Load 측정 SQL - History
with raw as (
select a.timemi
, a.host
-- 1. Host
, a.host_cpu_util
, a.host_mem_resd_mb
, a.host_mem_tot_resd_mb
, a.host_mem_usd_mb
, a.host_mem_alloc_lim_mb
, a.host_mem_sz_mb
, a.host_swap_in_mb_sum
, a.host_swap_out_mb_sum
, a.host_disk_sz_mb
, a.host_disk_usd_mb
, a.host_nw_in_mb_sum
, a.host_nw_out_mb_sum
-- 2. Service
, b.cpu_util
, b.system_cpu_util
, b.used_mem_mb
, b.malloc_limmit_mb
, b.handle_cnt
, b.ping_tm_ms
, b.swap_in_mb_sum
-- 2.1 SQL
, b.conn_cnt
, b.int_conn_cnt
, b.ext_conn_cnt
, b.idl_conn_cnt
, b.trans_cnt
, b.int_trans_cnt
, b.ext_trans_cnt
, b.user_trans_cnt
, b.blocked_trans_cnt
, b.stmt_cnt
, b.stmt_per_sec
, b.mvcc_vers_cnt
, b.pending_sess_cnt
-- 2.11 Row Store
, b.record_lock_cnt
-- 2.12 Column Store
, b.cs_rd_cnt
, b.cs_wr_cnt
, b.cs_merge_cnt
, b.cs_unload_cnt
-- 2.13 Threads
, b.active_thread_cnt
, b.wait_thread_cnt
, b.tot_thread_cnt
, b.active_sqlexec_cnt
, b.wait_sqlexec_cnt
, b.tot_sqlexec_cnt
-- 2.2 Persistence
, b.data_wrsz_mb_per_sec
, b.data_wrtm_sec_per_sec
, b.log_wrsz_mb_per_sec
, b.log_wrtm_sec_per_sec
, b.data_rdsz_mb_per_sec
, b.data_rdtm_sec_per_sec
, b.log_rdsz_mb_per_sec
, b.log_rdtm_sec_per_sec
-- 2.21 Backup
, b.databackup_wrsz_mb_per_sec
, b.databackup_wrtm_sec_per_sec
, b.logbackup_wrsz_mb_per_sec
, b.logbackup_wrtm_sec_per_sec
-- 2.3 Synchronization Primitives
, b.mutex_collision_cnt
, b.rdwr_lock_collision_cnt
-- 2.4 Session Admission Control
, b.admctl_admit_cnt_sum
, b.admctl_reject_cnt_sum
, b.admctl_queue_cnt_sum
, b.admctl_wait_tm_sec_sum
from (
select host
, to_varchar(time,'YYYYMMDD-HH24MI') timemi
, avg(CPU ) host_cpu_util
, avg(MEMORY_RESIDENT )/1024/1024 host_mem_resd_mb
, avg(MEMORY_TOTAL_RESIDENT )/1024/1024 host_mem_tot_resd_mb
, avg(MEMORY_USED )/1024/1024 host_mem_usd_mb
, avg(MEMORY_ALLOCATION_LIMIT)/1024/1024 host_mem_alloc_lim_mb
, avg(MEMORY_SIZE )/1024/1024 host_mem_sz_mb
, avg(DISK_SIZE )/1024/1024 host_disk_sz_mb
, avg(DISK_USED )/1024/1024 host_disk_usd_mb
, sum(NETWORK_IN )/1024/1024 host_nw_in_mb_sum
, sum(NETWORK_OUT )/1024/1024 host_nw_out_mb_sum
, sum(SWAP_IN )/1024/1024 host_swap_in_mb_sum
, sum(SWAP_OUT )/1024/1024 host_swap_out_mb_sum
from m_load_history_host
--from _sys_statistics.host_load_history_host
where time between to_timestamp('20210809-120000','YYYYMMDD-HH24MISS') and to_timestamp('20210809-130000','YYYYMMDD-HH24MISS')
group by host, to_varchar(time,'YYYYMMDD-HH24MI')
) a inner join (
select host
, to_varchar(TIME,'YYYYMMDD-HH24MI') timemi
, avg(CPU ) cpu_util
, avg(SYSTEM_CPU ) system_cpu_util
, avg(MEMORY_USED )/1024/1024 used_mem_mb
, avg(MEMORY_ALLOCATION_LIMIT )/1024/1024 malloc_limmit_mb
, avg(HANDLE_COUNT ) handle_cnt
, avg(PING_TIME ) ping_tm_ms
, sum(SWAP_IN )/1024/1024 swap_in_mb_sum
, avg(CONNECTION_COUNT ) conn_cnt
, avg(INTERNAL_CONNECTION_COUNT ) int_conn_cnt
, avg(EXTERNAL_CONNECTION_COUNT ) ext_conn_cnt
, avg(IDLE_CONNECTION_COUNT ) idl_conn_cnt
, avg(TRANSACTION_COUNT ) trans_cnt
, avg(INTERNAL_TRANSACTION_COUNT) int_trans_cnt
, avg(EXTERNAL_TRANSACTION_COUNT) ext_trans_cnt
, avg(USER_TRANSACTION_COUNT ) user_trans_cnt
, avg(BLOCKED_TRANSACTION_COUNT ) blocked_trans_cnt
, sum(STATEMENT_COUNT ) stmt_cnt
, avg(MVCC_VERSION_COUNT ) mvcc_vers_cnt
, avg(PENDING_SESSION_COUNT ) pending_sess_cnt
, avg(RECORD_LOCK_COUNT ) record_lock_cnt
, avg(CS_MERGE_COUNT ) cs_merge_cnt
, avg(CS_READ_COUNT ) cs_rd_cnt
, avg(CS_UNLOAD_COUNT ) cs_unload_cnt
, avg(CS_WRITE_COUNT ) cs_wr_cnt
, avg(ACTIVE_THREAD_COUNT ) active_thread_cnt
, avg(WAITING_THREAD_COUNT ) wait_thread_cnt
, avg(TOTAL_THREAD_COUNT ) tot_thread_cnt
, avg(ACTIVE_SQL_EXECUTOR_COUNT ) active_sqlexec_cnt
, avg(WAITING_SQL_EXECUTOR_COUNT) wait_sqlexec_cnt
, avg(TOTAL_SQL_EXECUTOR_COUNT ) tot_sqlexec_cnt
, avg(DATA_WRITE_SIZE/interval_s)/1024/1024 data_wrsz_mb_per_sec
, avg(DATA_WRITE_TIME/interval_s)/1000000 data_wrtm_sec_per_sec
, avg(LOG_WRITE_SIZE /interval_s)/1024/1024 log_wrsz_mb_per_sec
, avg(LOG_WRITE_TIME /interval_s)/1000000 log_wrtm_sec_per_sec
, avg(DATA_READ_SIZE /interval_s)/1024/1024 data_rdsz_mb_per_sec
, avg(DATA_READ_TIME /interval_s)/1000000 data_rdtm_sec_per_sec
, avg(LOG_READ_SIZE /interval_s)/1024/1024 log_rdsz_mb_per_sec
, avg(LOG_READ_TIME /interval_s)/1000000 log_rdtm_sec_per_sec
, avg(DATA_BACKUP_WRITE_SIZE/interval_s)/1024/1024 databackup_wrsz_mb_per_sec
, avg(DATA_BACKUP_WRITE_TIME/interval_s)/1000000 databackup_wrtm_sec_per_sec
, avg(LOG_BACKUP_WRITE_SIZE /interval_s)/1024/1024 logbackup_wrsz_mb_per_sec
, avg(LOG_BACKUP_WRITE_TIME /interval_s)/1000000 logbackup_wrtm_sec_per_sec
, avg(MUTEX_COLLISION_COUNT ) mutex_collision_cnt
, avg(READ_WRITE_LOCK_COLLISION_COUNT) rdwr_lock_collision_cnt
, sum(ADMISSION_CONTROL_ADMIT_COUNT) admctl_admit_cnt_sum
, sum(ADMISSION_CONTROL_REJECT_COUNT) admctl_reject_cnt_sum
, avg(ADMISSION_CONTROL_QUEUE_SIZE ) admctl_queue_cnt_sum
, sum(ADMISSION_CONTROL_WAIT_TIME )/1000000 admctl_wait_tm_sec_sum
, sum(interval_s ) interval_sum
, avg(statement_count/interval_s) stmt_per_sec
from (
select a.*, NANO100_BETWEEN(LEAD(TIME, 1) OVER (PARTITION BY HOST, PORT ORDER BY TIME DESC), TIME) / 10000000 INTERVAL_S
from m_load_history_service a
--from _sys_statistics.host_load_history_service a
where port = 30003
and time between to_timestamp('20210809-120000','YYYYMMDD-HH24MISS') and to_timestamp('20210809-130000','YYYYMMDD-HH24MISS')
) a
group by host, to_varchar(TIME,'YYYYMMDD-HH24MI')
) b on a.timemi = b.timemi and a.host = b.host
order by a.host, a.timemi
)
select *
from raw
union all
select 'Min'
, host
, min(host_cpu_util )
, min(host_mem_resd_mb )
, min(host_mem_tot_resd_mb )
, min(host_mem_usd_mb )
, min(host_mem_alloc_lim_mb)
, min(host_mem_sz_mb )
, min(host_swap_in_mb_sum )
, min(host_swap_out_mb_sum )
, min(host_disk_sz_mb )
, min(host_disk_usd_mb )
, min(host_nw_in_mb_sum )
, min(host_nw_out_mb_sum )
, min(cpu_util )
, min(system_cpu_util )
, min(used_mem_mb )
, min(malloc_limmit_mb )
, min(handle_cnt )
, min(ping_tm_ms )
, min(swap_in_mb_sum )
, min(conn_cnt )
, min(int_conn_cnt )
, min(ext_conn_cnt )
, min(idl_conn_cnt )
, min(trans_cnt )
, min(int_trans_cnt )
, min(ext_trans_cnt )
, min(user_trans_cnt )
, min(blocked_trans_cnt )
, min(stmt_cnt )
, min(stmt_per_sec )
, min(mvcc_vers_cnt )
, min(pending_sess_cnt )
, min(record_lock_cnt )
, min(cs_rd_cnt )
, min(cs_wr_cnt )
, min(cs_merge_cnt )
, min(cs_unload_cnt )
, min(active_thread_cnt )
, min(wait_thread_cnt )
, min(tot_thread_cnt )
, min(active_sqlexec_cnt )
, min(wait_sqlexec_cnt )
, min(tot_sqlexec_cnt )
, min(data_wrsz_mb_per_sec )
, min(data_wrtm_sec_per_sec)
, min(log_wrsz_mb_per_sec )
, min(log_wrtm_sec_per_sec )
, min(data_rdsz_mb_per_sec )
, min(data_rdtm_sec_per_sec)
, min(log_rdsz_mb_per_sec )
, min(log_rdtm_sec_per_sec )
, min(databackup_wrsz_mb_per_sec )
, min(databackup_wrtm_sec_per_sec)
, min(logbackup_wrsz_mb_per_sec )
, min(logbackup_wrtm_sec_per_sec )
, min(mutex_collision_cnt )
, min(rdwr_lock_collision_cnt)
, min(admctl_admit_cnt_sum )
, min(admctl_reject_cnt_sum )
, min(admctl_queue_cnt_sum )
, min(admctl_wait_tm_sec_sum )
from raw
group by host
union all
select 'Average'
, host
, avg(host_cpu_util )
, avg(host_mem_resd_mb )
, avg(host_mem_tot_resd_mb )
, avg(host_mem_usd_mb )
, avg(host_mem_alloc_lim_mb)
, avg(host_mem_sz_mb )
, avg(host_swap_in_mb_sum )
, avg(host_swap_out_mb_sum )
, avg(host_disk_sz_mb )
, avg(host_disk_usd_mb )
, avg(host_nw_in_mb_sum )
, avg(host_nw_out_mb_sum )
, avg(cpu_util )
, avg(system_cpu_util )
, avg(used_mem_mb )
, avg(malloc_limmit_mb )
, avg(handle_cnt )
, avg(ping_tm_ms )
, avg(swap_in_mb_sum )
, avg(conn_cnt )
, avg(int_conn_cnt )
, avg(ext_conn_cnt )
, avg(idl_conn_cnt )
, avg(trans_cnt )
, avg(int_trans_cnt )
, avg(ext_trans_cnt )
, avg(user_trans_cnt )
, avg(blocked_trans_cnt )
, avg(stmt_cnt )
, avg(stmt_per_sec )
, avg(mvcc_vers_cnt )
, avg(pending_sess_cnt )
, avg(record_lock_cnt )
, avg(cs_rd_cnt )
, avg(cs_wr_cnt )
, avg(cs_merge_cnt )
, avg(cs_unload_cnt )
, avg(active_thread_cnt )
, avg(wait_thread_cnt )
, avg(tot_thread_cnt )
, avg(active_sqlexec_cnt )
, avg(wait_sqlexec_cnt )
, avg(tot_sqlexec_cnt )
, avg(data_wrsz_mb_per_sec )
, avg(data_wrtm_sec_per_sec)
, avg(log_wrsz_mb_per_sec )
, avg(log_wrtm_sec_per_sec )
, avg(data_rdsz_mb_per_sec )
, avg(data_rdtm_sec_per_sec)
, avg(log_rdsz_mb_per_sec )
, avg(log_rdtm_sec_per_sec )
, avg(databackup_wrsz_mb_per_sec )
, avg(databackup_wrtm_sec_per_sec)
, avg(logbackup_wrsz_mb_per_sec )
, avg(logbackup_wrtm_sec_per_sec )
, avg(mutex_collision_cnt )
, avg(rdwr_lock_collision_cnt)
, avg(admctl_admit_cnt_sum )
, avg(admctl_reject_cnt_sum )
, avg(admctl_queue_cnt_sum )
, avg(admctl_wait_tm_sec_sum )
from raw
group by host
union all
select 'Max'
, host
, max(host_cpu_util )
, max(host_mem_resd_mb )
, max(host_mem_tot_resd_mb )
, max(host_mem_usd_mb )
, max(host_mem_alloc_lim_mb)
, max(host_mem_sz_mb )
, max(host_swap_in_mb_sum )
, max(host_swap_out_mb_sum )
, max(host_disk_sz_mb )
, max(host_disk_usd_mb )
, max(host_nw_in_mb_sum )
, max(host_nw_out_mb_sum )
, max(cpu_util )
, max(system_cpu_util )
, max(used_mem_mb )
, max(malloc_limmit_mb )
, max(handle_cnt )
, max(ping_tm_ms )
, max(swap_in_mb_sum )
, max(conn_cnt )
, max(int_conn_cnt )
, max(ext_conn_cnt )
, max(idl_conn_cnt )
, max(trans_cnt )
, max(int_trans_cnt )
, max(ext_trans_cnt )
, max(user_trans_cnt )
, max(blocked_trans_cnt )
, max(stmt_cnt )
, max(stmt_per_sec )
, max(mvcc_vers_cnt )
, max(pending_sess_cnt )
, max(record_lock_cnt )
, max(cs_rd_cnt )
, max(cs_wr_cnt )
, max(cs_merge_cnt )
, max(cs_unload_cnt )
, max(active_thread_cnt )
, max(wait_thread_cnt )
, max(tot_thread_cnt )
, max(active_sqlexec_cnt )
, max(wait_sqlexec_cnt )
, max(tot_sqlexec_cnt )
, max(data_wrsz_mb_per_sec )
, max(data_wrtm_sec_per_sec)
, max(log_wrsz_mb_per_sec )
, max(log_wrtm_sec_per_sec )
, max(data_rdsz_mb_per_sec )
, max(data_rdtm_sec_per_sec)
, max(log_rdsz_mb_per_sec )
, max(log_rdtm_sec_per_sec )
, max(databackup_wrsz_mb_per_sec )
, max(databackup_wrtm_sec_per_sec)
, max(logbackup_wrsz_mb_per_sec )
, max(logbackup_wrtm_sec_per_sec )
, max(mutex_collision_cnt )
, max(rdwr_lock_collision_cnt)
, max(admctl_admit_cnt_sum )
, max(admctl_reject_cnt_sum )
, max(admctl_queue_cnt_sum )
, max(admctl_wait_tm_sec_sum )
from raw
group by host
;
-- Current Load
select left(now()||'',19) Time
, m_t_running
, m_t_nw_poll
, m_t_jobexec_wait
, m_t_mutex_wait
, m_t_exlock_enter
, m_t_semaphore_wait
, m_t_shlock_enter
, m_t_joining
, m_t_intentlockenter
, m_t_all - (m_t_running+m_t_nw_poll+m_t_jobexec_wait+m_t_mutex_wait+m_t_exlock_enter+m_t_semaphore_wait+m_t_shlock_enter+m_t_joining+m_t_intentlockenter) m_t_other
, s_t_running
, s_t_nw_poll
, s_t_jobexec_wait
, s_t_mutex_wait
, s_t_exlock_enter
, s_t_semaphore_wait
, s_t_shlock_enter
, s_t_joining
, s_t_intentlockenter
, s_t_all - (s_t_running+s_t_nw_poll+s_t_jobexec_wait+s_t_mutex_wait+s_t_exlock_enter+s_t_semaphore_wait+s_t_shlock_enter+s_t_joining+s_t_intentlockenter) s_t_other
, m_t_sqlexecutor
, m_t_jobworker
, m_t_all - (m_t_sqlexecutor+m_t_jobworker) m_t_other
, m_t_sqlexecutor_running
, m_t_jobworker_running
, m_t_running - (m_t_sqlexecutor_running+m_t_jobworker_running) m_t_other_running
, s_t_sqlexecutor
, s_t_jobworker
, s_t_all - (s_t_sqlexecutor+s_t_jobworker) s_t_other
, s_t_sqlexecutor_running
, s_t_jobworker_running
, s_t_running - (s_t_sqlexecutor_running+s_t_jobworker_running) s_t_other_running
, r2m_blog_tm_ms
, r2m_blog_sz_mb
, r2m_rep_blog_tm_ms
, r2m_rep_blog_sz_mb
, r2s_blog_tm_ms
, r2s_blog_sz_mb
, r2s_rep_blog_tm_ms
, r2s_rep_blog_sz_mb
, r3m_blog_tm_ms
, r3m_blog_sz_mb
, r3m_rep_blog_tm_ms
, r3m_rep_blog_sz_mb
, r3s_blog_tm_ms
, r3s_blog_sz_mb
, r3s_rep_blog_tm_ms
, r3s_rep_blog_sz_mb
, m_tps
, m_exe_per_sec
, m_commit_per_sec
, m_rollbk_per_sec
, s_tps
, s_exe_per_sec
, s_commit_per_sec
, s_rollbk_per_sec
, case when m_deltaemerge is null then 0 else m_deltaemerge end m_deltamerge_cnt
, case when m_savepoint is null then 0 else m_savepoint end m_savepoint_cnt
, case when m_savepoint_crit is null then 0 else m_savepoint_crit end m_savepoint_crit_cnt
, case when s_deltamerge is null then 0 else s_deltamerge end s_deltamerge_cnt
, case when s_savepoint is null then 0 else s_savepoint end s_savepoint_cnt
, case when s_savepoint_crit is null then 0 else s_savepoint_crit end s_savepoint_crit_cnt
from (
select sum(case when host = 'hana1-master' and thread_state = 'Running' then 1 else 0 end) m_t_running
, sum(case when host = 'hana1-master' and thread_state = 'Network Poll' then 1 else 0 end) m_t_nw_poll
, sum(case when host = 'hana1-master' and thread_state = 'Job Exec Waiting' then 1 else 0 end) m_t_jobexec_wait
, sum(case when host = 'hana1-master' and thread_state = 'Mutex Wait' then 1 else 0 end) m_t_mutex_wait
, sum(case when host = 'hana1-master' and thread_state = 'ExclusiveLock Enter' then 1 else 0 end) m_t_exlock_enter
, sum(case when host = 'hana1-master' and thread_state = 'Semaphore Wait' then 1 else 0 end) m_t_semaphore_wait
, sum(case when host = 'hana1-master' and thread_state = 'SharedLock Enter' then 1 else 0 end) m_t_shlock_enter
, sum(case when host = 'hana1-master' and thread_state = 'Joining' then 1 else 0 end) m_t_joining
, sum(case when host = 'hana1-master' and thread_state = 'IntentLock Enter' then 1 else 0 end) m_t_intentlockenter
, sum(case when host = 'hana1-master' then 1 else 0 end) m_t_all
, sum(case when host = 'hana1-slave' and thread_state = 'Running' then 1 else 0 end) s_t_running
, sum(case when host = 'hana1-slave' and thread_state = 'Network Poll' then 1 else 0 end) s_t_nw_poll
, sum(case when host = 'hana1-slave' and thread_state = 'Job Exec Waiting' then 1 else 0 end) s_t_jobexec_wait
, sum(case when host = 'hana1-slave' and thread_state = 'Mutex Wait' then 1 else 0 end) s_t_mutex_wait
, sum(case when host = 'hana1-slave' and thread_state = 'ExclusiveLock Enter' then 1 else 0 end) s_t_exlock_enter
, sum(case when host = 'hana1-slave' and thread_state = 'Semaphore Wait' then 1 else 0 end) s_t_semaphore_wait
, sum(case when host = 'hana1-slave' and thread_state = 'SharedLock Enter' then 1 else 0 end) s_t_shlock_enter
, sum(case when host = 'hana1-slave' and thread_state = 'Joining' then 1 else 0 end) s_t_joining
, sum(case when host = 'hana1-slave' and thread_state = 'IntentLock Enter' then 1 else 0 end) s_t_intentlockenter
, sum(case when host = 'hana1-slave' then 1 else 0 end) s_t_all
, sum(case when host = 'hana1-master' and thread_type = 'JobWorker' then 1 else 0 end) m_t_jobworker
, sum(case when host = 'hana1-master' and thread_type = 'SQLExcutor' then 1 else 0 end) m_t_SQLExecutor
, sum(case when host = 'hana1-slave' and thread_type = 'JobWorker' then 1 else 0 end) s_t_jobworker
, sum(case when host = 'hana1-slave' and thread_type = 'SQLExcutor' then 1 else 0 end) s_t_SQLExecutor
, sum(case when host = 'hana1-master' and thread_state = 'Running' and thread_type = 'JobWorker' then 1 else 0 end) m_t_jobworker_running
, sum(case when host = 'hana1-master' and thread_state = 'Running' and thread_type = 'SQLExcutor' then 1 else 0 end) m_t_SQLExecutor_running
, sum(case when host = 'hana1-slave' and thread_state = 'Running' and thread_type = 'JobWorker' then 1 else 0 end) s_t_jobworker_running
, sum(case when host = 'hana1-slave' and thread_state = 'Running' and thread_type = 'SQLExcutor' then 1 else 0 end) s_t_SQLExecutor_running
from m_service_threads
where is_active = 'TRUE'
and port = 30003
)
, (
select sum(case when secondary_host = 'hana2-master' then BACKLOG_TIME /1000 else 0 end) r2m_blog_tm_ms
, sum(case when secondary_host = 'hana2-master' then BACKLOG_SIZE /1024/1024 else 0 end) r2m_blog_sz_mb
, sum(case when secondary_host = 'hana2-master' then REPLAY_BACKLOG_TIME/1000 else 0 end) r2m_rep_blog_tm_ms
, sum(case when secondary_host = 'hana2-master' then REPLAY_BACKLOG_SIZE/1024/1024 else 0 end) r2m_rep_blog_sz_mb
, sum(case when secondary_host = 'hana2-slave' then BACKLOG_TIME /1000 else 0 end) r2s_blog_tm_ms
, sum(case when secondary_host = 'hana2-slave' then BACKLOG_SIZE /1024/1024 else 0 end) r2s_blog_sz_mb
, sum(case when secondary_host = 'hana2-slave' then REPLAY_BACKLOG_TIME/1000 else 0 end) r2s_rep_blog_tm_ms
, sum(case when secondary_host = 'hana2-slave' then REPLAY_BACKLOG_SIZE/1024/1024 else 0 end) r2s_rep_blog_sz_mb
, sum(case when secondary_host = 'hana3-master' then BACKLOG_TIME /1000 else 0 end) r3m_blog_tm_ms
, sum(case when secondary_host = 'hana3-master' then BACKLOG_SIZE /1024/1024 else 0 end) r3m_blog_sz_mb
, sum(case when secondary_host = 'hana3-master' then REPLAY_BACKLOG_TIME/1000 else 0 end) r3m_rep_blog_tm_ms
, sum(case when secondary_host = 'hana3-master' then REPLAY_BACKLOG_SIZE/1024/1024 else 0 end) r3m_rep_blog_sz_mb
, sum(case when secondary_host = 'hana3-slave' then BACKLOG_TIME /1000 else 0 end) r3s_blog_tm_ms
, sum(case when secondary_host = 'hana3-slave' then BACKLOG_SIZE /1024/1024 else 0 end) r3s_blog_sz_mb
, sum(case when secondary_host = 'hana3-slave' then REPLAY_BACKLOG_TIME/1000 else 0 end) r3s_rep_blog_tm_ms
, sum(case when secondary_host = 'hana3-slave' then REPLAY_BACKLOG_SIZE/1024/1024 else 0 end) r3s_rep_blog_sz_mb
from m_service_replication
where port = 30003
)
, (
select round(avg(case when host = 'hana1-master' then CURRENT_TRANSACTION_RATE/60 end),2) m_tps
, round(avg(case when host = 'hana1-master' then CURRENT_EXECUTION_RATE /60 end),2) m_exe_per_sec
, round(avg(case when host = 'hana1-master' then CURRENT_COMMIT_RATE /60 end),2) m_commit_per_sec
, round(avg(case when host = 'hana1-master' then CURRENT_ROLLBACK_RATE /60 end),2) m_rollbk_per_sec
, round(avg(case when host = 'hana1-slave' then CURRENT_TRANSACTION_RATE/60 end),2) s_tps
, round(avg(case when host = 'hana1-slave' then CURRENT_EXECUTION_RATE /60 end),2) s_exe_per_sec
, round(avg(case when host = 'hana1-slave' then CURRENT_COMMIT_RATE /60 end),2) s_commit_per_sec
, round(avg(case when host = 'hana1-slave' then CURRENT_ROLLBACK_RATE /60 end),2) s_rollbk_per_sec
from m_workload
where port = 30003
)
, (
select sum(case when host = 'hana1-master' and job_name = 'Delta Merge' then 1 else 0 end) m_deltaemerge
, sum(case when host = 'hana1-master' and job_name = 'Savepoint' then 1 else 0 end) m_savepoint
, sum(case when host = 'hana1-master' and job_name = 'Savepoint Critical Phase' then 1 else 0 end) m_savepoint_crit
, sum(case when host = 'hana1-slave' and job_name = 'Delta Merge' then 1 else 0 end) s_deltamerge
, sum(case when host = 'hana1-slave' and job_name = 'Savepoint' then 1 else 0 end) s_savepoint
, sum(case when host = 'hana1-slave' and job_name = 'Savepoint Critical Phase' then 1 else 0 end) s_savepoint_crit
from m_job_progress
where port = 30003
)
;
-- Thread 현황 확인
select thread_state
, substr(thread_method,1,30)
, substr(lock_wait_name,1,30)
, application_user_name
, substr(application_source,1,30)
, substr(thread_detail,1,30)
, statement_hash
, count(*) cnt
from M_SERVICE_THREADS
where thread_state != 'Inactive'
group by
thread_state
, substr(thread_method,1,30)
, substr(lock_wait_name,1,30)
, application_user_name
, substr(application_source,1,30)
, statement_hash
, substr(thread_detail,1,30)
order by thread_state, cnt desc
;
-- Thread 상세 현황
select connection_id
, thread_id
, thread_method
, thread_detail
, thread_state
, duration
, caller
, calling
, statement_hash
, user_name
, application_user_name
, application_source
, cpu_time_self
, client_ip
, client_pid
, transaction_id
, lock_wait_component
, lock_wait_name
, numa_node_index
from m_service_threads
where statement_hash = 'c13a8d89f6857abc9b034b**********'
and thread_method = 'ExecutePrepared'
and thread_state = 'Semaphore Wait'
;
-- SQL Text 확인
with st as ( select 'c13a8d89f6857abc9b034b**********' sh from dummy )
select top 1 substr(statement_string, 1,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string, 201,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string, 401,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string, 601,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string, 801,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,1001,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,1201,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,1401,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,1601,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,1801,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,2001,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,2201,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,2401,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,2601,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh union all
select top 1 substr(statement_string,2801,200) from _sys_statistics.HOST_SQL_PLAN_CACHE, st where statement_hash = st.sh
-- SQL History 현황
select to_char(server_timestamp, 'YYYY-MM-DD HH24:MI:SS') tm
, application_name
, application_source
, user_name
, min_execution_time / 1024 min_exe_tm_ms -- Execution Time (Min)
, avg_execution_time / 1024 avg_exe_tm_ms -- Execution Time (Avg)
, max_execution_time / 1024 max_exe_tm_ms -- Execution Time (Max)
, min_preparation_time / 1024 min_pre_tm_ms -- Preparation Time (Min)
, avg_preparation_time / 1024 avg_pre_tm_ms -- Preparation Time (Avg)
, max_preparation_time / 1024 max_pre_tm_ms -- Preparation Time (Max)
, execution_count exe_cnt -- Execution count
, total_result_record_count rs_cnt -- Result record count
, total_result_record_count / execution_count rs_per_exe
, total_execution_time / 1024 tot_exe_ms
, total_preparation_time / 1024 tot_pre_ms
, (total_execution_time - total_lock_wait_duration)/1024 "EXE-LOCK" -- Execution time - lock time
, (total_execution_time - total_lock_wait_duration)/execution_count/1024 "EXE_Tm-LOCK_Tm per EXE"
, total_lock_wait_duration / 1024 tot_lck_ms
, total_lock_wait_count tot_lck_cnt
from _sys_statistics.host_sql_plan_cache
where statement_hash = 'c13a8d89f6857abc9b034bc4********'
and server_timestamp between to_timestamp('20200720-000000','YYYYMMDD-HH24MISS') and to_timestamp('20200723-235900','YYYYMMDD-HH24MISS')
;
-- 아래 쿼리들은 실행은 되지만 실제 값들을 확인하지 못하였음 (HANA DB cloud 환경)
-- 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;
-- 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."}'
-- Resetting Monitoring Views
-- 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;
-- UNCOMPRESSED_SIZE 컬럼이 없어짐
-- 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_NAME='SCHEMA'
-- SCHEMA --> SCHEMA_NAME 으로 변경됨
-- 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';
관리 Objects
SYS.TABLE_GROUPS : 연관된 테이블들은 일반적인 Table group으로 분류 가능
CREATE COLUMN TABLE "TEST".A1 (A INT) GROUP TYPE ABC GROUP SUBTYPE T
ALTER TABLE "SAPLG1"."/BIC/MUCSTR000000" SET GROUP NAME "ABC"
SYS.TABLE_PLACEMENT : Table Placement Rules
M_TABLE_REPLICAS : Replication Monitoring View
reorg_plan / REORG_OVERVIEW : 수행계획
m_table_locations : Tables and their logical location. Physical locations are shown in M_TABLE_PERSISTENCE_LOCATIONS
Dictionary 정보
AUDIT_POLICIES - Defined audit policies
select * from AUDIT_POLICIES
select * from sys.users
AUTHORIZATION_GRAPH - Represents authorization dependencies of complex database objects
CONSTRAINTS - Constraints defined for tables
CS_BO_VIEWS - Business object views for column store join views
CS_FREESTYLE_COLUMNS - Freestyle search columns for column store join views
CS_JOIN_CONDITIONS - Join conditions defined for column store join views
CS_JOIN_CONSTRAINTS - Join constraints defined for column store join views
CS_JOIN_PATHS - Join paths defined for column store join views
CS_JOIN_TABLES - Physical tables referred by column store join views
CS_KEY_FIGURES - Key figures defined for column store join views
CS_VIEW_COLUMNS - Columns defined for column store join views
DATA_TYPES - Available SQL data types
EFFECTIVE_PRIVILEGES - Privileges of the current user
** EXPLAIN_PLAN_TABLE - SQL query plan explanation result
FULLTEXT_INDEXES - Fulltext indexes on table columns
** FUNCTIONS - Available functions
FUNCTION_PARAMETERS - Parameters of functions
GRANTED_PRIVILEGES - Privileges granted to users and roles
GRANTED_ROLES - Roles granted to users or other roles
** INDEXES - Indexes on tables
** INDEX_COLUMNS - Columns of indexes
INVALID_CONNECT_ATTEMPTS - Number of invalid connect attempts for a user between two successful connects
-- M_ATTACHED_STORAGES - Information about currently attached devices
M_BACKUP_CATALOG - Common data for all backup catalog entries
M_BACKUP_CATALOG_FILES - Location information of all backup catalog entries
M_BACKUP_CONFIGURATION - Backup configuration statistics
M_BLOCKED_TRANSACTIONS - Transaction list waiting for locks
M_CACHES - Shows aggregated information on caches
M_CACHES_RESET - Shows aggregated information on caches
M_CACHE_ENTRIES - Cache entry information
M_CATALOG_MEMORY - Memory usage information by catalog manager
M_CE_CALCSCENARIOS - All available CalculationScenarios
M_CE_CALCVIEW_DEPENDENCIES - All views that are referencing a CalculationScenario
M_CE_DEBUG_INFOS - Debug information after execution of a CalculationScenario
M_CE_DEBUG_JSONS - All available JSONS (original, instantiated, optimized) of a scenario for a concrete query
M_CE_DEBUG_NODE_MAPPING - Node mapping between CalculationNodes and RuntimeNodes after execution
-- M_CE_PLE_CALCSCENARIOS - All available CalculationScenarios created by the PlanningEngine
M_CLIENT_VERSIONS - Versions of all supported client applications
M_COMPACTION_THREAD - Compaction thread statistics
M_CONDITIONAL_VARIABLES - Semaphore statistics
M_CONDITIONAL_VARIABLES_RESET - Semaphore statistics
-- M_CONFIGURATION - Configuration parameters
-- M_CONNECTIONS - Detailed information on connections between a client and database. Information includes connection status, client information, connection type, resource utilization
** M_CONNECTION_STATISTICS - Detailed statistics on each connection between an application and database
M_CONTAINER_DIRECTORY - ContainerDirectory statistics
M_CONTAINER_NAME_DIRECTORY - ContainerNameDirectory statistics
M_CONTEXT_MEMORY - Memory allocator statistics
M_CONTEXT_MEMORY_RESET - Memory allocator statistics
M_CONVERTER_STATISTICS - Converter statistics
M_CONVERTER_STATISTICS_RESET - Converter statistics
M_CS_ALL_COLUMNS - Runtime information of all columns of column tables, inclusive internal ones
M_CS_COLUMNS - Runtime information of columns of column tables
-- M_CS_PARTITIONS - Partition information of column tables
M_CS_TABLES - Runtime data of column tables
M_CS_UNLOADS - A history of column unloads
** M_DATABASE - Database information
M_DATABASE_HISTORY - Installation version history
M_DATA_VOLUMES - DataVolume statistics
M_DATA_VOLUME_PAGE_STATISTICS - Page usage statistics on data volumes
M_DATA_VOLUME_PAGE_STATISTICS_RESET - Page usage statistics on data volumes
M_DATA_VOLUME_SUPERBLOCK_STATISTICS - FreeBlockManager Superblock statistics
M_DELTA_MERGE_STATISTICS - Table delta merge statistics
M_DISKS - Disk configuration and utilization of the host machine
** M_ERROR_CODES - Error codes with descriptions
M_EVENTS - Internal events
M_EXPENSIVE_STATEMENTS - All statements with duration longer than a threshold
-- M_EXPORT_BINARY_STATUS - Export status information for current session
-- M_EXTRACTORS - Direct extractor connection (DXC) status information
M_FEATURES - All supported features
-- M_FULLTEXT_QUEUES - Fulltext index queue status
M_GARBAGE_COLLECTION_STATISTICS - Garbage collection/history manager statistics
M_GARBAGE_COLLECTION_STATISTICS_RESET - Garbage collection/history manager statistics
M_HEAP_MEMORY - Memory allocator statistics
M_HEAP_MEMORY_RESET - Memory allocator statistics
-- M_HISTORY_INDEX_LAST_COMMIT_ID - Last commit ID of history index for each session
M_HOST_INFORMATION - Host information such as machine, OS configuration
M_HOST_RESOURCE_UTILIZATION - Host resource utilization, CPU time is in milliseconds and added across all cores since system start
-- M_IMPORT_BINARY_STATUS - Import status information for current session
M_INIFILES - All configuration files
M_INIFILE_CONTENTS - Configuration information from inifiles
M_JOB_PROGRESS - Current long running system operations
M_LANDSCAPE_HOST_CONFIGURATION - Host roles in a distributed landscape
M_LICENSE - Information on the currently valid license (if any) installed on this system
-- M_LICENSE_USAGE_HISTORY - Information on the maximum resource consumption per time period, used for validity check of license installed on this system (if any)
-- M_LIVECACHE_CONTAINER_STATISTICS - LiveCache container statistics
-- M_LIVECACHE_CONTAINER_STATISTICS_RESET - LiveCache container statistics
-- M_LIVECACHE_LOCKS - Detailed information on the Object Management System (OMS) locks
-- M_LIVECACHE_LOCK_STATISTICS - LiveCache lock statistics
-- M_LIVECACHE_LOCK_STATISTICS_RESET - LiveCache lock statistics
-- M_LIVECACHE_OMS_VERSIONS - Detailed information on the OMS versions that currently exists
-- M_LIVECACHE_PROCEDURE_STATISTICS - LiveCache procedure statistics
-- M_LIVECACHE_PROCEDURE_STATISTICS_RESET - LiveCache procedure statistics
-- M_LIVECACHE_SCHEMA_STATISTICS - LiveCache schema statistics
-- M_LIVECACHE_SCHEMA_STATISTICS_RESET - LiveCache schema statistics
M_LOCK_WAITS_STATISTICS - Accumulated lock wait count and duration for record lock, table lock and metadata lock for all available services from DB startup until now
M_LOG_BUFFERS - Log buffer statistics
M_LOG_BUFFERS_RESET - Log buffer statistics
M_LOG_PARTITIONS - Log partition statistics
M_LOG_PARTITIONS_RESET - Log partition statistics
M_LOG_SEGMENTS - Log segment statistics
M_LOG_SEGMENTS_RESET - Log segment statistics
M_MEMORY_OBJECTS - Memory object statistics
M_MEMORY_OBJECTS_RESET - Memory object statistics
M_MEMORY_OBJECT_DISPOSITIONS - Disposition specific memory object statistics. The statistics are calculated and reading them may take a while.
M_MERGED_TRACES - Contains the merged content of the server trace files for all of the SAP HANA processes
** M_MONITORS - Available monitoring views
** M_MONITOR_COLUMNS - All the columns in the monitoring views
SELECT * FROM M_MONITOR_COLUMNS ORDER BY VIEW_NAME, POSITION ASC
M_MUTEXES - Mutex statistics
M_MUTEXES_RESET - Mutex statistics
M_MVCC_TABLES - Statistics of row-store Multiversion Concurrency Control (MVCC) manager
M_OBJECT_LOCKS - Status of currently acquired locks on objects with detailed information such as lock acquisition time, lock mode
M_OBJECT_LOCK_STATISTICS - Lock contention statistics - lock wait count, wait time, failed count for each objects
M_OBJECT_LOCK_STATISTICS_RESET - Lock contention statistics - lock wait count, wait time, failed count for each objects
M_PAGEACCESS_STATISTICS - PageAccess statistics
M_PAGEACCESS_STATISTICS_RESET - PageAccess statistics
M_PASSWORD_POLICY - Configuration values concerning password policy
M_PERFTRACE - Current PerfTrace state
M_PERSISTENCE_MANAGERS - Persistence manager statistics
M_PERSISTENCE_MANAGERS_RESET - Persistence manager statistics
M_PREPARED_STATEMENTS - Prepared statements list
M_READWRITELOCKS - Read/Write lock statistics
M_READWRITELOCKS_RESET - Read/Write lock statistics
M_RECORD_LOCKS - Record lock status
M_REORG_ALGORITHMS - Landscape redistribution algorithms
M_REPO_TRANSPORT_FILES - All repository transport files
M_RS_INDEXES - Statistics of B-tree and CPB-tree indexes
M_RS_TABLES - Information on row tables: detailed table sizes and record count
M_RS_TABLE_VERSION_STATISTICS - Information on row table versions: detailed version counts and used sizes
M_SAVEPOINTS - Current and historical savepoint statistics
M_SAVEPOINT_STATISTICS - Savepoint statistics
M_SAVEPOINT_STATISTICS_RESET - Savepoint statistics
M_SEMAPHORES - Semaphore statistics
M_SEMAPHORES_RESET - Semaphore statistics
M_SERVICES - Status of all services
M_SERVICE_COMPONENT_MEMORY - Service-specific memory usage by logical component
M_SERVICE_MEMORY - Detailed information on memory utilization by services
M_SERVICE_NETWORK_IO - Service network I/O statistics
M_SERVICE_REPLICATION - Information about replicated services
M_SERVICE_STATISTICS - Statistics on active services
M_SERVICE_THREADS - Detailed information on threads created by services
M_SERVICE_THREAD_CALLSTACKS - Stack frame information for service threads
M_SERVICE_TRACES - Configured trace components for each service type
M_SERVICE_TYPES - Service types
M_SESSION_CONTEXT - Session variables for each connection.
M_SHARED_MEMORY - Shared memory usage information by SAP HANA indexserver
M_SNAPSHOTS - Existing snapshots
M_SQL_PLAN_CACHE - Statistics of an individual execution plan
M_SQL_PLAN_CACHE_OVERVIEW - Overall statistics of evicted and cached plans
M_SQL_PLAN_CACHE_RESET - Statistics of an individual execution plan
M_SYSTEM_INFORMATION_STATEMENTS - System information statements
M_SYSTEM_LIMITS - System limits information
** M_SYSTEM_OVERVIEW - Overview of system status including important resource usage information and alerts
** M_TABLES - Information on row and column tables
** M_TABLE_LOB_FILES - All LOB files that belong to a table
M_TABLE_LOCATIONS - Tables and their logical location. Physical locations are shown in M_TABLE_PERSISTENCE_LOCATIONS
M_TABLE_PERSISTENCE_LOCATIONS - Column store tables and their physical data locations
M_TABLE_PERSISTENCE_STATISTICS - Persistence virtual file summary statistics for tables
M_TABLE_VIRTUAL_FILES - All virtual files that belong to a table
M_TEMPORARY_TABLES - Temporary tables
M_TEMPORARY_TABLE_COLUMNS - Columns of temporary tables
M_TEMPORARY_VIEWS - Temporary views
M_TEMPORARY_VIEW_COLUMNS - Columns of temporary tables
-- M_TENANTS - Available tenant information
-- M_TEXT_ANALYSIS_LANGUAGES - List of supported languages
-- M_TEXT_ANALYSIS_MIME_TYPES - List of supported mime types
M_TOPOLOGY_TREE - SAP HANA nameserver topology content
M_TRACEFILES - All trace files
M_TRACEFILE_CONTENTS - SAP HANA information from trace files
M_TRANSACTIONS - All transactions created by users or database
M_UNDO_CLEANUP_FILES - Information about undo files and cleanup files
M_VERSION_MEMORY - Memory usage of row-store Multiversion Concurrency Control (MVCC) manager
M_VOLUMES - Volumes used by SAP HANA servers
M_VOLUME_FILES - Information about volume files
-- M_VOLUME_IO_PERFORMANCE_STATISTICS - File performance statistics
-- M_VOLUME_IO_PERFORMANCE_STATISTICS_RESET - File performance statistics
-- M_VOLUME_IO_STATISTICS - File access statistics
-- M_VOLUME_IO_STATISTICS_RESET - File access statistics
M_VOLUME_SIZES - Volume sizes used by SAP HANA servers
M_WORKLOAD - Database workload collected every minute
-- M_XS_APPLICATIONS - Applications deployed and running in SAP HANA XS
M_XS_APPLICATION_ISSUES - Issues related to applications in SAP HANA XS
** OBJECTS - Available objects
** OBJECT_DEPENDENCIES - Dependencies between objects, for example, views which refer to a specific table
OWNERSHIP - Ownership of available objects when users create any object in other users' schemas
** PRIVILEGES - Available privileges
** PROCEDURES - Available stored procedures
PROCEDURE_OBJECTS - Contains results of the system procedure GET_PROCEDURE_OBJECTS
** PROCEDURE_PARAMETERS - Parameters of stored procedures
QUERY_PLANS - Plans how to handle query execution
REFERENTIAL_CONSTRAINTS - Referential constraints
REORG_OVERVIEW - Overview of landscape redistributions
REORG_PLAN - Current plan for landscape reorganization
REORG_PLAN_INFOS - Additional informations about current landscape reorganization plan
REORG_STEPS - Details of landscape redistribution
ROLES - Shows available roles
SAML_PROVIDERS - Shows available SAML provider
SAML_USER_MAPPINGS - Shows the SAML providers known for each user
SCHEMAS - Shows available schemas
** SEQUENCES - Available sequences
-- SQLSCRIPT_TRACE - Contains the names of the local temporary tables created by the SQLScript TRACE operator
-- STATISTICS - Stores a histogram of tables. The histogram can be used by query optimizer to calculate size of an intermediate result
STRUCTURED_PRIVILEGES - Available structured privileges
** SYNONYMS - Available synonyms
** TABLES - Available tables
** TABLE_COLUMNS - Available table columns
-- TABLE_COLUMNS_ODBC - Available table columns
TABLE_GROUPS - Overview of table groups relations
-- TRANSACTION_HISTORY - Committed transactions and their users
TRIGGERS - Triggers defined for tables
** USERS - All users
USER_PARAMETERS - All parameters and their values, which have been assigned to users in the system (using CREATE USER ... SET PARAMETER or ALTER USER ... SET PARAMETER)
** VIEWS - Available views
VIEW_COLUMNS - Available view columns
SELECT * FROM M_MONITORS
VIEW_NAME DESCRIPTION RESETTABLE
M_LOAD_HISTORY_INFO Load history KPI description FALSE
M_JOB_HISTORY_INFO History of long running system operations FALSE
M_RS_TABLES Information on row tables: detailed table sizes and record count FALSE
M_CS_TABLES Runtime data of column tables FALSE
M_RS_INDEXES Statistics of B-tree and CPB-tree indexes FALSE
M_CS_INDEXES Statistics of column store indexes FALSE
M_EFFECTIVE_TABLE_PLACEMENT Information about effective placement info for tables FALSE
M_COLLECTION_TABLES Information on collections FALSE
M_DATA_STATISTICS Runtime data of data statistics FALSE
M_SYSTEM_DATA_STATISTICS System data statistics FALSE
M_MERGED_TRACES Contains the merged content of the server trace files for all of the SAP HANA services FALSE
M_CS_COLUMNS Runtime information of columns of column tables FALSE
M_FUZZY_SEARCH_INDEXES Runtime information of fuzzy search indexes of column tables FALSE
M_CATALOG_MEMORY Memory usage information by catalog manager FALSE
M_DATABASE Database information FALSE
M_ERROR_CODES Error codes with descriptions FALSE
M_SYSTEM_LIMITS System limits information FALSE
M_SYSTEM_INFORMATION_STATEMENTS System information statements FALSE
M_MONITORS Available monitoring views FALSE
M_MONITOR_COLUMNS All the columns in the monitoring views FALSE
M_RECORD_LOCKS Record lock status FALSE
M_OBJECT_LOCKS Status of currently acquired locks on objects with detailed information such as lock acquisition time, lock mode FALSE
M_TABLE_PARTITION_STATISTICS Partition specific select count for partitioned tables FALSE
M_DISKS Disk configuration and utilization of the host machine FALSE
M_HA_DR_PROVIDERS Information about loaded HADR Providers FALSE
M_SERVICE_REPLICATION Information about replicated services FALSE
M_TABLE_LOCATIONS Tables and their logical location. Physical locations are shown in M_TABLE_PERSISTENCE_LOCATIONS FALSE
M_TABLE_VIRTUAL_FILES All virtual files that belong to a table FALSE
M_COLLECTION_TABLE_VIRTUAL_FILES Information on virtual files for collections FALSE
M_TABLE_LOB_FILES All LOB files that belong to a table FALSE
M_TABLE_PLACEMENT_LOCATIONS Table placement locations FALSE
M_REMOTE_SUBSCRIPTION_COMMIT_SEQUENCE_GROUP_CONTAINERS Commit sequence containers created for a remote source FALSE
M_REMOTE_SUBSCRIPTION_COMMIT_SEQUENCE_CONTAINERS Commit sequence of real time data elements FALSE
M_REMOTE_SUBSCRIPTION_ROLLBACK_SAVEPOINT_CONTAINERS Rollback savepoints of a transaction FALSE
M_REMOTE_SUBSCRIPTION_TRANSACTION_CONTAINERS Real time data rowsets in a transaction FALSE
M_REMOTE_SUBSCRIPTION_LOB_CONTAINERS LOB container IDs of a transaction FALSE
M_REMOTE_SUBSCRIPTION_DISTRIBUTOR_QUEUE_DATA_CONTAINERS Real time data elements between begin-marker and end-marker FALSE
M_ANONYMIZATION_VIEWS Current state of the anonymized view. FALSE
M_SYSTEM_OVERVIEW Overview of system status including important resource usage information and alerts FALSE
M_SERVICES Status of all services FALSE
M_SERVICE_STATISTICS Statistics on active services FALSE
M_SERVICE_THREADS Detailed information on threads created by services FALSE
M_SERVICE_THREAD_CALLSTACKS Stack frame information for service threads FALSE
M_SERVICE_MEMORY Detailed information on memory utilization by services FALSE
M_SERVICE_TYPES Service types FALSE
M_SERVICE_TRACES Configured trace components for each service type FALSE
M_SERVICE_COMPONENT_HISTORY History lifecycle information for all components for all services FALSE
M_INIFILES All configuration files FALSE
M_INIFILE_CONTENTS Configuration information from inifiles FALSE
M_VOLUMES Volumes used by SAP HANA servers FALSE
M_VOLUME_SIZES Volume sizes used by SAP HANA servers FALSE
M_HOST_INFORMATION Host information such as machine, OS configuration FALSE
M_HOST_RESOURCE_UTILIZATION Host resource utilization, CPU time is in milliseconds and added across all cores since system start FALSE
M_LICENSE Information on the currently valid license (if any) installed on this system FALSE
M_LICENSES Information on all licenses (if any) so far installed on this system FALSE
M_TOPOLOGY_TREE SAP HANA nameserver topology content FALSE
M_DATABASES multiple database content FALSE
M_TRACEFILES All trace files FALSE
M_TRACEFILE_CONTENTS SAP HANA information from trace files FALSE
M_DATABASE_HISTORY Installation version history FALSE
M_PERFTRACE Current PerfTrace state FALSE
M_EXPENSIVE_STATEMENTS This view shows all statements with duration longer than a threshold FALSE
M_SQL_CLIENT_NETWORK_IO This view shows client and server elapsed time as well as message sizes for client network messages FALSE
M_CS_UNLOADS This view shows a history of column unloads FALSE
M_CS_LOADS This view shows a history of column loads FALSE
M_CS_COLUMNS_PERSISTENCE Columnstore table main column data information FALSE
M_JOINENGINE_STATISTICS Column store joinengine statistics FALSE
M_JOIN_TRANSLATION_TABLES Column store joinengine translation tables statistics FALSE
M_JOIN_DATA_STATISTICS Column store joinengine join statistics FALSE
M_REPO_TRANSPORT_FILES All repository transport files FALSE
M_CLIENT_VERSIONS Versions of all supported client applications FALSE
M_CE_CALCSCENARIOS All available CalculationScenarios FALSE
M_CE_CALCSCENARIO_HINTS The list of all hints defined at calculation scenarios FALSE
M_CE_CALCVIEW_DEPENDENCIES All views that are referencing a CalculationScenario FALSE
M_LANDSCAPE_HOST_CONFIGURATION Host roles in a distributed landscape FALSE
M_SYSTEM_AVAILABILITY View of system availability log FALSE
M_LOAD_HISTORY_HOST Host specific load history KPIs FALSE
M_LOAD_HISTORY_SERVICE Host specific load history KPIs FALSE
M_JOB_PROGRESS Current long running system operations FALSE
M_BACKUP_SIZE_ESTIMATIONS Estimated size of the next data backup FALSE
M_LOCK_WAITS_STATISTICS Accumulated lock wait count and duration for record lock, table lock and metadata lock for all available services from DB startup until now FALSE
M_CE_DEBUG_INFOS Debug information after execution of a CalculationScenario FALSE
M_CE_DEBUG_NODE_MAPPING Node mapping between CalculationNodes and RuntimeNodes after execution FALSE
M_CE_DEBUG_JSONS All available JSONS (original, instantiated, optimized) of a scenario for a concrete query FALSE
M_FEATURES All supported features FALSE
M_ENCRYPTION_OVERVIEW Reports about the encryption status for all data-at-rest where encryption is supported FALSE
M_STATISTICS_LASTVALUES In-memory version of _SYS_STATISTICS.STATISTICS_LASTVALUES FALSE
M_REORG_ALGORITHMS Landscape redistribution algorithms FALSE
M_CACHE_ENTRIES Cache entry information FALSE
M_PASSWORD_POLICY Configuration values concerning password policy FALSE
M_PLUGIN_MANIFESTS Information about installed plugins FALSE
M_PLUGIN_STATUS Information on installed plugins FALSE
M_DEBUG_SESSIONS Overview of debug sessions and their properties FALSE
M_DEBUG_CONNECTIONS Overview of connections used per debug session FALSE
M_SQLSCRIPT_CODE_COVERAGE_OBJECT_DEFINITIONS Stores object definitions (source code) and mapping to entries in sqlscript code coverage results view FALSE
M_SQLSCRIPT_CODE_COVERAGE_RESULTS Lists the sqlscript code coverage results from the beginning of the coverage run up to the point of selecting M_SQLSCRIPT_CODE_COVERAGE_RESULTS FALSE
M_RESULT_CACHE_EXCLUSIONS Result cache exclusions FALSE
M_RESULT_CACHE Result cache statistics FALSE
M_EPM_SESSIONS This view shows all EPM sessions with detailed information FALSE
M_MULTIDIMENSIONAL_STATEMENT_STATISTICS MultiDimensional Query Statistics for InA and MDX queries FALSE
M_REMOTE_SUBSCRIPTION_COMPONENTS Status of remote subscription for each internal component. FALSE
M_REMOTE_SUBSCRIPTION_STATISTICS Statistics for remote subscriptions running on DPServer including number of messages received/applied/rejected, message bytes recieved/applied, last message received/applied timestamp, receiver/applier latency. FALSE
M_AGENTS Status of each DP agent registered in the HANA system. FALSE
M_REMOTE_SOURCE_LATENCY_HISTORY Remote source latency statistics history for monitoring performance of real time replication. FALSE
M_REMOTE_SOURCE_LATENCY_STATUS Remote source latency ticket status for monitoring performance of real time replication. FALSE
M_REMOTE_SOURCE_STATISTICS Remote source operational statistics for monitoring data provisioning components. FALSE
M_REMOTE_QUERY_STATISTICS Data Provisioning Remote Query Statistics FALSE
M_REMOTE_SUBSCRIPTIONS Run-time information of the remote subscription that must be persisted for subscription restart. FALSE
M_HOST_NETWORK_STATISTICS Network statistics of host; most columns correspond to the similarly named values reported by 'netstat -s' FALSE
M_TIMEZONE_ALERTS Alerts relating to status of SAP HANA internal timezone conversion FALSE
M_EXECUTED_STATEMENTS This view shows all executed statements classified to be traced FALSE
M_AFL_FUNCTIONS Information on application functions FALSE
M_AFL_STATES Information on application function states FALSE
M_EFFECTIVE_PASSWORD_POLICY Password policy for specified user FALSE
M_MEMORY_PROFILER Current memory profiler state FALSE
M_LLANG_STATISTICS Llang compilation statistics FALSE
M_SYSTEM_STATISTICS System KPI metrics overview FALSE
M_HEAP_MEMORY Memory allocator statistics FALSE
M_HEAP_MEMORY_RESET Memory allocator statistics TRUE
M_CONTEXT_MEMORY Memory allocator statistics FALSE
M_CONTEXT_MEMORY_RESET Memory allocator statistics TRUE
M_OUT_OF_MEMORY_EVENTS Information about out of memory events FALSE
M_OUT_OF_MEMORY_EVENTS_RESET Information about out of memory events TRUE
M_MEMORY_RECLAIM_STATISTICS Statistics for reclaiming memory (e.g. defragmentation, unloading of memory objects, ...) FALSE
M_MEMORY_RECLAIM_STATISTICS_RESET Statistics for reclaiming memory (e.g. defragmentation, unloading of memory objects, ...) TRUE
M_HEAP_MEMORY_AREAS Heap memory areas by Memory Management FALSE
M_COMPACTION_THREAD Compaction thread statistics FALSE
M_PERSISTENT_MEMORY_VOLUME_STATISTICS Statistics of physical lifecycle events of data blocks managed on the persistent memory volumes FALSE
M_PERSISTENT_MEMORY_VOLUME_STATISTICS_RESET Statistics of physical lifecycle events of data blocks managed on the persistent memory volumes TRUE
M_PERSISTENT_MEMORY_VOLUME_DATA_FILES Metadata statistics about files created for data storage on persistent memory volumes. FALSE
M_PERSISTENT_MEMORY_VOLUMES Statistics about capacity, usage and metadata of persistent memory volumes configured per NUMA Node. FALSE
M_FEATURE_USAGE Detailed feature usage statistics FALSE
M_CONDITIONAL_VARIABLES Semaphore statistics FALSE
M_CONDITIONAL_VARIABLES_RESET Semaphore statistics TRUE
M_VOLUME_IO_DETAILED_STATISTICS File access detailed statistics FALSE
M_VOLUME_IO_DETAILED_STATISTICS_RESET File access detailed statistics TRUE
M_EVENTS Internal events FALSE
M_JOBEXECUTORS Job Executors statistics FALSE
M_JOBEXECUTORS_RESET Job Executors statistics TRUE
M_JOBEXECUTOR_WORKER_GROUPS Job executor thread group statistics FALSE
M_JOBEXECUTOR_WORKER_GROUPS_RESET Job executor thread group statistics TRUE
M_KERNEL_PROFILER Information about active kernel profilers FALSE
M_MEMORY_OBJECT_DISPOSITIONS Disposition specific memory object statistics. The statistics are calculated and reading them may take a while. FALSE
M_MEMORY_OBJECTS Memory object statistics FALSE
M_MEMORY_OBJECTS_RESET Memory object statistics TRUE
M_MUTEXES Mutex statistics FALSE
M_MUTEXES_RESET Mutex statistics TRUE
M_NUMA_RESOURCES Numa host information FALSE
M_NUMA_NODES Numa nodes information FALSE
M_READWRITELOCKS Read/Write lock statistics FALSE
M_READWRITELOCKS_RESET Read/Write lock statistics TRUE
M_SEMAPHORES Semaphore statistics FALSE
M_SEMAPHORES_RESET Semaphore statistics TRUE
M_TRACE_CONFIGURATION Trace configuration statistics FALSE
M_TRACE_CONFIGURATION_RESET Trace configuration statistics TRUE
M_VOLUME_IO_SUBMIT_STATISTICS IO Submit statistics FALSE
M_VOLUME_IO_SUBMIT_STATISTICS_RESET IO Submit statistics TRUE
M_VOLUME_IO_TOTAL_STATISTICS File access total statistics FALSE
M_VOLUME_IO_TOTAL_STATISTICS_RESET File access total statistics TRUE
M_SERVICE_NETWORK_IO Service network I/O statistics FALSE
M_SERVICE_NETWORK_IO_RESET Service network I/O statistics TRUE
M_TRANSACTIONS All transactions created by users or database FALSE
M_SERVICE_COMPONENT_DETAILS Details and startup progress information about components of a service FALSE
M_CONTAINER_DIRECTORY ContainerDirectory statistics FALSE
M_CONTAINER_NAME_DIRECTORY ContainerNameDirectory statistics FALSE
M_CONVERTER_STATISTICS Converter statistics FALSE
M_CONVERTER_STATISTICS_RESET Converter statistics TRUE
M_DATA_VOLUME_PAGE_STATISTICS FreeBlockManager SizeClass statistics FALSE
M_DATA_VOLUME_PAGE_STATISTICS_RESET FreeBlockManager SizeClass statistics TRUE
M_DATA_VOLUME_PARTITION_STATISTICS Data volume partition statistics FALSE
M_DATA_VOLUME_STATISTICS Data volume statistics FALSE
M_DATA_VOLUME_SUPERBLOCK_STATISTICS FreeBlockManager Superblock statistics FALSE
M_DATA_VOLUME_RECLAIM_STATISTICS Statistics for reclaim operation on a data volume FALSE
M_GARBAGE_COLLECTION_STATISTICS Garbage collection/history manager statistics FALSE
M_GARBAGE_COLLECTION_STATISTICS_RESET Garbage collection/history manager statistics TRUE
M_LOG_BUFFERS Log buffer statistics FALSE
M_LOG_BUFFERS_RESET Log buffer statistics TRUE
M_LOG_PARTITIONS Log partition statistics FALSE
M_LOG_PARTITIONS_RESET Log partition statistics TRUE
M_LOG_SEGMENTS Log segment statistics FALSE
M_LOG_SEGMENTS_RESET Log segment statistics TRUE
M_LOG_REPLAY_QUEUE_STATISTICS Log replay queue statistics FALSE
M_LOG_REPLAY_QUEUE_STATISTICS_RESET Log replay queue statistics TRUE
M_PAGEACCESS_STATISTICS PageAccess statistics FALSE
M_PAGEACCESS_STATISTICS_RESET PageAccess statistics TRUE
M_PERSISTENCE_ENCRYPTION_KEYS Information about encryption page keys FALSE
M_PERSISTENCE_ENCRYPTION_STATUS Information about persistence encryption FALSE
M_PERSISTENCE_MANAGERS Persistence manager statistics FALSE
M_PERSISTENCE_MANAGERS_RESET Persistence manager statistics TRUE
M_SAVEPOINT_STATISTICS Savepoint statistics FALSE
M_SAVEPOINT_STATISTICS_RESET Savepoint statistics TRUE
M_SAVEPOINTS Current and historical savepoint statistics FALSE
M_SYSTEM_REPLICATION_TIMETRAVEL Valid ranges for system replication time travel FALSE
M_SNAPSHOTS Existing snapshots FALSE
M_UNDO_CLEANUP_FILES Information about undo files and cleanup files FALSE
M_SERVICE_NETWORK_METHOD_IO Statistics on network io per method FALSE
M_SERVICE_NETWORK_METHOD_IO_RESET Statistics on network io per method TRUE
M_ADMISSION_CONTROL_EVENTS Information on happened events of admission control FALSE
M_ADMISSION_CONTROL_QUEUES Information on queued requests of admission control FALSE
M_ADMISSION_CONTROL_STATISTICS Statistics on admission control, including accumulative and current counts FALSE
M_WORKLOAD_CLASS_STATISTICS Resource usage statistics per workload class FALSE
M_SERVICE_THREAD_SAMPLES Detailed information about locks held by threads FALSE
M_CUSTOMIZABLE_FUNCTIONALITIES Statistics of customizable functionalities FALSE
M_BUFFER_CACHE_STATISTICS BufferCache statistics FALSE
M_BUFFER_CACHE_STATISTICS_RESET BufferCache statistics TRUE
M_BUFFER_CACHE_POOL_STATISTICS BufferChain statistics FALSE
M_BUFFER_CACHE_POOL_STATISTICS_RESET BufferChain statistics TRUE
M_VOLUME_FILES Information about volume files FALSE
M_REPLICATION_LOG Current status of the replication log in the system FALSE
M_TABLE_REPLICAS Detailed information on asynchronous/synchronous table replicas FALSE
M_TABLE_REPLICAS_RESET Detailed information on asynchronous/synchronous table replicas TRUE
M_BACKUP_CONFIGURATION Backup configuration statistics FALSE
M_BACKUP_PROGRESS Progress of the most recent backup FALSE
M_OBJECT_LOCK_STATISTICS Lock contention statistics - lock wait count, wait time, failed count for each objects FALSE
M_OBJECT_LOCK_STATISTICS_RESET Lock contention statistics - lock wait count, wait time, failed count for each objects TRUE
M_SQLSCRIPT_PLAN_PROFILERS List of active SQLScript Plan Profilers FALSE
M_SQLSCRIPT_PLAN_PROFILER_RESULTS Profiling results of stored procedures FALSE
M_ABSTRACT_SQL_PLAN_STATISTICS Shows the statistics of abstract sql plan FALSE
M_ABSTRACT_SQL_PLAN_OVERVIEW Overview of Abstract SQL Plan FALSE
M_VERSION_MEMORY Memory usage of row-store Multiversion Concurrency Control (MVCC) manager FALSE
M_RS_TABLE_VERSION_STATISTICS Information on row table versions: detailed version counts and used sizes FALSE
M_DYNAMIC_RESULT_CACHE cache entry's statistics for dynamic result cache FALSE
M_DYNAMIC_RESULT_CACHE_EXCLUSIONS cache exclusion list of dynamic result cache FALSE
M_RESULT_CACHE_RESET Result cache statistics TRUE
M_WORKLOAD_REPLAYS Workload replay statistics FALSE
M_WORKLOAD_REPLAY_PREPROCESSES Workload replay preprocess statistics FALSE
M_WORKLOAD_CAPTURES Workload capture statistics FALSE
M_WORKLOAD Database workload collected every minute FALSE
M_TABLE_STATISTICS Table DML Runtime Statistics FALSE
M_TABLE_STATISTICS_RESET Table DML Runtime Statistics TRUE
M_SQL_PLAN_CACHE Statistics of an individual execution plan FALSE
M_SQL_PLAN_CACHE_RESET Statistics of an individual execution plan TRUE
M_SQL_PLAN_CACHE_OVERVIEW Overall statistics of evicted and cached plans FALSE
M_SQL_PLAN_CACHE_PARAMETERS Bind parameters for statements cached in SQL Plan Cache. It saves parameter set of the most expensive execution for each plan FALSE
M_SQL_PLAN_CACHE_EXECUTION_LOCATION_STATISTICS Statistics of an individual execution plan execution location FALSE
M_SQL_PLAN_CACHE_EXECUTION_LOCATION_STATISTICS_RESET Statistics of an individual execution plan execution location TRUE
M_SQL_PLAN_STATISTICS Statistics of an individual execution plan FALSE
M_SQL_PLAN_STATISTICS_RESET Statistics of an individual execution plan TRUE
M_SQLSCRIPT_VARIABLE_CACHE SQLScript variable cache view to support runtime infomation FALSE
M_ACTIVE_PROCEDURES Statistics of Procedure Execution FALSE
M_CONNECTIONS Detailed information on connections between a client and database. Information includes connection status, client information, connection type, resource utilization FALSE
M_PREPARED_STATEMENTS Prepared statements list FALSE
M_ACTIVE_STATEMENTS Prepared statements list FALSE
M_REMOTE_CONNECTIONS Detailed information on remote connections between database and remote sources. Information includes connection status, adapter name and adapter properties FALSE
M_REMOTE_STATEMENTS Detailed information on executed remote queries. Information includes query status, number of fetched rows FALSE
M_RS_MEMORY RS Memory Statistics FALSE
M_SEQUENCES Sequence statistics FALSE
M_SESSION_CONTEXT Session variables for each connection FALSE
M_CONNECTION_STATISTICS Detailed statistics on each connection between an application and database FALSE
M_MVCC_OVERVIEW Overview of row-store Multiversion Concurrency Control (MVCC) manager FALSE
M_TABLE_SNAPSHOTS Snapshot information of tables which are blocked by table-wise GC FALSE
M_BLOCKED_TRANSACTIONS Transaction list waiting for locks FALSE
M_SHARED_MEMORY Use M_RS_MEMORY for all except TOPOLOGY. FALSE
M_MVCC_TABLES Statistics of row-store Multiversion Concurrency Control (MVCC) manager FALSE
M_EXPENSIVE_STATEMENT_EXECUTION_LOCATION_STATISTICS Statistics of expensive statement execution per location FALSE
M_DSO_OPERATIONS DSO operation statistics FALSE
M_CACHES Shows aggregated information on caches FALSE
M_CACHES_RESET Shows aggregated information on caches TRUE
M_CS_LOG_REPLAY_QUEUE_STATISTICS Public view of column store related information about log replay FALSE
M_CS_LOG_REPLAY_QUEUE_STATISTICS_RESET Public view of column store related information about log replay TRUE
M_DELTA_MERGE_STATISTICS Table delta merge statistics FALSE
M_CS_LOB_SPACE_RECLAIMS Overview of the last running lob garbage collections FALSE
M_CS_MVCC Columnstore MVCC information FALSE
M_CS_NSE_ADVISOR Statistics used to identify candidate objects that should be page loadable or column loadable FALSE
M_SERVICE_COMPONENTS Detailed information about the components of a service FALSE
M_TEMPORARY_TABLE_COLUMNS Columns of temporary tables FALSE
M_TEMPORARY_TABLES Temporary tables FALSE
M_TEMPORARY_VIEWS Temporary views FALSE
M_TEMPORARY_VIEW_COLUMNS Columns of temporary tables FALSE
M_TEMPORARY_KEY_FIGURES Column of temporary key figures FALSE
M_TEMPORARY_JOIN_CONDITIONS This view shows temporary join condition FALSE
M_TEMPORARY_JOIN_CONSTRAINTS This view shows temporary join constraints FALSE
M_TEMPORARY_OBJECT_DEPENDENCIES Dependencies between temporary objects, for example, temporary views which refer to a specific table FALSE
M_SQL_ANALYZER_PLAN_TRACES PLAN TRACE Metadata Statistics FALSE
M_TABLE_PERSISTENCE_LOCATION_STATISTICS Persistence storage statistics for tables partitions and services FALSE
M_SERVICE_MOVE_STATISTICS Service statistics of currently active service moves FALSE
M_SYSTEM_REPLICATION_MVCC_HISTORY Global MVCC Timestamp history in secondary site of System Replication FALSE
M_SERVICE_COMPONENT_MEMORY Service-specific memory usage by logical component FALSE
M_DATABASE_REPLICAS Overview of currently active database replicas where this is either source or target database FALSE
M_CS_ALL_COLUMN_STATISTICS Column store table runtime statistics FALSE
M_WORKAROUNDS Overview of all workarounds on each target and their current states. FALSE
M_SYSTEM_REPLICATION_TAKEOVER_HISTORY System Replication takeover history table FALSE
M_TABLES Information on row and column tables FALSE
M_TASKS Task Execution Statistics. FALSE
M_VIRTUAL_TABLE_REPLICA_ACTION_HISTORY Histroy information about replica tables generated by virtual table toggling FALSE
M_DATABASE_REPLICA_STATISTICS Service statistics of currently active database replicas FALSE
M_TRANS_TOKENS All transaction tokens which are currently active FALSE
M_WORKAROUND_CONFIGURATION_PREVIOUS_VALUES All previous values that were set before activating the workaround and will be restored when deactivating the workaround. FALSE
M_CONFIGURATION_PARAMETER_VALUES Configuration parameter values FALSE
M_VOLUME_REPLICATION_STATISTICS Service statistics for volume replication FALSE
M_VIRTUAL_TABLE_REPLICAS Detailed information about replica tables generated by virtual table toggling FALSE
M_REMOTE_TABLE_REPLICAS Remote table replication FALSE
M_DATA_VOLUMES DataVolume statistics FALSE
M_BACKUP_CATALOG Common data for all backup catalog entries FALSE
M_REMOTE_SOURCES Refresh operation refresh status for a remote source FALSE
M_MVCC_SNAPSHOTS Detailed information on snapshots of Multiversion Concurrency Control (MVCC) manager FALSE
M_DISK_USAGE Disk usage information on host basis group by usage_types FALSE
M_TABLE_LOB_STATISTICS Aggregated lob statistics per table partition and column. Only for lobs that are not stored inplace in the table. FALSE
M_BACKUP_CATALOG_FILES Location information of all backup catalog entries FALSE
M_CS_ALL_COLUMNS Runtime information from all columns of column tables, including internal ones FALSE
M_TABLE_PERSISTENCE_STATISTICS Persistence storage statistics for tables FALSE
M_TABLE_PERSISTENCE_LOCATIONS Column store tables and their physical data locations FALSE
M_CONSISTENCY_CHECK_HISTORY_ERRORS Information about errors reported by consistency check executions FALSE
M_SYSTEM_REPLICATION Information about replicated systems FALSE
M_TABLE_PERSISTENT_MEMORY_FILES Persistent memory file information FALSE
M_BACKUP_HISTORY_BROKEN Backup history broken entries FALSE
M_CONSISTENCY_CHECK_HISTORY Information about consistency check executions FALSE
M_CE_CALCSCENARIOS_OVERVIEW Overview of Calcscenarios without JSON representation FALSE
M_INIFILE_CONTENT_HISTORY Information about configuration changes from inifiles FALSE
M_SCHEDULER_JOBS Show information about current and past scheduler jobs FALSE
M_METADATA_CACHE_STATISTICS Metadata cache statistics FALSE
M_TABLE_PARTITIONS Partition specific memory and disk usage for partitioned tables FALSE
HANA DB 성능 향상 방법
결과세트를 작게 유지
SELECT single --> SELECT & loop Where 구문을 구체적으로 활용
전송되는 데이터의 양 최소화
SELECT * --> SELECT (field lists) 집계함수 (COUNT, MIN, MAX, SUM, AVG) 사용
데이터 전송 횟수 최소화
중첩 SELECT 대신 JOIN 및 / 또는 하위 쿼리 사용
SELECT ... FOR ALL ENTRIES 사용 DML (INSERT, MODIFY, ...) 시에 ARRAY 형태로 사용
검색 오버 헤드 최소화
적절한 인데스 사용
데이터베이스에서 부하유지
중복데이터 읽지 않기, TABLE BUFFER 사용.
SAP HANA database and storage snapshots
Storage snapshot의 장단점
장점
기본적으로 network, 전체적인 I/O 성능 및 HANA DB 자체의 영향을 거의 주지 않으며 대량의 데이터의 빠른 백업을 제공
반대로 빠른 DB 복구 (RTO)를 제공
Storage 업체에 따라 다름 (중복제거기술 등 감소된 disk space를 제공)
단점
Database의 내부적인 integrity check가 불가 (checksum calculation)
Storage snapshot은 손상된 데이터 page를 포함할 수 있음
각 서비스의 특정 data volume뿐만 아니라 전체 DB의 전체 데이터를 고려함
스토리지 시스템에서만 사용할 수 있고 다른 위치 또는 Media에 복제되지 않은 Storage snapshot은 재해 발생 시 Storage system 자체의 백업으로 사용할 수 없음
사용 방법
3단계로 수행 됨
STEP1 : PREPARE
HANA DB에서 PREPARE를 request
BACKUP DATA CREATE SNAPSHOT
여러 storage volume group간의 I/O consistency를 보장하기 위해서 HANA DB는 system wide savepoint에 기반한 internal snapshot을 생성함
각 database service의 data volume에 저장됨
이후 recovery에 사용될 해당 data의 snapshot으로 freeze됨
따라서 추가적인 storage system level에서의 action이 필요하지 않음
snapshot of a SAP HANA data volume must be an atomic operation
Internal snapshot은 각각의 데이터 볼륨을 가지고 있는 모든 SAP HANA database service에 걸쳐서 생성됨
영향받는 HANA Host들의 갯수에 독립적임
STEP2
모든 DB service의 전체 DB에 대한 Storage snapshot을 storage system의 기능으로 생성 함
STEP3: CONFIRM, ABANDON
Storage snapshot 성공/실패여부를 DB에 알림
BACKUP DATA CLOSE SNAPSHOT BACKUP_ID <backup_id> SUCCESSFUL <external_id>
BACKUP DATA CLOSE SNAPSHOT BACKUP_ID <backup_id> UNSUCCESSFUL <external_id>
DB service가 storage snapshot 생성 도중에 restart된 경우에 명령이 실패할 수 있음
Storage 벤더에서 HANA storage snapshot 기능을 포함한 plugins을 제공 (벤더에 확인 필요)
기타 내용
백업 대상
모든 HANA database seervice의 데이터 볼륨을 고려해야 함
Log area는 Point-in-time option을 포함한 DB 복구를 수행하기 위해서 storage snapshot에 포함되지 말아야 함
Log backups과 아직 백업되지 않은 log segments는 snapshot restore 후에 복구될 수 있음
데이터볼륨에 추가해서 HANA database metadata 정보가 필요
name server 데이터 볼륨이 저장된 곳과 동일한 directory에 OS파일로 위치
백업 가능 상태
HANA DB 상태는 ONLINE 상태여야 함 (PREPARE, CONFIRM 명령을 수행할 수 있는 상황)
HANA backup catalog에 storage snapshot 저장됨
snapshot 생성이 fail된 경우에도 저장됨
단계는 PREPARE, CONFIRM or ABANDON
HANA DB의 복구를 위한 사용방법
복구하는 경우에 데이터 영역에 storage snapshot이 존재해야 함
Storage system 에서 snapshot을 자동으로 복원할 수 없음 --> manual로 복구 해야 함
적합한 storage snapshot은 storage sytem admin console 또는 HANA studio의 recovery wizard에서 표시되는 HANA backup catalog를 통해서 확인 가능
데이터 area의 storage snapshot의 가용성은 HANA backup catalog overview에서 확인 가능
snapshot이 가용하면 Green 표시
Refresh 버튼을 통해서 상태 변경 가능
Restore된 storage snapshot과 연계된 log backup으로 point-in-time recovery 가능
한편으로 HANA DB는 storage snapshot만을 사용하여 복구 가능 --> 이경우 로그 영역이 암시적으로 초기화되고 redo log가 적용되지 않음
반면 특정 시점 복구는 로그 백업을 사용하여 수행할 수 있으며 아직 백업되지 않은 로그 세그먼트를 사용하여 임의의 특정 시점에 도달할 수 있음
HANA backup catalog에 저장되지 않은 storage snapshot의 사용은 가능
Backup catalog가 예외적인 상황으로 re-create 되면 storage snapshot은 catalog에 더이상 저장되지 않음
그런 경우에도 유효한 storage snapshot을 복구할 수 있음
storage system admin console을 이용해 적합한 snapshot을 확인해야 함
snapshot의 수동 restore 후에 recovery 마법사에서 유용한 snapshot을 보여줌
DB recovery 도중에 DB의 internal snapshot은 drop 됨
DB recovery가 실패하고 재수행되어야 한다면 storage snapshot은 다시 한번 Restore 되어야 함
HANA replication을 위해 snapshot과 internal snapshot간의 interface가 있는가?
Table replication
In a scale-out system tables (or selected columns of column store tables) may be replicated to multiple hosts.
Sysnchronous and Asynchronous Replication
Sub-Table Replication : 선택된 특정 Column들 만으로 replication 적용 가능 (Column store table 대상)
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini', 'system') SET ('table_replication', 'is_column_wise_replication_enabled') = 'true' WITH RECONFIGURE
Monitoring View : M_TABLE_REPLICAS
INMEMORY DB 특징
OLTP & OLAP
Applications
OLTP : Manage usual business, operational and management applications
OLAP : Management system, reporing and decision
Effectiveness
OLTP : Number of transactions per sec
OLAP : Response time per query
Volume
OLTP : Large number of short oline trancation (INSERT, UPDATE, DELETE)
OLAP : Low volume of complex analysical transaction (SELECT)
Space
OLTP : Operational data stored, typically small
Data Source
OLTP : Operational information of the application
OLAP : Historical and archive data
Focus
OLTP : Updating Data
OLAP : Retrieval of information
Uers
OLTP : Common staff
OLAP : Managers and Executives
Database는 OLAP와 OLTP 모두를 좋은 성능으로 처리할 수 있어야 하지만 대부분의 시스템에서는 가능하지 못함
Columnar storage가 OLAP 쿼리의 처리를 향상시키고 OLTP workload를 위해서 in-memory row storage가 높은 성능을 보장함
복합 부하((Mixed workload)를 처리하기 위해서 MVCC와 2단계 commit으로 최적화 된 Logging schema로 Reduced Locking이 적용됨
DB의 모든 Read가 Transaction의 일관된 뷰를 구성하는 모든 정보를 포함하는 transaction token에 의해 보장되는 일관된 스냅샷을 갖게 됨
2단계 commit을 최적화 하기 위해 첫번째 commit 이후에 log를 disk에 쓰고 두번째 commit은 비동기적으로 쓰여지게 됨
Encoding
Dictionary encoding
DB 병목구간을 줄이기 위해서 data access time을 줄이는 노력 -> 압축 -> Dictionary 활용
각 테이블의 컬럼 별
dictionary + attribute vector (hash table과 비슷) with valueID
많은 중복 데이터가 있는 경우에 특히 효율적
Search 처리 프로세스
요청 값을 Dictionary에서 search하여 해당되는 ValueID 찾음
Attribute를 ValueID로 Scan
Search result에서 ValueID를 해당되는 dictionary value로 replace
Encoding 후에 dictionary의 정렬을 통해서 access time을 줄일 수 있음
Sorted dictionaries의 lookup speed = O(long(n)) with binary search
하지만 dictionary에 존재하지않는 새로운 데이터의 입력은 dictionary의 re-sort 및 attribute vector의 update를 야기하여 Cost가 많이 소요됨
Compression
Memory는 여전히 제약이 있는 Resource로 cost를 줄이는 노력이 필요
이를 위해서 dictionary 적용 + 추가 compress 수행
압축기술은 가벼워야 하며 그렇지 않은 경우 encoding/decoding의 cost가 더 비싸짐
주로 사용되는 Compressiong 기술
Prefix Encoding - 특정 컬럼이 동일한 값의 long sequence를 가질 때 사용
Run-length Encoding - Cardinality가 낮고 발생 빈도가 낮은 경우에 사용
Cluster Encoding - Attribute vector가 동일 크기의 여러개의 cluster로 나눠질때 사용
(Cluster가 동일한 값을 가질 경우에 one value로 압축)
Sparse Encoding - 많은 empty, null값을 가진 경우
Indirect Encoding - Cluster encoding과 비슷, 특정 컬럼에 의해 정렬된 table에서 많이 사용되며 각 컬럼들간에 상호관계가 존재함. 각 cluster별 각각의 dictionary 이용
Data Layout
RDB가 2차원인 경우 in-memory에 모든 데이터는 1 차원 방식으로 저장 되어야 함
DB storage layer는 column, Row 또는 Hybrid layout를 결정해야 함
하지만 다양한 workload에 의해서 각 layout 별로 장점이 있을 수 있음
Hybrid는 각 장점을 모두 가짐
Parallelism
Query processing의 속도를 높이기 위해서 우리 데이터와 프로세스에 대한 병렬화가 적용됨
2가지 종류의 병렬 방식 존재
Pipeline parallelism = 뒤따르는 operation이 이전 operation 완료전 수행 가능
Data parallelism = 데이터를 나눠서 각기 처리 후 Merge는 방식
Data Aging and Archiving
모든 데이터가 시스템에서 사용되지는 않음
현재 데이터보다 10년 전 데이터의 사용 빈도는 낮을 것임
하지만 이전 데이터도 분석 쿼리를 위해서 사용될 수 있으므로 삭제는 불가함
이를 위해서 aging 기법을 사용할 수 있음
Hot / Cold 데이터를 다른 곳에 저장하지만 data schema 변경은 없으로 cold 데이터도 여전히 access 가능