UI5 모드
BTP

HANA 성능 Trace 없이 될까요? #shorts #SAP #HANA

수정: 2026년 7월 11일 👁 0 · ♥ 0
▶ YouTube에서 보기

개요 및 배경

SAP HANA 성능 튜닝을 시작할 때 가장 흔한 실패 원인은 "느낌"으로 튜닝하는 것입니다. 어떤 SQL이 얼마나 자원을 소모하는지 데이터 없이 인덱스를 추가하거나 쿼리를 뜯어고치면, 오히려 다른 쿼리의 성능이 떨어지거나 리소스가 낭비됩니다. Expensive Statement Trace는 임계값을 초과하는 문장을 자동으로 기록하여 튜닝의 근거 데이터를 제공하는 기능입니다. 이 글에서는 활성화 방법, 임계값 설계, M_EXPENSIVE_STATEMENTS 분석, 실행 계획 판독, 그리고 실제 SalesOrder 조회 시나리오 최적화까지 다룹니다.

핵심 개념 이해

Expensive Statement Trace는 이름 그대로 "비싼 문장"을 기록합니다. 여기서 "비싼"의 기준은 사용자가 정하는데, 실행 시간(threshold), CPU 사용, 메모리 사용, 결과 행 수 등 여러 축이 있습니다. Trace가 활성화되면 매 SQL 실행 종료 시점에 인덱스서버가 각 축의 값을 임계값과 비교하고, 하나라도 초과하면 해당 문장의 메타데이터를 M_EXPENSIVE_STATEMENTS에 남깁니다.

비유하자면 CCTV와 같습니다. 상시 녹화(모든 SQL 로깅)는 비용이 커서 불가능하지만, 움직임 감지(임계값 초과)가 있을 때만 프레임을 남기면 저장 공간도 아끼고 사후 분석도 가능합니다.

이 기능이 튜닝의 출발점인 이유는 명확합니다. 병목의 90%는 상위 10개 SQL이 만들어냅니다. 이 상위 SQL을 특정하지 못한 채 "왠지 인덱스가 부족한 것 같아서" 추가하면, 컬럼 스토어에서는 오히려 Delta Merge 부하와 메모리 증가를 초래합니다. 실제 사례 중 상당수는 Trace를 켜자마자 특정 배치 잡 하나가 리소스의 60%를 쓰고 있다는 사실이 드러납니다.

Trace의 동작 흐름은 다음과 같이 요약할 수 있습니다.

  1. 클라이언트가 SQL을 실행합니다.
  2. Execution Engine이 실행을 완료합니다.
  3. Trace 필터가 실행 통계를 임계값과 비교합니다.
  4. 초과 시 statement string, parameters, plan_id, 실행 시간, 메모리, 사용자, 애플리케이션 소스 등을 M_EXPENSIVE_STATEMENTS에 삽입합니다.
  5. 수집된 데이터는 Cockpit의 SQL Analyzer에서 실행 계획과 함께 시각화할 수 있습니다.

여기서 반드시 알아야 할 파라미터는 global.ini의 [expensive_statement] 섹션에 있습니다. enable, threshold_duration(마이크로초), threshold_memory, threshold_cpu_time, trace_parameter_values, use_in_memory_tracing 등이 핵심입니다. 특히 use_in_memory_tracing = true로 두면 디스크가 아닌 링버퍼에 저장되어 오버헤드가 크게 줄어드는 반면, 재시작 시 데이터가 사라진다는 트레이드오프가 있습니다.

환경 구성 및 준비물

실습은 SAP HANA Cloud QRC 2/2025 또는 HANA 2.0 SPS 07 이상을 기준으로 합니다. 관리 UI는 SAP HANA Cockpit 2.0 SP20 이상을 권장하며, 온프레미스 환경에서는 HANA Studio 2.3.x도 사용할 수 있습니다. 필요한 권한은 다음과 같습니다.

  • TRACE ADMIN — Trace 활성/비활성 제어
  • CATALOG READ — 시스템 뷰 조회
  • MONITORING 롤 — Cockpit UI 접근
  • DB Explorer 또는 SQL Console 접근 권한

