Top SQL 선정: V$SQL의 총량·실행당 부하·Child Cursor

핵심 요약

V$SQL은 Shared Pool에 존재하는 SQL의 Child Cursor별 성능 통계를 보여 줍니다. Top SQL은 가장 큰 숫자 하나로 결정하지 않고 목적에 맞는 관점을 조합해 선정합니다.

총량
  → 문제 구간의 DB Time·CPU·Logical I/O를 많이 소비한 SQL

실행당 부하
  → 한 번 실행할 때 비싼 SQL

완료 실행당 부하
  → 전체 Fetch를 완료한 실행을 기준으로 본 비용

행당 효율
  → 결과·처리 행 한 건을 만들기 위해 사용한 자원

실행 빈도·Call 구조
  → 한 번은 가볍지만 지나치게 자주 호출되는 SQL

Cursor 공유
  → Parse·Load·Invalidation·Child Cursor 증가 비용이 큰 SQL

같은 SQL_ID 아래에도 여러 Child Cursor와 Plan이 존재할 수 있습니다.

SQL_ID abc123
├─ Child 0 / Plan 111 / Bind·Environment A
├─ Child 1 / Plan 222 / Bind·Environment B
└─ Child 2 / Plan 111 / Bind·Environment C

Child 0과 2의 PLAN_HASH_VALUE가 같아도 Bind Metadata, Optimizer Environment, Shareable 상태와 실제 작업량은 다를 수 있습니다.

Top SQL 선정의 기본 절차는 다음과 같습니다.

1. 문제 시간·Service·Module·업무 목표 확정
2. 구간 Delta 또는 신뢰 가능한 구간 통계 확보
3. SQL_ID·Child·Plan·Instance·Container 분리
4. Total·Per Execute·Per Completed Execute·Per Row 계산
5. Parse·Load·Invalidation·Version 문제 확인
6. 실제 Plan·Bind·Wait·업무 중요도 연결
7. 같은 조건으로 개선 후 재측정

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → SQL 분석 도구 → 응답 시간 분석 범위에서 현재 Cursor 통계를 이용한 Top SQL 선정 방법을 다룹니다. SQL Trace·TKPROF, AWR·ASH Report 전체 사용법과 실행계획별 상세 튜닝은 후속 이론에서 다룹니다.

학습 목표

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

• V$SQL, V$SQLAREA, V$SQLSTATS, V$SQLAREA_PLAN_HASH의 기본 단위를 구분한다.
• V$SQL이 Child Cursor별 통계임을 설명한다.
• V$SQLSTATS가 더 긴 보존성을 가지지만 Child 진단용 View가 아님을 설명한다.
• Total·실행당·완료 실행당·행당 지표를 계산한다.
• EXECUTIONS, END_OF_FETCH_COUNT, FETCHES, ROWS_PROCESSED를 구분한다.
• 시스템 총부하 SQL과 단건 SLA SQL의 우선순위를 구분한다.
• PARSE_CALLS, LOADS, INVALIDATIONS, VERSION_COUNT의 위치와 의미를 설명한다.
• 같은 SQL_ID에서 Child·Plan별 작업량을 분리한다.
• RAC·CDB 환경에서 INST_ID, CON_ID를 포함해 분석한다.
• Cursor Age Out·Reload·Parent 재생성 시 Snapshot Counter가 초기화될 수 있음을 설명한다.
• Snapshot Key에 Child 주소·Load 시각을 추가해야 하는 이유를 설명한다.
• Parallel SQL의 ELAPSED_TIME을 벽시계 시간으로 단정하지 않는다.
• MODULE, ACTION이 최초 Parse Context라는 주의를 설명한다.
• 실제 우선순위를 총량·SLA·업무 중요도·개선 가능성으로 결정한다.

1. View별 집계 단위와 보존 범위

1.1 V$SQL은 SQL_ID당 한 행이 아니다

Oracle 공식 문서에서 V$SQL은 원본 SQL Text의 각 Child Cursor마다 한 행을 제공합니다.

SQL_ID는 Parent 식별값
CHILD_NUMBER는 Child 식별 번호
PLAN_HASH_VALUE는 주요 Plan 구조 비교값

따라서 SQL_ID만 GROUP BY하면 다음 정보가 섞일 수 있습니다.

• 서로 다른 Plan
• 서로 다른 Bind 선택도 범위
• 서로 다른 Optimizer Environment
• 사용량이 많은 Child와 거의 사용하지 않는 Child
• Shareable·Obsolete 상태

1.2 V$SQLAREA

V$SQLAREA는 같은 SQL String 아래 Child 통계를 합산합니다.

