song-ps

실행계획, TRACE

11 min read

실행계획 확인 방법 (Oracle / SQL Server)

(Oracle)

  • EXPLAIN PLAN / DBMS_XPLAN.DISPLAY

오라클이 기본으로 실행계획을 확인하는 방법은 EXPLAIN PLAN을 수행하여 실행계획을 PLAN_TABLE에 저장한 뒤, 이를 조회하는 것이다.

SQL * Plus 예시:

  • EXPLAIN PLAN FOR …
  • SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, ‘TYPICAL’));

참고로 DESCRIBE, EXPLAIN은 SQL*Plus 명령어다.

(SQL Server)

  • 예상 실행계획 출력

SQL Server에서 예상 실행계획을 출력하는 방법은 간단하다.

  • SET SHOWPLAN_TEXT ON 실행 후 SQL을 수행한다.

(03) Oracle “TYPICAL”에서 기본으로 확인 가능한 정보

오라클 예상 실행계획에서 기본으로( DBMS_XPLAN.DISPLAY 함수 세 번째 인자에 ‘TYPICAL’ 입력) 확인 가능한 정보는 다음과 같다.

  • Plan Hash Value
  • 오브젝트 액세스 방식(Operation)
  • 오브젝트 명(Name)
  • 예상 Cardinality(= Rows)
  • 예상 데이터 크기(Bytes)
  • 예상 Cost / 예상 CPU Time
  • 조건절 정보(Predicate Information)

또한 DBMS_XPLAN.DISPLAY 함수 세 번째 인자에 ALIAS, OUTLINE, ADVANCED 등을 지정하면 Query Block / Object Alias, Outline 정보, Column Projection 정보 등을 추가로 확인할 수 있다.

SET SHOWPLAN_TEXT ON

SQL SERVER 에서 SQL 트레이스 ON 하려고 할 때, SHOWPLAN_TEXT 를 ON 으로 하면 TRACE 가 보이지 않는다.

가장 큰 문제는 이 명령이 ‘실행 단계’를 생략한다는 것이다. SHOWPLAN_TEXT ON을 설정하면 SQL Server는 쿼리를 최적화하고 실행 계획만 생성한 뒤 작업을 중단한다.

  • 문제:
    • 쿼리 결과(Data)를 확인할 수 없으며, 쿼리 수행에 걸린 시간이나 실제 리소스 소모량을 측정할 수 없다.
  • 대조:
    • 오라클의 EXPLAIN PLAN FOR와 동일한 동작이며, 이는 ‘예상(Estimated) 계획’일 뿐이다.

SHOWPLAN_TEXT ON은 쿼리를 실행하지 않고 예측치만 보여주는 반쪽짜리 도구다. 실제 운영 환경에서 발생하는 성능 병목을 잡으려면 쿼리를 직접 수행하는 STATISTICS XML이나 Extended Events를 사용하는 것이 전문가적 접근이다.

SHOWPLAN_TEXT는 매우 오래된 방식이며, 텍스트 형태의 출력은 복잡한 실행 계획을 분석하기에 매우 부적합하다.

  • 문제: 현재 Microsoft는 이 기능 대신 XML 형태의 보기를 권장한다. 텍스트 방식은 연산자 간의 상관관계나 병렬 실행 여부를 추적하기 어렵다.

AutoTrace / SQL Trace / TKPROF

(05) AutoTrace 옵션 AutoTrace에서 사용할 수 있는 옵션은 아래와 같다.

  • SET AUTOTRACE ON
    • SQL을 실행하고 결과집합과 함께 예상 실행계획 + 실행통계 출력
  • SET AUTOTRACE ON EXPLAIN
    • SQL을 실행하고 결과집합과 함께 예상 실행계획 출력
  • SET AUTOTRACE ON STATISTICS
    • SQL을 실행하고 결과집합과 함께 실행통계 출력
  • SET AUTOTRACE TRACEONLY
    • SQL은 실행하지만 결과는 출력하지 않고 예상 실행계획 + 실행통계만 출력
  • SET AUTOTRACE TRACEONLY EXPLAIN
    • SQL을 실제로 실행하지 않고 예상 실행계획만 출력
  • SET AUTOTRACE TRACEONLY STATISTICS
    • SQL은 실행하지만 결과는 출력하지 않고 실행통계만 출력

statistics_level 파라미터를 ALL로 설정하면 SQL 트레이스 정보가 메모리에 수집될 수 있다. gather_plan_statistics는 파라미터가 아니라 옵티마이저 힌트다.

TKPROF 사용

SQL Trace 파일을 분석해서 리포트 파일을 생성하려면 TKPROF를 사용한다. 일반적인 사용법은 다음과 같다.

  • tkprof <tracefile> <reportfile> sys=no
    • 첫 번째 인자: 트레이스 파일명
    • 두 번째 인자: 결과(리포트) 파일명(확장자는 임의 가능)
    • sys=no: 내부적으로 수행되는 SYS SQL(딕셔너리 조회 등)을 제외
      • (필요 시 EXPLAIN=, SORT=, PRINT= 등의 옵션을 추가로 사용 가능)