작업 대상 스키마는 SALES_OPS로 가정하며, 테이블 SALES_ORDER_HEADER(약 4.2억 건), SALES_ORDER_ITEM(약 18억 건), BUSINESS_PARTNER(약 900만 건)를 사용합니다. Trace 파일 저장 공간은 최소 10GB 이상을 확보해야 장기간 수집이 가능합니다.

Trace 활성화와 기본 조회

먼저 시스템 파라미터로 Trace를 켭니다. 운영에서 처음 켤 때는 임계값을 넉넉하게 잡아 최상위 병목만 잡는 것이 정석입니다. 무턱대고 100ms로 잡으면 로그가 폭발합니다.

-- 인덱스서버 레벨에서 활성화
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM')
  SET ('expensive_statement','enable') = 'true' WITH RECONFIGURE;

-- 실행 시간 3초(3,000,000 마이크로초) 초과만 수집
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM')
  SET ('expensive_statement','threshold_duration') = '3000000' WITH RECONFIGURE;

-- 파라미터 값도 함께 기록 (바인드 변수 확인용)
ALTER SYSTEM ALTER CONFIGURATION ('indexserver.ini','SYSTEM')
  SET ('expensive_statement','trace_parameter_values') = 'true' WITH RECONFIGURE;

수집이 시작되면 아래 쿼리로 상위 병목을 확인합니다.

SELECT TOP 20
  STATEMENT_HASH,
  DB_USER,
  APPLICATION_USER,
  APPLICATION_SOURCE,
  DURATION_MICROSEC / 1000 AS DURATION_MS,
  CPU_TIME / 1000 AS CPU_MS,
  MEMORY_SIZE / 1024 / 1024 AS MEMORY_MB,
  RECORDS,
  START_TIME,
  STATEMENT_STRING
FROM M_EXPENSIVE_STATEMENTS
WHERE START_TIME > ADD_DAYS(CURRENT_TIMESTAMP, -1)
ORDER BY DURATION_MICROSEC DESC;

실전 예제 — SalesOrder 조회 쿼리 진단

실제로 발견된 문제 쿼리 하나를 예로 들어보겠습니다. 특정 판매조직의 최근 90일 미출고 주문을 파트너별로 집계하는 쿼리인데, 평균 12초가 걸리고 하루 4천 회 이상 실행됩니다.

SELECT
  h.SALES_ORG,
  h.SOLD_TO_PARTNER,
  COUNT(DISTINCT h.SALES_ORDER_ID) AS OPEN_ORDER_CNT,
  SUM(i.NET_AMOUNT)                AS OPEN_AMOUNT
FROM SALES_OPS.SALES_ORDER_HEADER h
JOIN SALES_OPS.SALES_ORDER_ITEM   i
  ON i.SALES_ORDER_ID = h.SALES_ORDER_ID
WHERE h.SALES_ORG = ?
  AND h.CREATED_AT >= ADD_DAYS(CURRENT_DATE, -90)
  AND i.DELIVERY_STATUS <> 'C'
GROUP BY h.SALES_ORG, h.SOLD_TO_PARTNER;

Trace에서 STATEMENT_HASH를 얻은 뒤 실행 계획을 뽑아봅니다.

-- Plan 캐시에서 계획 확인
SELECT OPERATOR_NAME, TABLE_NAME, OUTPUT_SIZE,
       EXECUTION_ENGINE, DETAILS
FROM M_SQL_PLAN_CACHE_PLAN_EXPLAIN
WHERE PLAN_ID = (
  SELECT TOP 1 PLAN_ID FROM M_SQL_PLAN_CACHE
  WHERE STATEMENT_HASH = '<해시값>'
  ORDER BY LAST_EXECUTION_TIMESTAMP DESC
)
ORDER BY OPERATOR_ID;

결과에서 다음 시그널을 확인합니다.

  • COLUMN SEARCH Operator의 OUTPUT_SIZE가 수억 행이면 필터 푸시다운이 실패한 것
  • JOIN Operator에서 HASH JOIN 대신 NESTED LOOP가 나오면 통계가 오래된 것
  • AGGREGATION이 조인 이후로 밀렸다면 조기 집계(pre-aggregation) 기회가 있음

