Parent·Child Cursor 이해와 진단: SQL_ID·VERSION_COUNT·V$SQL_SHARED_CURSOR

핵심 요약

Oracle은 동일한 SQL Text의 공통 정보를 Parent Cursor로 관리하고, 실제 실행에 필요한 실행계획과 실행 호환 조건을 하나 이상의 Child Cursor로 관리합니다.

Parent Cursor
  → SQL Text와 SQL_ID를 중심으로 한 공통 식별 정보
      ├─ Child Cursor 0: Plan A·Object A·Bind Metadata A·Optimizer Environment A
      ├─ Child Cursor 1: Plan B·Object A·Bind Metadata B·Optimizer Environment A
      └─ Child Cursor 2: Plan C·Object B·Bind Metadata A·Optimizer Environment B

핵심 관계는 다음과 같습니다.

• SQL_ID는 Library Cache에 있는 Parent Cursor를 식별하는 대표 값입니다.
• V$SQLAREA는 Parent Cursor 아래 Child Cursor들의 집계 정보를 보여 줍니다.
• V$SQL은 현재 Library Cache에 있는 Child Cursor별 실행 정보와 Plan 식별값을 보여 줍니다.
• V$SQL_PLAN은 특정 Child Cursor의 실행계획 Operation을 보여 줍니다.
• VERSION_COUNT는 해당 Parent 아래 현재 Cache에 존재하는 Child Cursor 수입니다.
• CHILD_NUMBER는 Child Cursor의 식별 번호이며, 현재 Child 수와 같은 의미가 아닙니다.
• V$SQL_SHARED_CURSOR는 기존 Child Cursor를 공유하지 못한 이유를 항목별로 보여 줍니다.
• Oracle AI Database 26ai부터는 V$SQL_SHARED_CURSOR_DIAG에서 공유 실패 기준과 진단 정보를 JSON 형식으로 추가 확인할 수 있습니다.

Child Cursor가 여러 개라는 사실만으로 성능 문제를 확정하면 안 됩니다. Adaptive Cursor Sharing처럼 여러 계획이 필요한 정상적인 경우도 있고, Bind Metadata 불일치나 불필요한 Session 환경 차이처럼 개선해야 할 경우도 있습니다.

VERSION_COUNT 진단의 핵심
  → Child 수가 몇 개인가?
  → 왜 분리되었는가?
  → 어떤 Child가 실제로 사용되는가?
  → CPU·메모리·Parse 비용과 SQL 응답시간에 영향을 주는가?

이 이론의 범위

이 이론은 SQLP의 SQL 옵티마이저 → SQL 공유 및 재사용 범위에서 Parent·Child Cursor와 Dynamic Performance View를 이용한 진단 원리를 다룹니다. Bind Peeking·Adaptive Cursor Sharing의 상세 동작, Cursor Heap 내부 구조, 공유 실패 Column 전체 목록은 후속 이론에서 다룹니다.

학습 목표

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

• Parent Cursor와 Child Cursor의 역할을 구분한다.
• SQL Text 차이와 실행 환경 차이가 Parent·Child 생성에 미치는 영향을 구분한다.
• SQL_ID, CHILD_NUMBER, VERSION_COUNT의 차이를 설명한다.
• V$SQLAREA, V$SQL, V$SQLAREA_PLAN_HASH, V$SQL_PLAN의 관찰 단위를 구분한다.
• PLAN_HASH_VALUE와 FULL_PLAN_HASH_VALUE를 사용할 때의 주의점을 설명한다.
• V$SQL_SHARED_CURSOR의 대표 공유 실패 원인을 분류한다.
• V$SQL_SHARED_CURSOR_DIAG의 목적과 버전 조건을 설명한다.
• Bind Metadata, 실제 객체, 권한, Optimizer 환경, 재최적화와 Invalidation에 의한 Child 분리를 설명한다.
• VERSION_COUNT를 장애 수치가 아닌 진단 출발점으로 해석한다.
• Child Cursor 증가 원인을 단계적으로 진단하고 개선 결과를 측정한다.
• RAC·Multitenant 환경에서 INST_ID와 CON_ID를 함께 확인해야 하는 이유를 설명한다.

1. Parent Cursor와 Child Cursor를 나누는 이유

