SQL 성능 문제 진단 절차: 증상 정의부터 개선 검증까지

핵심 요약

SQL 성능 진단은 실행계획에서 “나빠 보이는 Operation”을 먼저 고르는 작업이 아닙니다. 사용자가 경험한 문제를 시간·업무·응답시간·처리량·동시성·입력값으로 정의하고, End-to-End Response Time에서 Database가 차지한 비중을 분리한 다음, Database 내부의 DB Time·CPU·Wait·Top SQL·실제 실행계획을 연결해야 합니다.

증상 정의
  → 측정 계약과 정상 Baseline
  → End-to-End 구간 분리
  → DB Time을 CPU·Foreground Non-Idle Wait로 분해
  → 문제 Service·Session·SQL·실행 식별
  → 실제 Child Plan과 작업량 확인
  → 최초 병목 증거 선정
  → 원인 가설 수립
  → 한 가지 변경
  → 동일 조건 반복 측정
  → Regression·운영 영향·Rollback 검증

Oracle SQL Tuning의 핵심은 구체적이고 측정 가능하며 달성 가능한 목표를 정하고, 확인된 Bottleneck을 대상으로 반복적으로 개선하는 것입니다.

좋은 진단
  = 증상 범위
  + 정상 비교 기준
  + 실제 시간 소비 위치
  + 불필요한 Row·Block·Call
  + 변경 가능한 원인
  + 동일 조건 검증

나쁜 진단
  = 높은 Cost 하나
  + 느려 보이는 Operation 이름
  + 임의의 Hint·Index·Parameter 변경

성능 개선은 “몇 초 빨라졌다”만으로 끝나지 않습니다.

• 사용자 P95·P99와 Timeout이 목표를 만족하는가
• 동일 업무량에서 CPU·Buffers·Reads·Wait가 줄었는가
• 처리량과 Peak 동시성이 유지되는가
• 대표 Bind와 다른 SQL·DML에 Regression이 없는가
• 통계 갱신·재기동 후에도 Plan 전략이 안정적인가
• 배포 중단·Rollback 기준이 준비됐는가

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 응답 시간 분석 범위에서 성능 문제 진단의 전체 절차를 다룹니다. 각 Index·Join 방식의 내부 튜닝, AWR·ASH·SQL Trace의 상세 출력은 앞선 이론과 후속 이론에서 다룹니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• “느리다”는 표현을 재현 가능한 측정 계약으로 바꾼다.
• 정상 Baseline을 단일 최저값이 아닌 정상 범위와 변동성으로 정의한다.
• Client·Network·Application·Database·OS 시간을 분리한다.
• DB Time을 DB CPU와 Foreground Non-Idle Wait로 분해한다.
• Total·Per Execution·Per Business Unit 기준으로 Top SQL을 선정한다.
• V$SQL 누적 통계와 단일 실행·전체 Fetch 여부를 구분한다.
• 실제 Child Plan에서 최초 Cardinality·Row·Buffer 급증 지점을 찾는다.
• SQL·Application·Concurrency·System 원인 가설을 구분한다.
• 증거·가설·변경·예상 지표를 직접 연결한다.
• 한 번에 한 가지 주요 가설만 변경해 인과관계를 검증한다.
• Warm-up·반복 횟수·Fetch·Bind·동시성·Cache 조건을 통제한다.
• 평균·중앙값·P95·P99·처리량·오류율을 함께 비교한다.
• Index·Hint·통계·Application 변경의 부작용과 Regression을 확인한다.
• Monitoring·Alert·Canary·Rollback을 포함한 운영 종료 조건을 정의한다.

1. “느리다”를 측정 계약으로 바꾼다

다음 표현만으로는 진단할 수 없습니다.

주문 화면이 가끔 느리다.
Batch가 예전보다 오래 걸린다.
Database가 전체적으로 느리다.

측정 계약은 다음 정보를 고정합니다.

예시

