[HANA] 문제 SQL문 확인하기

1. 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
;

 

2. 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'
;

 

 

3. 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 

 

 

4. 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')						
;