VERSION_COUNT
  → 현재 Parent 아래 Cache에 존재하는 Child 수

EXECUTIONS·BUFFER_GETS·ELAPSED_TIME
  → 모든 Child의 합계

Parent 전체 크기를 빠르게 볼 수 있지만 어떤 Child가 부하를 만들었는지는 V$SQL로 내려가야 합니다.

1.3 V$SQLSTATS

V$SQLSTATS는 V$SQL·V$SQLAREA보다 빠르고 확장성이 높으며, Cursor가 Shared Pool에서 Age Out된 뒤에도 통계가 남을 수 있습니다.

V$SQLSTATS
  → Top SQL 후보를 가볍게 탐색
  → SQL_ID·Plan 중심의 간결한 Column

V$SQL
  → Child별 원인과 실제 Cursor 상태 확인

V$SQLSTATS의 DELTA_* Column은 마지막 AWR Snapshot 이후 증가량을 제공할 수 있습니다. 사용 환경의 Diagnostic Pack 허용 범위와 Monitoring 정책을 확인합니다.

1.4 V$SQLAREA_PLAN_HASH

같은 SQL_ID가 여러 Plan을 사용했다면 SQL_ID 전체 합계보다 Plan별 총량이 유용할 수 있습니다.

SELECT sql_id,
       plan_hash_value,
       executions,
       buffer_gets,
       disk_reads,
       cpu_time,
       elapsed_time
FROM   v$sqlarea_plan_hash
WHERE  sql_id = :sql_id
ORDER BY elapsed_time DESC;

Child 공유 원인은 유지하면서 Plan별 총량을 비교하려면 V$SQL Child 분석과 함께 사용합니다.

2. V$SQL 통계의 갱신·누적 범위

V$SQL의 통계는 일반적으로 Query 실행 종료 시 갱신되며, 장시간 실행 SQL은 실행 중에도 약 5초 간격으로 갱신될 수 있습니다.

Cursor Load
  → Parse·Execute·Fetch
  → 통계 누적
  → 장시간 실행 중 주기적 게시 가능
  → Age Out·Purge·Parent 재생성
  → 기존 V$SQL 행 소멸 또는 Counter 기준 변경

누적값은 다음에 영향을 받습니다.

• Cursor가 Shared Pool에 머문 기간
• Parent·Child가 Load·Reload된 시점
• Instance·PDB 시작 시점
• 문제 이전의 실행 포함량
• Statistics·DDL·Object 변경에 따른 Invalidation
• Bind·Optimizer Environment로 생성된 새 Child

현재 10분을 분석할 때는 누적 총량이 아니라 같은 Cursor Identity의 Snapshot Delta 또는 허용된 구간 통계를 사용합니다.

3. 정확한 Snapshot Identity

기존의 SQL_ID + CHILD_NUMBER + PLAN_HASH_VALUE만으로는 Parent가 Age Out된 뒤 같은 SQL_ID가 다시 Load된 경우를 완전히 구분하기 어렵습니다.

3.1 RAC·CDB Child Snapshot Key

snapshot_time
inst_id
con_id
sql_id
child_number
child_address
plan_hash_value
heap0_load_time 또는 last_load_time
statistic values

• INST_ID: RAC Instance 구분
• CON_ID: Container 구분
• CHILD_ADDRESS: 현재 Child Cursor 주소
• HEAP0_LOAD_TIME: Library Cache Object Heap 0 Load 시각
• LAST_LOAD_TIME: Query Plan Load 시각

동일 SQL_ID·CHILD_NUMBER라도 Child 주소나 Load 시각이 바뀌면 새로운 Counter Lifetime으로 처리합니다.

3.2 Counter Reset 판단

End Counter가 Begin보다 작으면 다음을 확인합니다.

1. Instance 또는 PDB Restart
2. Parent·Child Age Out 후 재생성
3. Child Address·Load Time 변화
4. 다른 INST_ID·CON_ID 연결
5. Snapshot 누락

음수 Delta에 절댓값을 적용하지 않습니다.

4. 먼저 확인할 주요 Column

4.1 MODULE·ACTION 주의

MODULE과 ACTION은 SQL을 최초 Parse할 때 설정된 값입니다. 동일 Cursor를 여러 Module이 공유하면 현재 모든 실행의 업무 Context를 완전하게 표현하지 못할 수 있습니다.

정확한 업무 Attribution에는 다음을 함께 사용합니다.

• Service별 통계
• Session·ASH의 Module·Action
• Application Trace ID·Client Identifier
• SQL 실행 구간의 Monitoring 자료