대상          : ORDER_API / SEARCH
문제 시간     : 2026-07-31 10:07~10:10 KST
정상 P95      : 0.7~1.0초
문제 P95      : 6.5초
요청량        : 120 requests/sec
동시 사용자   : 80
대표 Bind     : customer_type='VIP', date_range=90일
Fetch         : 첫 페이지 20행
Timeout       : 30건
목표          : P95 < 1.2초, Timeout 0건

측정 계약이 없으면 변경 전에는 전체 Fetch, 변경 후에는 첫 20행만 Fetch하는 등 서로 다른 작업을 비교할 수 있습니다.

2. 평균과 한 번의 최고 기록으로 판단하지 않는다

99건 × 0.1초 = 9.9초
 1건 × 20초  = 20초
-------------------
평균          ≈ 0.3초

평균은 낮지만 한 사용자는 20초를 경험합니다. 온라인 업무에서는 다음을 함께 봅니다.

• Median·P50
• P95·P99
• 최대·Timeout·Cancel
• 처리량·동시 요청
• Error·Retry Rate
• 시간대별 분포

Batch는 다음 지표가 더 중요할 수 있습니다.

• 전체 완료시간
• 단계별 Elapsed
• Row 처리량
• Commit·Checkpoint 주기
• 재시작 지점
• Peak CPU·PGA·TEMP·I/O

2.1 반복 측정

한 번의 실행만으로 개선을 확정하지 않습니다.

Warm-up
  → Cursor·Cache·JIT·Connection 준비

반복 실행
  → 동일 Bind·Fetch·동시성으로 여러 번 측정

평가
  → Median·P95·P99와 작업량 분포 확인

Cold Cache와 Warm Cache는 다른 업무 조건입니다. 운영 Database에서 Cache를 무리하게 Flush하여 비교하지 않고 실제 사용 조건을 재현합니다.

3. 정상 Baseline을 범위로 만든다

Baseline은 가장 빠른 한 번이 아니라 정상 범위와 변동성입니다.

정상 P95          : 0.7~1.0초
정상 TPS          : 100~140
정상 Gets/Exec    : 800~1,100
정상 CPU/Exec     : 20~35ms
정상 Timeout      : 0~1건/10분

다음 조건을 맞춥니다.

• 같은 업무와 Application Version
• 유사한 데이터량·분포
• 같은 요일·시간대·Batch 일정
• 같은 동시성·Connection Pool 설정
• 같은 대표 Bind·Fetch 범위
• 같은 Instance·PDB·Service
• Cache·통계정보·Optimizer 환경 차이 기록

Baseline과 문제 구간의 업무량이 다르면 초당 총량뿐 아니라 Request·Transaction·Execution·Row당 값을 비교합니다.

4. End-to-End 시간을 계층별로 분리한다

End-to-End Response Time
├─ Client Rendering·Think Time
├─ Network·DNS·Load Balancer
├─ Application Queue
├─ Business Logic·External API
├─ Connection Pool Wait
├─ Database Call
└─ Serialization·Response Transfer

예시

전체 응답시간            6.5초
Application Queue        0.3초
Connection Pool Wait     2.0초
Database Calls           3.8초
Network·Serialization    0.4초

Database Call을 50% 개선하면 전체는 약 4.6초입니다. 사용자 목표가 1초라면 Pool·Application 영역도 함께 개선해야 합니다.

Amdahl 관점

개선 가능한 최대 효과
  ≤ 해당 구간이 전체에서 차지한 시간

Database Call이 전체 8초 중 2초라면 SQL만으로 줄일 수 있는 이론적 최대는 2초 이내입니다.

5. Database 구간을 DB CPU와 Wait로 분해한다

ΔDB Time
  ≈ ΔDB CPU
  + ΔForeground Non-Idle Wait

Wait Event는 시간 소비 위치입니다. Event 이름만으로 Storage·Index·Application 원인을 확정하지 않습니다.

5.1 AAS

AAS = ΔDB Time / Elapsed

CPU AAS와 Wait Class AAS를 나누면 평균 동시 활동량의 성격을 파악할 수 있습니다.

6. 문제 SQL과 실행을 선정한다

Top SQL은 목표에 따라 다르게 봅니다.