하나의 SQL Text는 서로 다른 객체·Bind Metadata·Optimizer 환경에서 실행될 수 있습니다.

SELECT *
FROM employees
WHERE department_id = :deptno;

같은 SQL Text라도 다음 조건이 달라질 수 있습니다.

• 어느 Schema 또는 PDB의 EMPLOYEES 객체를 참조하는가?
• Bind 변수 :deptno를 NUMBER로 전달하는가, VARCHAR2로 전달하는가?
• Bind의 최대 길이와 Character Set이 호환되는가?
• Session의 Optimizer Mode와 관련 환경이 같은가?
• 권한과 Parsing Schema가 호환되는가?
• Bind 선택도 차이로 다른 실행계획이 필요한가?
• Statistics Feedback이나 SQL Management Object가 새로 적용되었는가?

Oracle은 SQL Text의 공통 정보를 Parent Cursor에 두고, 실제 실행 호환 조건과 실행계획을 Child Cursor에 나누어 관리합니다.

이 구조는 다음 두 요구를 함께 만족합니다.

1. 같은 SQL Text의 공통 정보는 공유한다.
2. 실행 조건이 호환되지 않으면 안전하게 별도 Child Cursor를 사용한다.

2. Parent Cursor의 역할

Parent Cursor는 SQL Text와 공통 식별 정보를 관리합니다. Oracle은 SQL Text를 기반으로 SQL_ID를 계산하고 Library Cache에서 Parent Cursor 후보를 찾습니다.

2.1 SQL Text가 다르면 별도 Parent가 될 수 있다

다음 문장은 결과가 같을 수 있지만 Text가 다릅니다.

SELECT * FROM employees;
select * from employees;
SELECT  * FROM employees;
SELECT /* screen A */ * FROM employees;

대소문자, 공백, 줄바꿈, 주석, Literal 값의 차이는 별도 SQL Text와 SQL_ID를 만들 수 있습니다.

Text A → SQL_ID A → Parent A
Text B → SQL_ID B → Parent B

따라서 Application이나 Framework가 같은 업무 SQL을 여러 문자열 형태로 생성하면 Parent Cursor가 불필요하게 증가할 수 있습니다.

2.2 V$SQLAREA에서 Parent 단위 집계 확인

SELECT sql_id,
       sql_text,
       version_count,
       loaded_versions,
       open_versions,
       parse_calls,
       executions,
       loads,
       invalidations,
       sharable_mem
FROM v$sqlarea
WHERE sql_id = :sql_id;

V$SQLAREA는 Parent Cursor 아래 Child Cursor들의 실행·메모리 통계를 합산한 형태로 보여 줍니다.

3. Child Cursor의 역할

Child Cursor는 SQL을 실제로 실행하기 위한 구체적인 정보를 가집니다.

대표적으로 다음 정보가 연결됩니다.

• 실행계획
• Bind 변수 Metadata
• 참조 객체와 Object Translation 정보
• Parsing Schema와 권한 관련 정보
• Optimizer 환경
• SQL Profile·Patch·Plan Baseline 등 SQL Management Object 적용 상태
• Bind-Sensitive·Bind-Aware 상태
• Child별 실행·자원 사용 통계
• 공유 실패와 재최적화 관련 상태

같은 Parent Cursor 아래 여러 Child가 존재할 수 있습니다.

Parent: SELECT * FROM employees WHERE department_id = :deptno
  ├─ Child 0: Plan A, NUMBER Bind, Object USER_A.EMPLOYEES
  ├─ Child 1: Plan A, VARCHAR2 Bind, Object USER_A.EMPLOYEES
  └─ Child 2: Plan B, NUMBER Bind, Object USER_B.EMPLOYEES

3.1 같은 SQL_ID가 같은 Plan을 뜻하지 않는 이유

SQL_ID는 Parent Cursor를 식별합니다. 실행계획은 Child Cursor에 연결되므로 같은 SQL_ID 아래 서로 다른 CHILD_NUMBER와 PLAN_HASH_VALUE가 존재할 수 있습니다.

SELECT sql_id,
       child_number,
       plan_hash_value,
       full_plan_hash_value,
       executions,
       parse_calls,
       loads,
       invalidations,
       is_bind_sensitive,
       is_bind_aware,
       is_shareable,
       is_obsolete,
       last_active_time
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