5. Top SQL 조회 예

현재 Shared Pool에서 Child별 총량과 평균을 함께 조회합니다.

SELECT sql_id,
       child_number,
       child_address,
       plan_hash_value,
       executions,
       end_of_fetch_count,
       parse_calls,
       fetches,
       rows_processed,
       buffer_gets,
       disk_reads,
       physical_read_requests,
       physical_read_bytes,
       cpu_time,
       elapsed_time,
       ROUND(buffer_gets
             / NULLIF(executions, 0), 1) AS gets_per_exec,
       ROUND(elapsed_time
             / NULLIF(executions, 0)
             / 1000, 3) AS elapsed_ms_per_exec,
       ROUND(elapsed_time
             / NULLIF(end_of_fetch_count, 0)
             / 1000, 3) AS elapsed_ms_per_completed_exec,
       ROUND(buffer_gets
             / NULLIF(rows_processed, 0), 1) AS gets_per_row,
       last_load_time,
       last_active_time,
       service,
       module,
       action,
       is_shareable,
       is_obsolete
FROM   v$sql
WHERE  executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 20 ROWS ONLY;

NULLIF는 0으로 나누는 오류를 방지합니다.

5.1 executions > 0 Filter의 한계

실행된 SQL의 실행당 비용을 찾을 때는 유용하지만, 실행되지 않았어도 Hard Parse 비용과 Memory를 크게 소비한 Cursor는 제외됩니다.

Parse·Load 문제를 찾을 때는 다음처럼 목적에 맞는 별도 조회를 사용합니다.

• PARSE_CALLS
• LOADS
• INVALIDATIONS
• SHARABLE_MEM
• AVG_HARD_PARSE_TIME (V$SQLSTATS)

6. 총량 기준 Top SQL

총량은 문제 구간의 Database 전체 자원을 많이 소비한 SQL을 찾는 데 적합합니다.

예를 들어 SQL A가 한 번 5ms지만 100만 번 실행되면 총 Database Time은 약 5,000초입니다.

1ms 개선 × 1,000,000회
  = 1,000초 절감

시스템 전체 부하 감소가 목표라면 실행당 가벼운 고빈도 SQL도 높은 우선순위가 될 수 있습니다.

7. 실행당·완료 실행당 부하

7.1 실행당 평균

Elapsed / Execution
CPU / Execution
Buffer Gets / Execution
Disk Reads / Execution
Physical Read Requests / Execution
Rows / Execution

7.2 완료 실행당 평균

EXECUTIONS는 실행 시작을 포함하지만 SELECT가 일부 Fetch된 뒤 닫히거나 오류로 종료될 수 있습니다. END_OF_FETCH_COUNT는 끝까지 완료된 실행 수를 구분하는 데 도움을 줍니다.

Completion Ratio
  = END_OF_FETCH_COUNT / EXECUTIONS

Elapsed / Completed Execution
  = ELAPSED_TIME / END_OF_FETCH_COUNT

다만 실행 중인 Cursor와 부분 Fetch가 섞여 있으므로 Ratio 하나로 오류를 확정하지 않습니다. Client Pagination·Cancel·Timeout과 SQL 종류를 확인합니다.

7.3 평균의 한계

평균값은 빠른 Bind와 느린 Bind, 서로 다른 Plan과 Tail Latency를 숨길 수 있습니다.

• Child·Plan별 분리
• Bind 값·선택도
• P95·P99 응답시간
• Max·Histogram·ASH

을 함께 확인합니다.

8. 처리 Row당 효율

Buffer Gets / Row
Disk Reads / Row
CPU Time / Row
Elapsed Time / Row

SQL B는 결과 10행을 만들기 위해 많은 Block을 방문했을 수 있습니다. 실제 실행계획에서 다음을 찾습니다.

• 후보 행이 급증한 최초 Operation
• Table Access 반복
• 늦은 Filter·Join 제거
• Sort·Hash 입력 과다

8.1 ROWS_PROCESSED와 Fetch 범위

SELECT가 일부 Row만 Fetch되고 Cursor가 닫히면 ROWS_PROCESSED와 END_OF_FETCH_COUNT가 전체 결과 처리 실행과 다를 수 있습니다.

비교할 때 다음을 통제합니다.

• Fetch 완료 여부
• Pagination·Array Fetch Size
• Timeout·Cancel
• SELECT·DML 종류

9. 실행 빈도와 Database Call 문제

한 번은 가벼워도 지나치게 자주 실행되면 총부하와 Round Trip이 커집니다.

Executions / Business Request
Parse Calls / Executions
Fetches / Execution
Rows / Fetch
User Calls / Transaction