SQL A
  1,000,000회 × 5ms
  → 전체 부하 큼

SQL B
  10회 × 30초
  → 단건 사용자 지연 큼

6.1 누적값과 단일 실행

V$SQL은 Child Cursor가 Load된 이후 여러 실행의 누적값입니다. 평균이 정상이어도 일부 실행이 매우 느릴 수 있습니다.

SELECT에서는 다음도 확인합니다.

EXECUTIONS
END_OF_FETCH_COUNT
FETCHES
ROWS_PROCESSED

END_OF_FETCH_COUNT < EXECUTIONS이면 일부 실행이 전체 Fetch 전에 종료됐을 수 있습니다.

Parallel SQL의 누적 ELAPSED_TIME은 QC와 PX Worker 시간을 합쳐 벽시계보다 클 수 있으므로 개별 실행은 SQL Monitor·Trace에서 확인합니다.

7. 실제 실행계획에서 최초 작업량 급증을 찾는다

예상 계획이나 Cost만 보지 않고 실제 Child Plan과 Runtime Statistics를 확인합니다.

Starts
E-Rows
A-Rows
A-Time
Buffers
Reads
OMem·1Mem·Used-Mem·Used-Tmp
Predicate
Adaptive·Parallel 여부

반복 Operation에서는 단위를 맞춥니다.

예상 총 행 ≈ Starts × E-Rows
실제 1회 평균 = A-Rows ÷ Starts

7.1 최초 오차 지점

Leaf Object Access부터 올라가며 다음을 찾습니다.

1. 예상과 실제가 처음 크게 달라진 Operation
2. 후보 Row가 급증한 Operation
3. Buffer·Read·TEMP가 집중된 Branch
4. 늦게 적용된 Filter·Join
5. 반복 Starts가 큰 Inner Operation

최상위의 큰 오차는 하위 오차가 전파된 결과일 수 있습니다.

7.2 Partial Fetch

첫 페이지 20행만 요청한 SQL과 전체 100,000행을 Fetch한 SQL의 A-Rows·Buffers·Elapsed는 직접 비교할 수 없습니다. Client Row Limit·Array Fetch·Timeout·Cancel 조건을 기록합니다.

8. 원인을 계층별로 분류한다

8.1 SQL·Plan 원인

• 부적절한 Access Path·Join Order
• Cardinality 추정 오류
• Data Skew·Bind 선택도
• 늦은 Filter
• 과도한 Sort·Hash·TEMP Spill
• Function·암시적 형변환
• Plan Regression·Child Cursor 차이

8.2 Application 원인

• N+1 Query
• 동일 SQL 반복 호출
• 작은 Array Fetch Size
• Row별 Commit
• 불필요한 Column·대량 결과
• Connection Pool 부족·Leak
• Transaction 범위가 사용자 입력까지 유지됨

8.3 Concurrency 원인

• TX Row Lock
• Hot Index Leaf·Segment Header
• Latch·Mutex·Library Cache 경합
• RAC Hot Block
• 동시 Worker 과다

8.4 System·Resource 원인

• CPU 포화·Run Queue
• Storage Latency·대역폭
• PGA 부족·TEMP Spill
• Network Latency·Packet Loss
• Memory Pressure·Swapping
• Backup·Batch·Maintenance 충돌

한 증상에 여러 계층의 병목이 함께 존재할 수 있습니다.

9. 증거와 개선안을 직접 연결한다

좋은 개선안은 다음 구조를 가집니다.

증거
  → 원인 가설
  → 변경
  → 기대 지표
  → 부작용·Rollback

예시 1: Top-N 과다 접근

증거
  최종 20행
  Index 후보 200,000행
  Table Buffers 350,000

가설
  정렬·Filter를 지원하지 못해 Top-N Stop이 늦음

변경
  Filter + ORDER BY를 지원하는 Index 후보 검증

기대
  후보 Row·Table Access·Buffers·Elapsed 감소

부작용
  DML Index 유지비·Storage·Hot Leaf

예시 2: N+1 Query

