데이터베이스 & 성능 최적화/오라클 성능 튜닝 가이드

실전 Oracle 성능 분석! AUTOTRACE vs SQL Trace 차이와 활용법 (tkprof 포함)

프로잉2 2025. 7. 2. 18:16
728x90

오라클 튜닝을 공부하다 보면 가장 먼저 부딪히는 벽이 바로 **"실행계획이 뭐지?"**  
그리고 그 다음엔 이런 생각이 들죠.

> 이 쿼리 진짜 느린데, 왜 느린지는 모르겠네…

그래서 오늘은 실무와 튜닝 공부에서 정말 많이 쓰이는 두 가지 기능인  
**AUTOTRACE**, **SQL TRACE + tkprof** 에 대해 정리해봤습니다.





1️⃣ AUTOTRACE란?

AUTOTRACE는 SQL을 실행하면, 그 결과와 함께 **실행계획 + 통계 정보**를 보여주는 도구입니다.  
SQL*Plus 또는 Oracle SQL Developer에서 사용 가능합니다.

  • TABLE ACCESS FULL, INDEX RANGE SCAN 같은 실행계획 키워드를 볼 수 있어요.
  • 특히 Rows, Cost, Time 정보를 보면서 쿼리 효율을 체크할 수 있습니다.

0. SQL*Plus 실행 및 로그인 진행
1. autotrace on 설정하기

ALTER SESSION SET SQL_TRACE = TRUE;

2. sql 실행하기(태스트용 테이블_emp을 조회하면서 발생하는 실행 계획 보기 위해서 DML 활용)

SELECT * FROM EMP;

3. autotrace off하기

ALTER SESSION SET SQL_TRACE = FALSE;

4. trc파일 위치 확인하기

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

응답값으로 폴더위치가 나옵니다. 
예시: ~~\trace\orcl_ora_123456.trc
5.CMD(명령어 프롬프트)을 열어서 4번에 나온  trc폴더 위치로 이동(trace 폴더까지만 이동)

cd C:\APP\C\사용자이름\diag\rdbms\orcl\orcl\trace

6. 이어서  trc폴더에서 명령어 하나 입력(사용자가 볼 수 있게 txt로 변환)

tkprof orcl_ora_123456.trc output.txt

tkprof orcl_ora_5번에 나온 수치.trc output.txt


7. output.txt 생성 확인 

Trace file: orcl_ora_123456.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT * 
FROM
 EMP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         83          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1335      0.00       0.02          0       1466          0       20000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1337      0.00       0.02          0       1549          0       20000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 9  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     20000      20000      20000  TABLE ACCESS FULL EMP (cr=1466 pr=0 pw=0 time=25668 us starts=1 cost=39 size=2129673 card=24479)

********************************************************************************

SQL TRACE + tkprof란?

AUTOTRACE가 한 번의 쿼리를 분석한다면,
SQL TRACE는 전체 세션의 모든 SQL 실행을 추적해주는 기능이에요.

트레이스 결과는 .trc 파일로 저장되며, Oracle이 제공하는 tkprof로 사람이 읽기 좋게 변환합니다.

 

tkprof 해석 예시

  • query: 논리적 블록 읽기
  • disk: 실제 디스크 접근 횟수
  • rows: 반환된 레코드 수
  • cpu, elapsed: 처리 시간

✅ 실제로 인덱스를 탔다면 INDEX UNIQUE SCAN 같은 문구도 뜹니다!

 

실습 사례 정리

이번 글을 위해 직접 테스트도 진행해봤습니다.

  • EMP 테이블에 2만 건 데이터 삽입
  • ORA-01438, ORA-00001 오류 발생 및 해결
  • PRIMARY KEY 제약 조건 수정
  • AUTOTRACE 및 tkprof 분석 수행

 

728x90