[DB/Oracle] SQL 튜닝 내용 정리 : 개념부터 DBMS_XPLAN, MONITOR까지

실행계획 분기 이미지

 

안녕하세요.

오늘은 오라클 환경에서 SQL 튜닝을 주제로, 기본 개념부터 실무에서 자주 사용하는 도구와 기법까지 정리해보겠습니다.
단순 나열이 아니라, 각 방법이 무엇에 좋고, 어떻게 활용하고, 어떤 출력이 나오며,

무엇을 중점적으로 봐야 개선 효과를 얻을 수 있는지까지 정리해 보겠습니다.

---

1. SQL 튜닝의 이해

정의

• SQL을 더 빠르고 효율적으로 실행되도록 개선하는 작업
• 정확한 결과를 최소한의 리소스로, 최대한 빠르게 도출
• 성능 향상, 리소스 절감, 운영 안정화

SQL 튜닝이 중요한 이유

• 전체 DB 성능 저하의 70~80%는 비효율적인 SQL
• 화면 지연, 배치 작업 지연 대부분은 SQL 튜닝으로 해결
• 잘못된 SQL은 CPU, I/O, TEMP 등 자원을 과도하게 사용
• 데이터 증가에도 안정적 성능 확보 가능

---

2. SQL 튜닝 대상과 목적

주요 튜닝 대상

• SQL 구문 : 불필요한 조인, SELECT *, 과도한 서브쿼리
• 인덱스 사용 : 미사용, 불필요, 컬럼 순서 오류
• 통계정보 : 옵티마이저 통계 부정확
• 조인 방식 : Nested Loop ↔ Hash Join 선택 오류
• 옵티마이저 힌트 : 잘못된 제어

목적

• 성능 향상 : 처리·응답시간 단축
• 리소스 최적화 : CPU/I/O 절약
• 확장성 확보 : 데이터 증가에도 안정성 유지
• 운영 안정화 : 장애·병목 예방

---

3. SQL 성능 접근 방법

SQL 성능 문제는 단순 실행계획 확인이 아니라 원인 분석이 핵심입니다.

• SQL 자체 : SELECT * 제거, 조건절 최적화
• 옵티마이저 판단 : 통계 재수집, 힌트 조정
• 인덱스 구조 : 적절한 인덱스 설계·제거
• 실행 환경 : AWR/ASH 분석, 리소스 튜닝

---

4. SQL 튜닝 유용한 방법 (정리)

아래는 실무에서 활용하는 대표적인 방법들입니다. 각 항목을 ① 목적 → ② 활용 → ③ 예시 → ④ 분석 포인트 & 개선 효과로 설명합니다.

---

4.1 EXPLAIN PLAN

① 목적

• SQL 실행 전 옵티마이저가 선택할 실행계획을 확인

② 활용

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.deptno=10;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

③ 예시 출력

| Id | Operation         | Name | Rows | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT  |      |    3 |    2 |
|* 1 |  NESTED LOOPS     |      |    3 |    2 |
|* 2 |   TABLE ACCESS FULL| EMP |    3 |    2 |
|* 3 |   TABLE ACCESS BY…| DEPT|    1 |    1 |

 

④ 분석 포인트 & 개선 효과

• Full Table Scan → Index Scan으로 개선 여지 확인
• Nested Loop ↔ Hash Join 선택 확인
• 실행 전 SQL 구문 자체의 비효율성을 조기 발견 가능

---

4.2 SQL*Plus AUTOTRACE

① 목적

• SQL 실행과 동시에 실행계획 + 통계정보 확인

② 활용

 

SET AUTOTRACE ON
SELECT * FROM emp WHERE deptno=10;

 

③ 예시 출력

Execution Plan
-------------------------------------------------
| Id | Operation         | Name | Rows | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT  |      |    3 |    2 |
|* 1 |  TABLE ACCESS FULL| EMP  |    3 |    2 |

Statistics
-------------------------------------------------
consistent gets  = 5
physical reads   = 0
bytes sent       = 456

 

④ 분석 포인트 & 개선 효과

• 블록 접근량(consistent gets, physical reads) 확인
• SQL 실행 시 I/O 부담 체크
• SQL 실행 비용의 체감 확인 가능

---

4.3 SQL Trace + tkprof

① 목적

• SQL 실행의 Parse, Execute, Fetch 단계별 리소스 사용 확인

② 활용

ALTER SESSION SET sql_trace = true;
-- SQL 실행
ALTER SESSION SET sql_trace = false;

 

③ 예시 출력

Parse   CPU=0.00s Consistent gets=2
Execute CPU=0.00s Consistent gets=0
Fetch   CPU=0.01s Consistent gets=5 Rows=3

 

④ 분석 포인트 & 개선 효과

• 파싱에 시간이 오래 걸리면 바인드 변수 활용 필요
• Fetch에서 블록 읽기 많으면 인덱스 튜닝 필요
• 병목 단계별 정확한 원인 파악 가능

---

4.4 DBMS_XPLAN.DISPLAY

① 목적

• EXPLAIN PLAN 결과를 보기 좋게 출력

② 활용

EXPLAIN PLAN FOR
SELECT * FROM emp e, dept d
WHERE e.deptno=d.deptno AND e.ename='benoit';

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

 

③ 예시 출력

Plan hash value: 395616093
| Id | Operation          | Name | Rows | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT   |      |    1 |    3 |
|* 1 |  NESTED LOOPS      |      |    1 |    3 |
|* 2 |   TABLE ACCESS FULL| EMP  |    1 |    2 |
|* 3 |   TABLE ACCESS BY… | DEPT |    1 |    1 |

Predicate Information:
  2 - filter("E"."ENAME"='benoit')

 

④ 분석 포인트 & 개선 효과