FETCHES는 Fetch Call 수이므로 다음 계산이 유용합니다.

Rows per Fetch
  = ROWS_PROCESSED / FETCHES

작은 Fetch Size 때문에 Round Trip이 많을 수 있지만, 부분 Fetch와 DML에서는 해석 범위를 확인합니다.

10. Parse·Load·Invalidation·Child 증가

10.1 Parse Calls가 Executions에 가까운 경우

PARSE_CALLS = 100,000
EXECUTIONS  = 100,000

실행마다 Parse API Call을 수행하는 Application 패턴일 수 있습니다. Soft Parse라도 비용은 남으므로 Prepared Statement·Statement Cache·Session Cursor Cache를 확인합니다.

10.2 Loads가 Parse Calls보다 작은 경우

PARSE_CALLS = 100,000
LOADS       = 10

대부분 기존 Cursor를 찾은 Parse일 수 있습니다. Hard Parse·Reload는 적지만 Parse Call 자체를 줄일 여지가 있습니다.

10.3 Version Count

VERSION_COUNT는 V$SQLAREA의 현재 Child 수입니다. 높은 값 자체를 오류로 확정하지 않습니다.

• Bind Metadata 차이
• Optimizer Environment 차이
• Object Translation·권한 차이
• Adaptive Cursor Sharing
• SQL Management Object 변화
• Statistics·DDL Invalidation

을 V$SQL_SHARED_CURSOR와 Child별 Plan·실행량으로 구분합니다.

11. 구간 Delta 계산

문제 구간의 Begin·End Snapshot을 같은 Child Lifetime으로 연결합니다.

Gets/Exec
  = 4,800,000 / 4,000
  = 1,200

Elapsed/Exec
  = 600 / 4,000
  = 0.15초

Completion Ratio
  = 3,500 / 4,000
  = 87.5%

Gets/Row
  = 4,800,000 / 40,000
  = 120

Counter Lifetime이 바뀌면 해당 Delta를 폐기하거나 새 Load 시점부터 별도 구간으로 계산합니다.

12. RAC·CDB Top SQL

RAC에서는 GV$SQL을 사용하고 Instance별 Delta를 계산한 뒤 필요에 따라 합산합니다.

Key
  = INST_ID
  + CON_ID
  + SQL_ID
  + CHILD_ADDRESS
  + Load Time

같은 SQL_ID가 여러 Instance에서 실행됐을 때 다음을 따로 봅니다.

• Instance별 부하 편중
• Plan Hash 차이
• Service 배치
• Cluster Wait Time
• Instance Affinity·Load Balancing

CDB에서는 PDB별 CON_ID와 CON_DBID를 기록해 다른 Container의 SQL 통계를 섞지 않습니다.

13. Top SQL 선정 사례

시스템 총부하 목표

• A의 Total Elapsed가 가장 큼
• 호출 수 감소 또는 1회당 1ms 개선도 큰 효과

단건 SLA 목표

• B가 실행당 15초
• 낮은 실행 횟수 때문에 총량 Ranking에서 놓칠 수 있음

핵심 업무 목표

• 주문 저장 C가 매출 핵심이고 P95를 위반하면 업무 중요도를 반영해 우선할 수 있음

최종 Priority
  = 총량
  + 실행당·Tail 지연
  + 실행 빈도
  + 업무 중요도
  + 개선 가능성
  + Regression Risk

14. 자주 혼동하는 판단

15. 실전 선정 순서

1. 문제 시간·Service·Module·SLA 목표 확정
2. V$SQLSTATS·Monitoring으로 후보 SQL 탐색
3. GV$SQL의 INST_ID·CON_ID·Child Identity 확인
4. 같은 Cursor Lifetime의 Delta 계산
5. Total Elapsed·CPU·Gets·Reads·Bytes Ranking
6. Per Exec·Per Completed Exec·Per Row 계산
7. Executions·Fetches·Parse Calls로 Call 구조 확인
8. Version Count·Shared Cursor 이유 확인
9. 실제 Child Plan·Bind·Wait와 연결
10. 업무 중요도·개선 가능성·Regression Risk 반영
11. 변경 후 동일 구간·업무량으로 재측정

Top SQL 선정의 핵심은 단순 Ranking이 아닙니다. 시스템 전체를 많이 소비하는 SQL, 한 번의 사용자 요청을 오래 지연시키는 SQL, 완료되지 못하는 실행이 많은 SQL, 반복 Call과 Cursor 공유 문제를 만드는 SQL을 서로 다른 지표로 구분하는 것입니다.