V$SQL은 현재 Library Cache에 존재하는 Child Cursor별 정보를 비교할 때 사용합니다.

3.2 CHILD_NUMBER와 VERSION_COUNT는 다르다

VERSION_COUNT
  → 현재 Cache에 존재하는 Child Cursor의 개수

CHILD_NUMBER
  → Parent 안에서 Child를 식별하는 번호

Child가 Aging Out·Purged·Obsolete 처리되면 번호에 빈 구간이 생길 수 있습니다.

현재 Child Number: 0, 2, 5
VERSION_COUNT: 3
최대 CHILD_NUMBER + 1: 6

따라서 MAX(CHILD_NUMBER)+1을 현재 Child 수로 사용하면 안 됩니다.

4. Parent는 같고 Child가 달라지는 대표 사례

4.1 같은 SQL Text가 다른 실제 객체를 참조하는 경우

사용자 A와 B가 각각 자신의 Schema에 EMPLOYEES 테이블을 가지고 있다고 가정합니다.

SELECT * FROM employees;

Text는 같아 같은 Parent Cursor 후보가 될 수 있지만 실제 Base Object가 다르므로 같은 Child Cursor를 안전하게 공유할 수 없습니다.

Parent Cursor: SELECT * FROM employees
  ├─ Child 0 → USER_A.EMPLOYEES
  └─ Child 1 → USER_B.EMPLOYEES

이 경우 TRANSLATION_MISMATCH 등 Object Translation 관련 공유 실패 항목을 확인할 수 있습니다.

4.2 Bind Metadata가 다른 경우

Application의 두 코드 경로가 같은 Bind 위치에 서로 다른 데이터 타입이나 길이 특성을 전달할 수 있습니다.

실행 1: :customer_id를 NUMBER로 Bind
실행 2: :customer_id를 VARCHAR2로 Bind

이 차이는 BIND_MISMATCH 또는 Bind 길이 관련 공유 실패 원인으로 나타날 수 있습니다.

컬럼과 Bind 타입이 일치하지 않으면 암묵적 형변환, 변환 오류, 선택도 추정 차이가 발생할 수 있습니다. Access Path 영향 여부는 최종 Predicate와 실행계획에서 확인해야 합니다.

4.3 Optimizer 환경이 다른 경우

다음 Session 환경 차이는 별도 Child Cursor가 필요한 원인이 될 수 있습니다.

• OPTIMIZER_MODE
• 일부 Optimizer Parameter
• Parallel DML·Parallel Query 환경
• NLS와 언어 환경
• Outline·SQL Patch·SQL Profile·Plan Baseline 적용 상태
• PL/SQL Compiler 설정

OPTIMIZER_MISMATCH, OPTIMIZER_MODE_MISMATCH, PX_MISMATCH, STB_OBJECT_MISMATCH 등의 항목을 원인에 맞게 확인합니다.

4.4 권한과 Object Translation이 다른 경우

다음 항목은 서로 구분해야 합니다.

4.5 Bind 값별 다른 계획이 필요한 경우

Data Skew가 큰 Bind Predicate에서는 Adaptive Cursor Sharing이 선택도 범위에 따라 여러 Child Plan을 관리할 수 있습니다.

관련 항목 예시는 다음과 같습니다.

• IS_BIND_SENSITIVE
• IS_BIND_AWARE
• BIND_EQUIV_FAILURE
• USER_BIND_PEEK_MISMATCH

이 경우 Child 증가가 실행계획 품질을 위한 정상 동작일 수 있으므로 Bind 범위와 각 Child의 실제 사용량을 확인합니다.

4.6 재최적화·Plan 관리 정보가 달라진 경우

다음 변화도 새 Child 또는 Hard Parse의 원인이 될 수 있습니다.

• Statistics Feedback을 이용한 재최적화: USE_FEEDBACK_STATS
• SQL Plan Baseline·SQL Profile·SQL Patch 생성 또는 변경: STB_OBJECT_MISMATCH
• Rolling Invalidation Window 경과: ROLL_INVALID_MISMATCH
• Materialized View Rewrite 상태 차이
• Object DDL·Compile과 Cursor Invalidation