증거
  화면 1회에 같은 SQL 501회
  단건 2ms, 총 1초 이상
  Network Round Trip 증가

가설
  Application 반복 Call이 총 지연 생성

변경
  Batch Fetch·Join·IN List·Prefetch

기대
  Executions·User Calls·Round Trip 감소

부작용
  SQL Text 크기·결과량·메모리 증가

예시 3: Row Lock

증거
  Application Wait AAS가 최상위
  동일 Final Blocker
  장시간 Open Transaction

가설
  Transaction 범위와 갱신 순서 문제

변경
  Transaction 축소·일관된 Lock 순서·불필요한 사용자 Think Time 제거

기대
  Lock Wait·P99·Timeout 감소

부작용
  Commit 빈도·Redo·정합성 검토

10. 한 번에 한 가지 주요 가설을 변경한다

다음 변경을 동시에 수행하면 인과관계를 알기 어렵습니다.

Index 추가
+ SQL Rewrite
+ 통계 재수집
+ Optimizer Parameter 변경
+ Cache Size 변경

권장 순서입니다.

1. 증거와 가설 기록
2. 한 가지 주요 변경
3. 동일 조건으로 반복 측정
4. 기대 지표 변화 확인
5. 부작용·Regression 확인
6. 채택 또는 Rollback
7. 다음 병목 분석

관련된 하나의 변경 세트는 허용할 수 있지만 변경 목적과 Rollback 단위를 명확히 합니다.

11. 재현 조건을 통제한다

비교 전에 다음을 고정하거나 기록합니다.

• SQL Text·Hint·Child·Plan Hash
• Bind 값·Data Type·분포
• Fetch 행 수·Array Size
• 동시 사용자·Worker
• 데이터량·Partition 범위
• 통계정보·Histogram
• Optimizer Parameter
• Cache Warm-up·Storage Cache 조건
• Parallel Degree
• Session·Service·PDB·Instance
• Background Batch·Backup

11.1 Cache Flush 금지 원칙

운영 Database에서 Shared Pool이나 Buffer Cache Flush는 다른 Session에 큰 영향을 주고 비현실적인 Cold 상태를 만들 수 있습니다. 실제 업무 환경을 재현하거나 별도 검증 환경을 사용합니다.

12. 변경 전후 검증 지표

사용자·업무 지표

• Median·P95·P99·Maximum
• Timeout·Error·Retry
• TPS·Request/sec·Rows/sec
• Batch 완료시간
• 업무 건당 처리량

Database·SQL 지표

• DB Time·AAS
• DB CPU·Wait Class AAS
• Elapsed·CPU/Execution
• Buffer Gets·Reads/Execution
• Gets·CPU·Redo/Row
• Executions·Parse Calls·Fetches
• Starts·E-Rows·A-Rows
• TEMP·PGA·Redo·Undo
• Lock Wait·Commit Wait

안정성 지표

• 대표 Bind·극단 Bind
• Peak 동시성
• Plan Hash·Child 변화
• 통계 갱신 후 Plan
• 재기동·배포 후 성능
• 다른 Service·SQL Regression

12.1 반복성과 변동성

변경 전후 각각 여러 번 측정하고 다음을 비교합니다.

• Median
• P95·P99
• 표본 수
• 정상 변동 범위
• Outlier 원인
• 업무량당 지표

단일 최고 기록이 아니라 분포가 안정적으로 개선됐는지 판단합니다.

13. Regression과 부작용 확인

13.1 Index 추가

• INSERT·UPDATE·DELETE 유지비
• Redo·Undo
• Storage·Buffer Cache
• Hot Leaf·Clustering
• 통계 수집시간
• 다른 SQL Plan 변화
• Drop·Invisible·Rollback 절차

13.2 SQL Rewrite·Hint

• 결과 정합성·NULL·중복
• 다른 Bind·데이터 분포
• Version·Transformation 차이
• Hint의 장기 유지보수
• Plan Baseline·Patch와 충돌

13.3 통계정보