Oracle SQL Trace에서 확인 가능한 정보 예시

  • Parse / Execute / Fetch 단계별 CPU, elapsed, disk, query, current, rows
  • Library cache misses
  • Optimizer mode
  • Parsing user id
  • Row Source Operation
    • (예: TABLE ACCESS BY INDEX ROWID, INDEX UNIQUE SCAN 등)

AutoTrace의 “recursive calls”

AutoTrace의 recursive calls는 하드 파싱 과정에서 딕셔너리를 조회하거나, DB 저장형 함수(PL/SQL 등)에 내장된 SQL을 수행할 때 발생하는 Call 횟수를 의미한다.

GATHER_PLAN_STATISTICS와 DISPLAY_CURSOR

SQL에 /*+ gather_plan_statistics */ 힌트를 지정하면, 트레이스 정보를 서버 파일이 아니라 SGA 메모리에 기록한다. SGA에 저장된 트레이스 정보는 뷰에서 직접 조회할 수도 있지만, 보통 DBMS_XPLAN.DISPLAY_CURSOR를 이용해 분석하기 쉬운 형태로 출력한다.

DBMS_XPLAN.DISPLAY_CURSOR는 일반적으로

  • 첫 번째/두 번째 인자에 NULL, NULL을 주면 “바로 직전에 실행한 커서”를 대상으로 출력한다.
  • 특정 SQL을 지정하려면 SQL_ID 및 CHILD_NUMBER가 필요하다.

SQL Monitor

MONITOR 힌트는 실시간 SQL 모니터링을 위해 사용한다. 실시간 SQL 모니터링 리포트는 DBMS_SQLTUNE.REPORT_SQL_MONITOR 함수로 출력한다.

DISPLAY_CURSOR (ALLSTATS LAST) 출력 항목 예시

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

주요 항목:

  • Starts: 각 실행 단계의 실행 횟수
  • E-Rows: 옵티마이저가 예상한 Rows
  • A-Rows: 실제 처리한 Rows
  • A-Time: 단계별 소요 시간
  • Buffers: 캐시에서 읽은 버퍼 블록 수
  • Reads: 디스크에서 읽은 블록 수

(DBMS_XPLAN vs 전통적 SQL Trace 매핑)

  • A-Rows ↔ rows (각 단계에서 읽거나 갱신한 건수)
  • A-Time ↔ time (각 단계별 소요 시간)
  • Buffers ↔ cr(=query) + current (캐시에서 읽은 버퍼 블록 수)
  • Reads ↔ pr (디스크로부터 읽은 블록 수)

(SQL Server) 실행 통계 관련 옵션

SQL Server에서 트레이스/통계를 확인하고자 설정하는 옵션은 아래와 같다.

  • SET STATISTICS PROFILE ON
    • 쿼리 결과집합 뒤에 실행 프로필(연산자 처리 행 수, 실행 횟수 등)을 추가 결과집합으로 표시
  • SET STATISTICS IO ON
    • 각 Transact-SQL 문 실행 후 디스크/버퍼 I/O 활동 정보를 표시
  • SET STATISTICS TIME ON
    • 구문 분석/컴파일/실행에 사용한 시간을 밀리초 단위로 표시
구분Trace (SQL Profiler / Extended Events)Actual Execution Plan
주요 목적서버 전체 또는 특정 세션의 활동 모니터링특정 쿼리의 내부 동작 및 성능 병목 분석
수집 정보CPU 사용량, 읽기/쓰기 횟수(I/O), 실행 시간, 시작/종료 시간 등인덱스 사용 여부, 조인 알고리즘, 연산자별 실제 행 수(Actual Rows), 경고(Spill 등)
실행 여부쿼리가 실행되는 동안 이벤트로 기록됨반드시 쿼리가 실행 완료되어야 생성됨
사용 도구SQL Server Profiler, 확장 이벤트(Extended Events)SSMS(SQL Server Management Studio) 실행 계획 탭
부하 정도필터링 없이 대량 수집 시 서버에 큰 부하를 줄 수 있음쿼리 실행 시 추가적인 런타임 통계를 수집하므로 오버헤드 발생

Trace (추적)

Trace는 서버에서 발생하는 ‘이벤트’를 추적합니다. 주로 SQL Server Profiler나 최신 방식인 **Extended Events(확장 이벤트)**를 사용합니다.

  • 언제 쓰는가?
    • 어떤 애플리케이션에서 어떤 쿼리를 보내는지 모를 때
    • 특정 시간대에 서버가 느려진 원인(가장 느린 쿼리 Top 10 등)을 찾고 싶을 때
    • 데드락(Deadlock)이 발생하는 시점과 관련 쿼리를 포착할 때

Actual Execution Plan (실제 실행 계획)

쿼리 최적화 도구(Optimizer)가 선택한 경로와 실제 실행 결과를 결합하여 보여줍니다.

  • 언제 쓰는가?
    • 특정 쿼리가 왜 느린지 분석할 때 (예: 인덱스 스캔 대신 풀 스캔을 하는지 확인)
    • 예상했던 데이터 양(Estimated)과 실제 처리된 데이터 양(Actual)의 차이를 비교하여 통계 정보가 최신인지 확인할 때
    • 쿼리 튜닝 후 성능이 실제로 개선되었는지 연산자 단위로 비교할 때

Graph View

Backlinks