SQL 추적 기능 (SQL_TRACE) 활용

SQL 추적(SQL_TRACE) 개요

Oracle 데이터베이스에서 SQL 추적(SQL_TRACE) 기능은 개별 SQL 문에 대한 성능 정보를 제공하는 강력한 도구입니다. 이를 통해 개발자와 DBA는 SQL 문 실행 중 발생하는 병목 현상을 진단하고 성능 개선을 위한 방향을 설정할 수 있습니다. SQL 추적 파일은 파싱 횟수, 실행 시간, CPU 사용량, 디스크 I/O 횟수, 대기 이벤트 등의 정보를 포함합니다.

SQL 추적 활성화 방법

1. 데이터베이스 인스턴스 레벨 추적 활성화

모든 세션에 대해 SQL 추적을 활성화하려면 데이터베이스 인스턴스 레벨에서 추적을 설정합니다. 이를 위해서는 DBA 권한이 필요합니다.


EXEC DBMS_MONITOR.DATABASE_TRACE_ENABLE(INSTANCE_NAME => 'orcl', waits => TRUE, binds => FALSE);
  • INSTANCE_NAME: 추적할 인스턴스 이름입니다.
  • waits => TRUE: 대기 이벤트 정보를 포함합니다.
  • binds => FALSE: 바인드 변수 값을 제외합니다.

2. 세션 레벨 추적 활성화

특정 세션에 대해서만 SQL 추적을 활성화할 수 있습니다. 이 방법은 특정 사용자의 활동을 모니터링할 때 유용합니다.


EXEC DBMS_SESSION.SET_SQL_TRACE(sql_trace => TRUE);

3. 특정 SQL 문에 대한 추적 활성화 (힌트 사용)

특정 SQL 문에 대해서만 추적을 활성화하려면 힌트를 사용합니다. 이 방법은 특정 쿼리의 성능을 분석할 때 유용합니다.


SELECT /*+ MONITOR */ employee_id, last_name
FROM employees
WHERE department_id = 50;

SQL 추적 파일 생성 및 위치

SQL 추적이 활성화되면 데이터베이스 서버 프로세스는 추적 정보를 운영체제 파일로 기록합니다. 이 파일의 위치는 데이터베이스 파라미터 diagnostic_dest에 의해 결정됩니다.


SHOW PARAMETER diagnostic_dest;

일반적으로 추적 파일은 $ORACLE_BASE/diag/rdbms/DB_NAME/INSTANCE_NAME/trace 디렉토리에 생성됩니다.

TKPROF 유틸리티를 이용한 추적 파일 분석

SQL 추적 파일은 사람이 읽기 어려운 형식으로 저장되므로, TKPROF 유틸리티를 사용하여 사람이 읽기 쉬운 형식으로 변환해야 합니다. TKPROF는 SQL 추적 파일을 분석하고 실행 계획, 통계 정보, 대기 이벤트 등을 포함한 보고서를 생성합니다.


tkprof trace_file output_file explain=user/password@connect_string
  • trace_file: 분석할 SQL 추적 파일 경로입니다.
  • output_file: 생성될 분석 보고서 파일 경로입니다.
  • explain: 실행 계획을 표시하기 위한 사용자/비밀번호@연결 문자열입니다.

TKPROF 보고서 예시


SQL ID: b7xe767xygyj3
Plan Hash Value: 4146454717

SELECT /*+ MONITOR */ employee_id, last_name
FROM employees
WHERE department_id = 50

call count cpu elapsed disk query current rows
------- ------ -------- ------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 0 14 107
------- ------ -------- ------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 0 14 107

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 99

Rows Row Source Operation
------ --------------------------------------------------
 107 TABLE ACCESS FULL EMPLOYEES (cr=31 pr=0 pw=0 time=116us cost=4 size=84 card=107)

위 보고서는 다음과 같은 정보를 제공합니다:

  • SQL ID: SQL 문의 고유 식별자
  • Plan Hash Value: 실행 계획의 해시 값
  • SQL 문 텍스트
  • 각 단계별 통계 정보 (파싱, 실행, 가져오기)
  • 실행 계획

실무 적용 예시

특정 사용자 계정 (예: test_user)에서 실행되는 모든 SQL 문의 추적 파일을 생성하고, TKPROF를 사용하여 분석하는 과정을 설명합니다.

1. 추적 활성화


-- DBA 권한으로 접속
CONNECT system/password AS SYSDBA

-- 특정 사용자 세션에 대한 추적 활성화
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => 'test_user', waits => TRUE, binds => FALSE);

2. SQL 실행

이제 test_user 계정으로 접속하여 성능 문제가 발생하는 SQL 문을 실행합니다.


-- test_user 계정으로 접속
CONNECT test_user/password

-- 성능 문제가 발생하는 SQL 문 실행
SELECT * FROM employees WHERE employee_id = 123;

3. 추적 중지


-- 다시 DBA 권한으로 접속
CONNECT system/password AS SYSDBA

-- 추적 중지
EXEC DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => 'test_user');

4. 추적 파일 찾기

생성된 추적 파일은 diagnostic_dest 파라미터에 지정된 디렉토리에서 찾을 수 있습니다.

5. TKPROF 분석


tkprof trace_file output_file explain=test_user/password@connect_string

6. TKPROF 보고서 분석

TKPROF 보고서를 열어 실행 계획, 통계 정보, 대기 이벤트 등을 분석하여 성능 개선 방안을 도출합니다.

SQL 추적 시 주의사항

  • SQL 추적은 데이터베이스 성능에 영향을 미칠 수 있으므로, 필요한 경우에만 활성화해야 합니다.
  • 바인드 변수 값을 포함하면 보안에 민감한 정보가 노출될 수 있으므로, 주의해야 합니다.
  • 장시간 추적을 활성화하면 디스크 공간이 부족해질 수 있으므로, 주기적으로 추적 파일을 관리해야 합니다.

결론

SQL 추적(SQL_TRACE)은 Oracle 데이터베이스 성능 튜닝에 필수적인 도구입니다. 위에 제시된 방법과 예시를 통해, 개발자와 DBA는 SQL 문 성능 문제를 효과적으로 진단하고 개선할 수 있습니다.

위로 스크롤