Invalidation이 발생했다고 항상 Child 수가 증가하는 것은 아닙니다. 기존 Child가 Invalid 상태에서 Reload·Reoptimization될 수도 있으므로 VERSION_COUNT뿐 아니라 LOADS, INVALIDATIONS, Child 생성 시각과 Plan 변화를 함께 봅니다.

5. 진단 View를 역할별로 구분하기

5.1 기본 진단 SQL

-- 1. Parent 상태
SELECT sql_id,
       version_count,
       loaded_versions,
       open_versions,
       parse_calls,
       executions,
       loads,
       invalidations,
       sharable_mem
FROM v$sqlarea
WHERE sql_id = :sql_id;

-- 2. Child별 상태
SELECT sql_id,
       child_number,
       plan_hash_value,
       executions,
       parse_calls,
       buffer_gets,
       cpu_time,
       elapsed_time,
       loads,
       invalidations,
       is_bind_sensitive,
       is_bind_aware,
       is_shareable,
       is_obsolete,
       last_active_time
FROM v$sql
WHERE sql_id = :sql_id
ORDER BY child_number;

-- 3. Plan별 집계
SELECT sql_id,
       plan_hash_value,
       version_count,
       executions,
       buffer_gets,
       cpu_time,
       elapsed_time
FROM v$sqlarea_plan_hash
WHERE sql_id = :sql_id
ORDER BY executions DESC;

-- 4. 특정 Child의 Plan Operation
SELECT child_number,
       id,
       parent_id,
       operation,
       options,
       object_owner,
       object_name,
       cost,
       cardinality,
       access_predicates,
       filter_predicates
FROM v$sql_plan
WHERE sql_id = :sql_id
  AND child_number = :child_number
ORDER BY id;

-- 5. 공유 실패 사유
SELECT *
FROM v$sql_shared_cursor
WHERE sql_id = :sql_id
ORDER BY child_number;

Oracle AI Database 26ai에서는 추가로 다음 View를 사용할 수 있습니다.

SELECT sql_id,
       child_number,
       failing_criteria,
       diag_data
FROM v$sql_shared_cursor_diag
WHERE sql_id = :sql_id
ORDER BY child_number;

V$SQL_SHARED_CURSOR_DIAG는 26ai부터 제공되므로 이전 버전에서는 사용할 수 없습니다.

5.2 RAC·Multitenant 환경

RAC에서는 GV$SQLAREA, GV$SQL, GV$SQL_SHARED_CURSOR와 INST_ID를 함께 확인합니다. Multitenant 환경에서는 CON_ID를 확인해야 합니다.

SQL_ID만 확인
  → 다른 Instance·Container의 Cursor를 혼동할 수 있음

SQL_ID + INST_ID + CON_ID + CHILD_NUMBER
  → 진단 대상 Cursor를 더 정확히 식별

6. V$SQL_SHARED_CURSOR의 대표 원인 해석

6.1 LITERAL_MISMATCH 해석 주의

일반적인 값 Literal 차이는 SQL Text 자체를 바꾸므로 별도 Parent Cursor와 SQL_ID를 만드는 경우가 일반적입니다.

V$SQL_SHARED_CURSOR.LITERAL_MISMATCH는 공식 설명상 Non-Data Literal이 기존 Child와 맞지 않는 경우를 뜻합니다. 이를 “값 Literal이 달라 같은 Parent의 Child가 증가한다”는 일반 규칙으로 사용하면 안 됩니다.

일반 Data Literal 변화
  → SQL Text 변화
  → Parent Cursor 증가 가능성

V$SQL_SHARED_CURSOR의 LITERAL_MISMATCH
  → Non-Data Literal의 공유 호환성 문제

7. VERSION_COUNT와 Plan Hash 해석

7.1 VERSION_COUNT는 현재 Child 수다

VERSION_COUNT = 1
  → 현재 Cache에 Child Cursor 1개

VERSION_COUNT = 12
  → 현재 Cache에 Child Cursor 12개

Child가 Aging Out되거나 Purged되면 값은 변할 수 있습니다. 장기간의 누적 생성 횟수와 동일하지 않습니다.