• Predicate 위치 확인 가능
• 실행계획 경로 및 조건절 적용 구간 파악

---

4.5 DBMS_XPLAN.DISPLAY_CURSOR

① 목적

• 실제 실행된 커서의 Runtime Plan 출력
• 추정치(E-Rows) vs 실제치(A-Rows) 비교

② 활용

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

 

③ 예시 출력

| Id | Operation          | E-Rows | A-Rows |
---------------------------------------------
|  0 | SELECT STATEMENT   |        |      3 |
|* 1 |  NESTED LOOPS      |      3 |      3 |
|* 2 |   TABLE ACCESS FULL|      3 |      3 |

 

④ 분석 포인트 & 개선 효과

• A-Rows ≠ E-Rows → 통계 부정확 → 튜닝 필요
• 실행 결과의 정확한 성능 확인 가능

---

4.6 gather_plan_statistics 힌트

① 목적

• SQL 실행 시 자동으로 실제 통계 수집

② 활용

SELECT /*+ gather_plan_statistics */ *
FROM emp e, dept d
WHERE e.deptno=d.deptno AND d.deptno=10;

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

 

③ 출력 예시

| Id | Operation         | E-Rows | A-Rows | A-Time |
------------------------------------------------------
|  2 | TABLE ACCESS FULL |      3 |      3 | 00:00:00 |

 

④ 분석 포인트 & 개선 효과

• 옵티마이저 추정치와 실제치 비교 → 통계 이상 발견
• 실행계획 신뢰성 검증 가능

---

4.7 STATISTICS_LEVEL=ALL

① 목적

• 세션 전체에 대해 gather_plan_statistics 효과 제공

② 활용

ALTER SESSION SET STATISTICS_LEVEL=ALL;

 

③ 출력 예시

• DBMS_XPLAN.DISPLAY_CURSOR 결과에 A-Rows, A-Time 포함

④ 분석 포인트 & 개선 효과

• 다수 SQL을 테스트할 때 유용
• 테스트 세션 전체 SQL의 실행 통계 자동 수집

---

4.8 MONITOR HINT + Real-Time SQL Monitoring

① 목적

• 장시간 실행 SQL의 진행 상황, 대기 이벤트 분석

② 활용

SELECT /*+ MONITOR gather_plan_statistics */ *
FROM emp e, dept d
WHERE e.deptno=d.deptno AND d.deptno=10;

SELECT sys.DBMS_SQLTUNE.REPORT_SQL_MONITOR(
         sql_id=>'cnfb9kz33gv3',
         report_level=>'ALL',
         type=>'TEXT')
FROM dual;

 

③ 출력 예시

Global Stats
------------
Elapsed Time (s) : 12.43
CPU Time (s)     : 1.21
Buffer Gets      : 1234
Physical Reads   : 56

Wait Activity
-------------
db file sequential read : 120 waits 6.21s
direct path read        :  30 waits 4.85s

 

④ 분석 포인트 & 개선 효과

• 대기 이벤트 분석 → I/O 병목, TEMP 과다 확인
• 실시간 병목 지점 파악, 장시간 배치 SQL 개선

---

4.9 SQL Tuning Advisor (DBMS_SQLTUNE)

① 목적

• SQL 단위 자동 분석 및 튜닝 권고 제공

② 활용

DECLARE
  tname VARCHAR2(30);
BEGIN
  tname := DBMS_SQLTUNE.CREATE_TUNING_TASK(
             sql_id=>'8ns3hdfn2',
             scope=>'COMPREHENSIVE');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(tname);
END;
/

 

③ 예시 출력

Recommendation:
  Consider creating index on EMP(ENAME)
  Consider rewriting query using EXISTS

 

④ 분석 포인트 & 개선 효과

• 인덱스 생성, 쿼리 리라이트 권고 확인
• 자동화된 개선 아이디어 확보

---

5. SQL Monitoring Report 예시 (전체)

SQL Monitoring Report
Global Information
------------------------------
 Status              : DONE (ALL ROWS)
 Instance ID         : 1
 SQL ID              : cnfb9kz33gv3
 Duration            : 00:00:12.43
 User                : SCOTT

Global Stats
------------------------------
 Elapsed Time (s)    : 12.43
 CPU Time (s)        : 1.21
 Buffer Gets         : 1,234
 Physical Reads      : 56
 Output Rows         : 3

Wait Activity
------------------------------
 db file sequential read   120 waits  6.21s
 direct path read           30 waits  4.85s

Plan Activity
---------------------------------------------------
| Id | Operation                     | A-Rows | E-Rows |
|  0 | SELECT STATEMENT              |      3 |        |
|  1 |  NESTED LOOPS                 |      3 |      3 |
|  2 |   TABLE ACCESS BY INDEX ROWID | DEPT   |      1 |
|  3 |   INDEX UNIQUE SCAN           | PK_DEPT|      1 |
|  4 |   TABLE ACCESS BY INDEX ROWID | EMP    |      3 |
|  5 |   INDEX RANGE SCAN            | IDX_EMP|      3 |
---------------------------------------------------

 

마무리

이번 글에서는 SQL 튜닝의 정의와 필요성부터 시작해서,

• 주요 튜닝 대상 및 접근 방법
• 각종 도구(EXPLAIN PLAN, AUTOTRACE, SQL Trace, DBMS_XPLAN, gather_plan_statistics, STATISTICS_LEVEL=ALL, MONITOR, DBMS_SQLTUNE)
• 각 방법별 목적·활용·출력 예시·분석 포인트·개선 효과

까지 정리했습니다.

 

👉 핵심은 E-Rows vs A-Rows 차이, 리소스 사용량(I/O, CPU, TEMP), **대기 이벤트(Wait Event)**를 함께 분석해야 한다는 점입니다.