위 쿼리는 DELIVERY_STATUS가 낮은 카디널리티(C, P, N 3가지)이고 <>연산자로 인해 인덱스 활용이 어려웠습니다. DELIVERY_STATUS IN ('P','N')으로 재작성하니 필터 선택도가 개선되었고 실행 시간이 12초에서 1.8초로 떨어졌습니다.

프로덕션 운영 — 자동화와 안전장치

운영 환경에서는 Trace 자체가 부담이 되지 않도록 관리해야 합니다. 아래는 일 단위로 상위 병목을 요약해 알림 테이블에 저장하는 프로시저 실전 예제입니다.

CREATE OR REPLACE PROCEDURE SALES_OPS.SP_COLLECT_EXPENSIVE_DAILY
LANGUAGE SQLSCRIPT AS
BEGIN
  DECLARE v_run_at TIMESTAMP = CURRENT_TIMESTAMP;

  INSERT INTO SALES_OPS.TUNING_CANDIDATE
  SELECT
    v_run_at,
    STATEMENT_HASH,
    COUNT(*)                      AS EXEC_CNT,
    AVG(DURATION_MICROSEC)/1000   AS AVG_MS,
    MAX(DURATION_MICROSEC)/1000   AS MAX_MS,
    AVG(MEMORY_SIZE)/1024/1024    AS AVG_MEM_MB,
    MIN(APPLICATION_SOURCE)       AS SAMPLE_SOURCE
  FROM M_EXPENSIVE_STATEMENTS
  WHERE START_TIME BETWEEN ADD_DAYS(v_run_at,-1) AND v_run_at
  GROUP BY STATEMENT_HASH
  HAVING COUNT(*) >= 5
  ORDER BY AVG_MS DESC;

  -- 오래된 후보는 보관 정책에 따라 정리
  DELETE FROM SALES_OPS.TUNING_CANDIDATE
   WHERE COLLECTED_AT < ADD_DAYS(v_run_at, -30);
END;

또한 Trace 파라미터를 환경별로 분리하는 것이 안전합니다. 개발계에서는 500ms, 스테이징 1초, 운영 3초를 시작점으로 잡고 2주 관찰 후 조정합니다. 민감 정보가 파라미터에 실릴 수 있으므로 trace_parameter_values는 개인정보 컬럼이 있는 시스템에서는 false로 두거나 마스킹 뷰를 별도로 구성합니다.

-- 개인정보 컬럼 마스킹 뷰 예시
CREATE VIEW SALES_OPS.V_EXPENSIVE_MASKED AS
SELECT
  START_TIME, DB_USER, APPLICATION_SOURCE,
  DURATION_MICROSEC, MEMORY_SIZE, RECORDS,
  REGEXP_REPLACE(STATEMENT_STRING,
    '(EMAIL|SSN|CARD_NO)\s*=\s*''[^'']*''',
    '\1=''***''') AS STATEMENT_MASKED
FROM M_EXPENSIVE_STATEMENTS;

인덱스 vs 쿼리 재작성 vs 파라미터 조정 — 의사결정 흐름

Expensive Statement Trace로 병목 SQL을 찾은 뒤 어떤 처방을 내릴지 결정하는 프레임워크를 정리합니다.

  • 실행 계획에 TABLE SCAN이 가득하고 카디널리티가 높은 컬럼 필터가 있을 때 → 복합 인덱스 추가. 단, HANA 컬럼 스토어는 Column Search 자체가 병렬 스캔을 지원하므로 무분별한 인덱스 추가는 금물입니다. Delta Merge 비용을 감안해 변경 빈도가 낮은 컬럼 위주로 생성합니다.
  • NESTED LOOP JOIN이 반복되는 경우 → UPDATE STATISTICS로 통계를 최신화한 뒤 계획을 재확인합니다. 통계가 오래되면 옵티마이저가 조인 순서를 잘못 판단합니다.
  • 부정 조건(<>, NOT IN)이 필터에 있는 경우 → IN 리스트로 전환하거나 CASE 표현식으로 재작성합니다. 부정 조건은 인덱스를 우회하는 경향이 있습니다.
  • AGGREGATION이 대용량 조인 이후에 위치한 경우 → 서브쿼리로 먼저 집계한 뒤 조인하는 방식(pre-aggregation)을 검토합니다.
  • Plan Cache에서 동일 해시 쿼리가 매번 재컴파일되는 경우 → CACHE 힌트나 Result Cache를 적용합니다. 특히 읽기 전용 마스터 데이터 조회에 효과적입니다.

