오라클 SQL TRACE와 TKPROF 활용법

 

1. SQL TRACE란?

SQL TRACE는 Oracle Database에서 SQL 실행 시 발생하는 세부적인 성능 데이터를 수집하는 기능입니다.
이 도구는 SQL의 실행 경로, 대기 이벤트, 수행 시간, I/O 사용량 등의 정보를 기록하여 성능 문제를 분석하는 데 유용합니다.
SQL TRACE는 주로 다음과 같은 경우에 활용됩니다:

• SQL 실행 성능 문제 진단
• 응답 시간이 긴 쿼리의 원인 분석
• 애플리케이션의 데이터베이스 호출 최적화

---

2. SQL TRACE 활성화 방법

SQL TRACE는 세션 단위로 활성화됩니다. 아래는 활성화 방법입니다.

(1) 세션에서 SQL TRACE 활성화

현재 세션에서 SQL TRACE를 활성화하려면 아래 명령어를 사용합니다:

ALTER SESSION SET SQL_TRACE = TRUE;

(2) 특정 세션에서 SQL TRACE 활성화

DBMS_MONITOR 패키지를 사용하면 특정 세션에서 TRACE를 활성화할 수 있습니다.

BEGIN
  DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 123, serial_num => 456);
END;
/

(3) TRACE 파일 저장 위치 확인

TRACE 데이터는 서버의 UDUMP(User Dump Destination)에 저장됩니다. 위치를 확인하려면 다음을 실행합니다:

SHOW PARAMETER user_dump_dest;

---

3. TKPROF란?

TKPROF는 SQL TRACE의 결과 파일을 사람이 읽기 쉽게 변환해주는 도구입니다. TRACE 파일은 원래 포맷이 복잡하여 직접 분석하기 어렵기 때문에, TKPROF를 사용하여 데이터를 구조화된 형태로 변환합니다.
TKPROF의 주요 기능은 다음과 같습니다:

• SQL 실행 시간 및 대기 이벤트 요약
• 실행 계획(Execution Plan) 출력
• 성능 문제를 유발하는 SQL을 식별

---

4. TKPROF 활용 방법

(1) TKPROF 명령 실행

SQL TRACE 파일을 분석하려면 아래와 같은 명령어를 사용합니다:

tkprof input_file output_file EXPLAIN=user/password SORT=(prsela,exeela,fchela)

주요 옵션

• input_file: TRACE 파일 경로
• output_file: 변환된 결과를 저장할 파일 경로
• EXPLAIN: SQL 실행 계획을 포함하도록 설정
• SORT: 결과 정렬 기준 (예: prsela는 파싱 시간 기준)

예를 들어:

tkprof /u01/app/oracle/trace/mytrace.trc /u01/app/oracle/trace/mytrace.out EXPLAIN=scott/tiger SORT=(exeela)

(2) TKPROF 결과 해석

TKPROF 결과는 아래와 같은 주요 섹션으로 구성됩니다:

1. SQL 문장:
   각 SQL 쿼리와 함께 실행 통계가 출력됩니다.
2. 실행 통계:
   • PARSE: SQL 파싱 시간
   • EXECUTE: SQL 실행 시간
   • FETCH: 결과 가져오기 시간
3. 대기 이벤트:
   SQL 실행 중 발생한 주요 대기 상태를 보여줍니다.
4. SQL 실행 계획:
   SQL 쿼리의 옵티마이저가 선택한 실행 경로를 표시합니다.

---

5. SQL TRACE와 TKPROF 활용 예시

예시 1: 특정 쿼리의 실행 성능 분석

1. 세션에서 SQL TRACE 활성화:
2. ALTER SESSION SET SQL_TRACE = TRUE;
3. 문제 쿼리 실행:
4. SELECT product_id, product_name FROM products WHERE category = 'Electronics';
5. TRACE 파일 생성 후 TKPROF로 분석:
6. tkprof /u01/app/oracle/trace/trace1.trc /u01/app/oracle/trace/trace1.out EXPLAIN=hr/hr SORT=(execnt)

---

예시 2: 다중 세션의 TRACE 분석

1. 특정 세션에 대해 TRACE 활성화:
2. EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 234, serial_num => 789);
3. 세션 종료 후 생성된 TRACE 파일을 TKPROF로 변환:
4. tkprof /u01/app/oracle/trace/session234.trc /u01/app/oracle/trace/session234.out EXPLAIN=admin/admin

---

예시 3: 대기 이벤트 중심의 문제 진단

TRACE 파일에서 WAIT 이벤트를 TKPROF로 분석하여 성능 병목을 파악합니다.

tkprof /u01/app/oracle/trace/tracefile.trc /u01/app/oracle/trace/trace_analysis.out SORT=(waitevent)

결과 파일에서 특정 대기 이벤트(예: db file sequential read)가 비정상적으로 높은 경우 해당 원인을 탐색합니다.

---

6. 주의사항 및 최적 활용 팁

1. TRACE 활성화 범위 조정:
   TRACE는 성능 오버헤드를 유발할 수 있으므로 문제 진단 범위를 최소화해야 합니다.
2. TRACE 데이터 보안:
   TRACE 파일에는 민감한 데이터(SQL, 바인드 변수 등)가 포함될 수 있으므로 관리에 주의해야 합니다.
3. 정렬 옵션 사용:
   TKPROF의 SORT 옵션을 적절히 활용하여 주요 문제를 신속히 파악합니다.

---

7. 결론

SQL TRACE와 TKPROF는 Oracle Database 성능 문제를 분석하는 데 강력한 도구입니다. TRACE 파일을 통해 세부적인 실행 데이터를 수집하고, TKPROF를 활용하여 분석 결과를 시각화함으로써 성능 병목을 해결할 수 있습니다.

위에서 설명한 절차와 예제를 따라 성능 문제를 효과적으로 진단하고, 데이터베이스의 최적화를 진행해 보세요!