7.2 높은 VERSION_COUNT가 필요한 경우

• 동일 Text가 서로 다른 실제 객체를 참조함
• Adaptive Cursor Sharing이 선택도 범위별 Plan을 관리함
• 필요한 Optimizer·Parallel 환경 차이가 있음
• SQL Management Object나 재최적화 상태에 맞는 Child가 필요함

7.3 개선 대상일 가능성이 큰 경우

• Application이 같은 Bind를 서로 다른 Type·길이로 전달함
• Connection Pool별 Session 환경이 불필요하게 다름
• 배포·DDL·Compile·통계 변경으로 Reload와 Invalidation이 반복됨
• 공유되지 않는 Child가 계속 생성되면서 Parse CPU와 Shared Memory가 증가함
• 거의 실행되지 않는 Obsolete·Unshareable Child가 과도하게 남아 있음

7.4 PLAN_HASH_VALUE 해석

PLAN_HASH_VALUE는 Plan Operation 구조를 빠르게 비교하기 위한 숫자입니다.

PLAN_HASH_VALUE가 다름
  → 일반적으로 Plan 구조가 다름

PLAN_HASH_VALUE가 같음
  → 주요 Plan 구조가 같다는 빠른 비교 기준
  → Child Cursor 자체가 같다는 뜻은 아님

같은 Plan Hash를 가진 여러 Child가 존재할 수 있습니다. Bind Metadata나 Optimizer 환경은 다르지만 최종 Plan 구조가 같을 수 있기 때문입니다.

FULL_PLAN_HASH_VALUE는 더 완전한 Plan 표현을 비교할 때 사용할 수 있지만 Oracle Release 사이에는 직접 비교할 수 없습니다.

8. Literal 폭증과 Child 폭증을 구분한다

값만 다른 Literal SQL은 대개 한 Parent의 VERSION_COUNT가 증가하는 문제가 아니라 서로 다른 Parent와 SQL_ID가 증가하는 문제입니다.

Literal 폭증
  → SQL Text 변화
  → Parent Cursor·SQL_ID 증가

Bind Metadata·Object·환경 불일치
  → 같은 Parent 아래 Child Cursor 증가

먼저 다음 두 질문을 구분해야 합니다.

유사 SQL Text가 여러 SQL_ID로 나뉘었는가?
  → SQL Text 생성과 Bind 적용 문제

한 SQL_ID의 VERSION_COUNT가 증가했는가?
  → Child 공유 조건 문제

9. Child Cursor 진단 절차

1단계: SQL Text와 진단 범위 확정

• SQL Text와 SQL_ID를 확인합니다.
• 유사 SQL이 Literal·대소문자·공백·주석 차이로 여러 Parent에 나뉘었는지 확인합니다.
• RAC·CDB 환경이면 INST_ID와 CON_ID를 함께 확정합니다.

2단계: Parent 집계 상태 확인

V$SQLAREA에서 다음 항목을 확인합니다.

• VERSION_COUNT
• LOADED_VERSIONS
• OPEN_VERSIONS
• PARSE_CALLS
• EXECUTIONS
• LOADS
• INVALIDATIONS
• SHARABLE_MEM

VERSION_COUNT가 높지만 Loaded·Open Child가 적고 실제 실행이 일부 Child에 집중될 수 있으므로 개수를 단독으로 판단하지 않습니다.

3단계: Child별 사용량과 상태 비교

V$SQL에서 다음 항목을 비교합니다.

• CHILD_NUMBER
• PLAN_HASH_VALUE
• EXECUTIONS
• BUFFER_GETS
• CPU_TIME
• ELAPSED_TIME
• LAST_ACTIVE_TIME
• IS_SHAREABLE
• IS_OBSOLETE
• IS_BIND_SENSITIVE
• IS_BIND_AWARE

실제 부하를 만드는 Child와 거의 사용되지 않는 Child를 구분합니다.

4단계: Plan 차이 확인

• V$SQLAREA_PLAN_HASH에서 Plan별 집계 사용량을 비교합니다.
• V$SQL_PLAN 또는 DBMS_XPLAN.DISPLAY_CURSOR로 특정 Child의 Plan을 확인합니다.
• 같은 Plan Hash를 가진 Child도 Metadata·환경은 다를 수 있음을 고려합니다.