의사결정 순서는 통계 갱신 → 쿼리 재작성 → Result Cache → 인덱스 추가 순으로, 가능한 한 스키마 변경 없이 해결하는 것을 원칙으로 합니다.

흔한 실수와 트러블슈팅

가장 자주 보는 실수는 Trace를 켜지 않은 채로 "느리다는 신고"에만 반응해 인덱스를 추가하는 것입니다. HANA 컬럼 스토어는 대부분의 경우 인덱스 없이도 빠르며, 오히려 인덱스가 Delta Merge 비용과 메모리를 늘립니다. Trace 데이터가 있어야 "정말 이 쿼리가 느린가", "얼마나 자주 실행되나", "어떤 파라미터일 때 느린가"를 확인할 수 있습니다.

Q1. Trace를 켰는데 데이터가 안 쌓입니다.
threshold를 너무 높게 잡았거나, 방금 켠 뒤 Plan Cache가 비어서 그렇습니다. M_EXPENSIVE_STATEMENTS는 실시간 뷰이므로, 임계값을 초과하는 문장이 실제로 실행되어야 합니다. 임시로 threshold를 500ms로 낮추고 대표 배치를 돌려보세요.

Q2. 실행 계획을 봐도 어떤 Operator가 병목인지 모르겠습니다.
M_SQL_PLAN_CACHE_PLAN_EXPLAIN 대신 Cockpit의 SQL Analyzer를 사용하면 시간축 기반 시각화로 어떤 Operator가 실제 시간을 잡아먹는지 명확히 보입니다. EXEC_TIME이 총 시간의 20% 이상인 Operator부터 손봅니다.

Q3. Trace가 성능을 떨어뜨리진 않나요?
threshold를 합리적으로 설정하면 오버헤드는 1% 미만입니다. use_in_memory_tracing을 켜면 더 낮아집니다. 다만 threshold를 0에 가깝게 잡으면 모든 SQL을 기록하려 하므로 성능이 눈에 띄게 떨어집니다.

Q4. Trace 데이터는 얼마나 오래 보관되나요?
기본은 파일 회전 정책을 따르지만, 장기 분석이 필요하다면 위 실전 예제처럼 별도 히스토리 테이블로 스냅샷을 남기는 것이 좋습니다. 원본 뷰만 믿으면 하루 이틀 지난 데이터가 사라져 있는 경우가 흔합니다.

Q5. 민감 정보가 Trace에 남는 것이 우려됩니다.
trace_parameter_values = false로 설정하면 바인드 변수 값이 기록되지 않습니다. 파라미터 값 없이도 STATEMENT_HASH 기반으로 대부분의 튜닝 분석이 가능합니다. 마스킹 뷰를 추가로 구성하면 DBA 이외 권한으로도 안전하게 분석 업무를 위임할 수 있습니다.

실무 적용 체크리스트

프로젝트에서 Expensive Statement Trace를 도입할 때 챙겨야 할 항목들을 정리합니다.

  • 환경별 threshold 기준 수립 (개발 500ms / 스테이징 1s / 운영 3s)
  • TRACE ADMIN, CATALOG READ, MONITORING 권한 체계 정비
  • use_in_memory_tracing = true 설정으로 디스크 오버헤드 최소화
  • Trace 데이터 히스토리 테이블 및 수집 프로시저 생성
  • 개인정보 포함 컬럼 마스킹 뷰 구성
  • CI 파이프라인에 상위 20개 쿼리 성능 회귀 검사 추가
  • Cockpit SQL Analyzer 접근 권한 팀 내 공유
  • 2주 주기 threshold 재검토 및 조정 스케줄 등록

댓글 0

아직 댓글이 없습니다.