• Sample Size·Method Opt
• Histogram 변화
• Global·Partition·Incremental 통계
• 다른 SQL Plan 영향
• Pending Statistics·Rollback

13.4 Application 변경

• Call 수·Result Size·Memory
• Connection Pool 사용
• Transaction·Commit 의미
• Retry·Idempotency
• Timeout·Circuit Breaker

14. 끝까지 추적하는 사례

14.1 증상

ORDER_API / SEARCH
정상 P95   0.8초
문제 P95   6.5초
문제 구간  10:07~10:10
대표 Bind  VIP, 90일
결과       20행

14.2 End-to-End

Database Call  6.0초
Application    0.3초
Network        0.2초

Database가 주요 범위입니다.

14.3 DB Time

User I/O AAS   4.0
CPU AAS        1.2
기타 Wait      0.3

14.4 Top SQL

특정 SQL_ID·Plan Hash
Elapsed/Exec      5.8초
Gets/Exec      300,000
Reads/Exec       15,000

14.5 실제 Plan

INDEX RANGE SCAN
  Starts        1
  E-Rows      100
  A-Rows  200,000

TABLE ACCESS
  A-Rows  180,000
  Buffers 350,000

SORT STOPKEY
  최종         20

14.6 가설

Filter와 ORDER BY를 충분히 지원하지 못해 후보 ROWID·Table Access·Sort가 과도합니다.

14.7 변경

대표 데이터와 DML 영향을 검토한 후 Filter·정렬을 지원하는 Index 후보를 별도 환경에서 검증합니다.

14.8 결과

P95             6.5초 → 0.9초
Gets/Exec     300,000 → 1,200
Reads/Exec     15,000 → 20
후보 A-Rows   200,000 → 20
TPS               120 → 135
DML CPU            +4%

14.9 운영 검증

• 일반·VIP·극단 고객 Bind
• Peak 동시성
• 통계 갱신 후 Plan
• 주문 DML 처리량
• Index Storage·Redo
• Canary 배포
• 이상 시 Rollback

15. 종료 조건

성능 문제는 단순히 더 빠른 Plan이 나왔다고 종료하지 않습니다.

사용자 목표

• P95·P99·Timeout이 목표 충족
• Throughput 유지·증가
• 오류·정합성 문제 없음

원인 제거

• 최초 Cardinality·Row·Buffer 급증 감소
• CPU·Wait·Call 부하 감소
• 같은 업무량에서 개선 재현

안정성

• 대표·극단 Bind에서 허용 범위
• Peak 동시성에서 안정
• 통계 갱신·재기동 후 Plan 전략 유지
• 다른 SQL·DML Regression 허용 범위

운영 준비

• Monitoring Dashboard·Alert
• Plan·SQL·업무 지표 Baseline
• Canary·배포 단계
• Rollback 조건·Script
• 담당자·확인 시간대

16. 혼동하기 쉬운 기준

17. 최종 진단 체크리스트

[증상]
□ 날짜·시각·업무·P95·P99·TPS·Timeout을 기록했는가
□ 대표 Bind·Fetch·동시성을 기록했는가

[범위]
□ End-to-End에서 DB 비중을 분리했는가
□ Instance·PDB·Service·Module·Action이 일치하는가

[원인]
□ DB Time을 CPU·Foreground Wait로 분해했는가
□ Top SQL을 총량·실행당·업무 영향으로 선정했는가
□ 실제 Child Plan과 최초 작업량 급증을 확인했는가
□ Application Call·Lock·OS Resource를 연결했는가

[변경]
□ 증거·가설·변경·예상 지표가 직접 연결되는가
□ 한 가지 주요 가설만 변경했는가
□ 부작용·Rollback을 정의했는가

[검증]
□ 동일 Bind·Fetch·동시성·Cache 조건인가
□ 여러 번 측정하고 Median·P95·P99를 비교했는가
□ 업무당 CPU·I/O·Call이 감소했는가
□ 다른 Bind·DML·SQL Regression을 확인했는가

[운영]
□ Canary·Monitoring·Alert가 준비됐는가
□ 중단·Rollback 조건이 문서화됐는가