5단계: 공유 실패 이유 확인

V$SQL_SHARED_CURSOR에서 Y인 항목을 확인합니다.

• Bind Metadata
• Object Translation
• 권한
• Optimizer·Parallel 환경
• Reoptimization·Feedback
• SQL Management Object
• Rolling Invalidation

26ai에서는 V$SQL_SHARED_CURSOR_DIAG의 JSON 진단 정보를 추가로 확인합니다.

6단계: Application과 변경 이력 대조

• Application 배포 후 Bind Type·Length가 바뀌었는가?
• Connection Pool마다 Session Parameter가 다른가?
• 동일 이름 Object가 다른 Schema·PDB에 존재하는가?
• 업무 시간에 DDL·Package Compile이 있었는가?
• 통계 수집·Index 생성·SQL Patch·Profile·Baseline 변경이 있었는가?

7단계: 원인 수정 후 증가율 재측정

• 새로운 Child 생성 속도
• VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS
• Hard Parse와 Parse CPU
• Shared Pool Memory
• Child·Plan별 Buffer Gets와 응답시간
• IS_OBSOLETE, IS_SHAREABLE 상태
• SQL 결과 정합성

Shared Pool Flush는 진단 정보를 제거하고 이후 Hard Parse를 증가시킬 수 있으므로 근본 해결책으로 사용하지 않습니다.

10. 사례: Bind Type 불일치

CUSTOMER_ID가 NUMBER Column이라고 가정합니다.

SELECT order_id,
       order_date
FROM orders
WHERE customer_id = :customer_id;

Application의 두 코드 경로가 다음처럼 Bind합니다.

코드 경로 A → NUMBER 205
코드 경로 B → VARCHAR2 '205'

가능한 영향은 다음과 같습니다.

Bind Metadata 불일치
  → 기존 Child Cursor 공유 실패
  → BIND_MISMATCH 또는 길이 관련 공유 실패
  → 별도 Child Cursor 생성
  → VERSION_COUNT 증가

Column·Bind Type 불일치
  → 암묵적 형변환 또는 변환 오류 가능
  → 선택도 추정과 Predicate 표현 차이 가능
  → 실제 Access Path는 실행계획에서 확인

개선은 SQL Text만 맞추는 데서 끝나지 않습니다. Application의 Bind 데이터 타입·길이·Character Set을 Database Column과 일관되게 전달해야 합니다.

Bind 상태를 확인할 때 V$SQL_BIND_CAPTURE를 참고할 수 있지만 Bind 값은 항상 Capture되는 것이 아니며 Capture 시점도 제한적입니다. WAS_CAPTURED와 LAST_CAPTURED를 함께 확인합니다.

11. 자주 혼동하는 판단과 정확한 기준

12. 핵심 정리

Parent Cursor
  → SQL Text와 SQL_ID 중심의 공통 정보

Child Cursor
  → 실행계획·Bind Metadata·참조 객체·권한·Optimizer 환경·실행 통계

• V$SQLAREA는 Parent 아래 Child들의 집계 상태를 확인합니다.
• V$SQL은 Child별 Plan, 사용량과 상태를 확인합니다.
• V$SQLAREA_PLAN_HASH는 한 SQL_ID의 Plan별 집계 비교에 사용합니다.
• V$SQL_PLAN은 특정 Child의 실행계획 Operation을 보여 줍니다.
• V$SQL_SHARED_CURSOR는 Child 공유 실패 이유를 항목별로 보여 줍니다.
• V$SQL_SHARED_CURSOR_DIAG는 26ai에서 추가 JSON 진단 정보를 제공합니다.
• VERSION_COUNT는 현재 Child 수이며 CHILD_NUMBER의 최댓값과 다를 수 있습니다.
• Literal 폭증은 Parent 증가, Bind·Object·환경 불일치는 Child 증가로 나타날 수 있습니다.
• 같은 SQL_ID 아래 다른 Plan Hash가 존재할 수 있고, 같은 Plan Hash를 가진 여러 Child도 존재할 수 있습니다.
• 최종 판단은 Child 수보다 생성 원인, 실제 사용량, Memory·Parse 비용과 SQL 성능을 기준으로 합니다.