Shared Pool과 SQL Parsing: Library Cache·Soft Parse·Hard Parse

핵심 요약

SQL Parsing은 SQL 문법만 검사하는 작업이 아닙니다. Oracle은 SQL의 문법과 의미를 확인하고, Shared Pool에서 현재 실행에 재사용할 수 있는 Cursor가 있는지 판단합니다.

Application이 열린 Cursor·Statement Handle을 재사용
  → 새로운 Parse 요청 없이 Execute 가능

Application이 Parse를 요청
  → Session Cursor Cache에서 재사용 가능한 Cursor Handle 확인 가능
      ├─ Hit: 실제 재파싱을 줄이고 기존 Cursor 재사용
      └─ Miss: SQL Parsing과 Shared Pool Check 수행
                  ├─ 호환 Cursor 존재: Soft Parse
                  └─ 호환 Cursor 없음: Hard Parse
                                          → Optimization
                                          → Row Source Generation
                                          → Shared SQL Area 등록

이 이론에서 반드시 구분할 항목은 다음과 같습니다.

1. Parse Call은 Application이 Oracle에 Parse를 요청한 횟수입니다.
2. Soft Parse는 Library Cache에서 재사용 가능한 실행 구조를 찾아 사용하는 경우입니다.
3. Hard Parse는 재사용 가능한 실행 구조가 없어 새로운 실행 가능한 형태를 만드는 경우입니다.
4. Execute without Parse는 Application이 이미 열린 Cursor를 재사용해 새 Parse 요청 없이 실행하는 경우입니다.
5. Session Cursor Cache Hit는 Session에 보관된 Cursor 정보를 이용해 실제 재파싱 작업을 줄이는 경우입니다.

가장 좋은 방향
  → Parse Once, Execute Many

그다음 방향
  → Soft Parse와 Session Cursor Cache 활용

피해야 할 방향
  → 실행할 때마다 Hard Parse

Soft Parse도 Syntax·권한 확인과 공유 Cursor 호환성 확인 등에 자원을 사용하므로 비용이 0은 아닙니다. Hard Parse는 여기에 Optimizer 탐색, Row Source Generation, Shared Pool 메모리 작업과 공유 구조 동시성 보호까지 추가되어 더 비쌉니다.

이 이론의 범위

이 이론은 SQLP의 SQL 옵티마이저 → SQL 공유 및 재사용 범위에서 Shared Pool과 SQL Parsing의 기본 원리를 다룹니다. Parent·Child Cursor의 세부 구조와 V$SQL_SHARED_CURSOR, Bind Peeking·Adaptive Cursor Sharing은 후속 이론에서 상세히 다룹니다.

학습 목표

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

• Parse Call과 실제 Parse 작업을 구분한다.
• Execute without Parse, Session Cursor Cache Hit, Soft Parse, Hard Parse를 구분한다.
• Syntax Check, Semantic Check, Shared Pool Check의 역할을 설명한다.
• Shared Pool, Library Cache, Data Dictionary Cache의 관계를 설명한다.
• Shared SQL Area와 Private SQL Area의 역할과 저장 위치를 구분한다.
• SQL Text가 같아도 Child Cursor를 공유하지 못할 수 있는 이유를 설명한다.
• Bind Variable이 Parent Cursor 공유에 주는 효과와 한계를 설명한다.
• Hard Parse가 CPU와 동시성 측면에서 비싼 이유를 설명한다.
• Library Cache Lock·Pin과 Cursor Mutex, Transaction Row Lock을 구분한다.
• DDL·통계 수집과 Cursor Invalidation의 관계를 설명한다.
• PARSE_CALLS, EXECUTIONS, VERSION_COUNT, LOADS, INVALIDATIONS를 올바르게 해석한다.
• System·Session Parse 통계와 Session Cursor Cache 통계를 이용해 원인을 구분한다.

1. Parse Call과 SQL 실행은 항상 1:1이 아니다

다음 질문부터 구분해야 합니다.

SQL을 실행할 때마다 반드시 Parse Call이 발생하는가?

Application이 이미 Parse된 Cursor 또는 Prepared Statement를 유지하고 재사용하면, 새로운 Parse 요청 없이 Bind 값만 변경해 여러 번 Execute할 수 있습니다.

좋은 Application 흐름

Parse
  → Bind
  → Execute
  → Fetch
  → Bind 값 변경
  → Execute
  → Fetch
  → 반복

이 경우 실행 횟수는 증가하지만 Parse Call은 매번 증가하지 않을 수 있습니다.

반대로 Application이 실행할 때마다 Statement를 새로 준비하면 다음 구조가 됩니다.

Parse → Execute
Parse → Execute
Parse → Execute

기존 실행계획을 매번 재사용하더라도 Soft Parse가 반복되므로 CPU와 Shared Pool 동시성 비용이 남습니다.

1.1 Parse Call과 실제 재파싱

Oracle 통계의 parse count (total)에는 Session Cursor Cache Hit도 포함될 수 있습니다. Oracle 공식 설명에서는 다음처럼 해석하도록 안내합니다.

실제 Parse 작업의 근사치
  = parse count (total)
  - session cursor cache hits

Session Cursor Cache Hit는 SQL이 다시 요청되었지만 Session이 보관한 Cursor 정보를 찾아 실제 재파싱을 줄인 경우입니다.

따라서 다음 항목을 구분합니다.

Session Cursor Cache의 세부 동작과 Application Statement Cache는 Client·Driver에 따라 다를 수 있습니다. 이 이론에서는 Parse 비용을 줄이는 계층으로 이해합니다.

2. SQL Parsing의 세 가지 확인 단계

Oracle의 SQL 처리 문서에서는 Parse 단계의 대표 작업을 다음과 같이 설명합니다.

Syntax Check
  → Semantic Check
  → Shared Pool Check

2.1 Syntax Check: SQL 문법 확인

Syntax Check는 SQL 문장이 Oracle 문법 규칙에 맞는지 확인합니다.

-- SAL 앞에 쉼표가 빠진 문장
SELECT empno,
       ename
       sal
FROM emp;

문법 오류가 있으면 이후 최적화와 실행 단계로 진행할 수 없습니다.

2.2 Semantic Check: 객체와 의미 확인

문법이 맞아도 실제로 실행 가능한지 확인해야 합니다.

주요 확인 대상은 다음과 같습니다.

• 테이블·뷰·컬럼 등의 객체가 존재하는가?
• 이름이 현재 Parsing Schema에서 어떤 객체로 해석되는가?
• 사용자가 객체에 접근할 권한이 있는가?
• 컬럼과 표현식의 데이터 타입이 유효한가?
• 함수와 연산자의 인자가 의미상 올바른가?

SELECT employee_name
FROM emp;

EMP 테이블에 EMPLOYEE_NAME 컬럼이 없다면 문법 형태는 맞아도 Semantic Check를 통과하지 못합니다.

2.3 Shared Pool Check: 실행 구조 재사용 가능 여부

Oracle은 SQL Text를 Hashing하여 후보를 찾고, Library Cache에서 재사용할 수 있는 Shared SQL Area와 Child Cursor가 있는지 확인합니다.

SQL Text의 Hash 값으로 Parent Cursor 후보 탐색
  → SQL Text가 동일한지 확인
  → 실제 참조 객체·Bind Metadata·Optimizer 환경이 호환되는 Child Cursor 탐색
      ├─ 발견: Soft Parse
      └─ 미발견: Hard Parse

동일한 SQL Text를 찾았다고 재사용이 확정되는 것은 아닙니다. 같은 Parent Cursor 아래에서도 실행 환경 차이에 따라 여러 Child Cursor가 생성될 수 있습니다.

3. SQL 공유 조건

Oracle은 SQL을 공유할 때 SQL Text와 실행 환경의 호환성을 확인합니다.

3.1 SQL Text의 일치

일반적으로 SQL Text는 문자 단위로 일치해야 합니다.

다음 차이도 별도 SQL Text가 될 수 있습니다.

• 대문자와 소문자
• 공백과 줄바꿈
• 주석
• Literal 값
• Object 이름 표기

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

Application이나 Framework가 SQL의 대소문자·공백·주석을 매번 다르게 생성하면 Parent Cursor가 불필요하게 증가할 수 있습니다.

3.2 실제 참조 객체의 일치

SQL Text가 같아도 Parsing Schema에 따라 다른 객체를 참조하면 동일 실행 구조를 공유할 수 없습니다.

SELECT * FROM employees;

두 사용자가 각각 자신의 Schema에 있는 EMPLOYEES를 참조한다면 SQL Text는 같아도 실제 객체가 다릅니다.

3.3 Bind Metadata의 일치

Bind Variable은 이름, 데이터 타입과 최대 길이 등 Metadata가 기존 Child Cursor와 호환되어야 합니다.

같은 SQL Text
  + Bind Type·Length 호환
  + 같은 참조 객체
  + 호환 Optimizer 환경
  → 기존 Child Cursor 공유 가능

3.4 Optimizer 환경의 호환

다음과 같은 환경 차이는 다른 Child Cursor가 필요한 원인이 될 수 있습니다.

• Optimizer Mode
• 일부 Optimizer Parameter
• NLS 환경
• Parallel 관련 설정
• 권한과 Parsing Schema
• Bind Metadata

Parent·Child Cursor와 공유 실패 원인은 후속 이론에서 V$SQL_SHARED_CURSOR를 이용해 상세히 다룹니다.

4. Shared Pool 구조

Shared Pool은 SGA의 일부이며 여러 Session이 함께 사용할 SQL 실행 정보와 Database Metadata를 저장합니다.

SGA
└─ Shared Pool
   ├─ Library Cache
   ├─ Data Dictionary Cache(Row Cache)
   └─ 그 밖의 Shared Pool 구성 영역

4.1 Library Cache

Library Cache는 재사용 가능한 실행 객체와 제어 구조를 저장합니다.

대표적으로 다음 정보가 포함됩니다.

• Shared SQL Area
• SQL Parse Tree와 실행계획
• PL/SQL Program Unit의 실행 가능한 형태
• Java Class의 실행 가능한 형태
• 객체 의존 관계
• Library Cache Handle과 Lock 등 제어 구조

Library Cache의 목적은 이미 분석하고 최적화한 SQL·Program 구조를 여러 Session이 공유하도록 하는 것입니다.

4.2 Data Dictionary Cache

Data Dictionary Cache는 객체와 사용자에 관한 Metadata를 빠르게 확인하기 위한 영역입니다.

예시는 다음과 같습니다.

• 테이블·컬럼 정의
• Segment와 Tablespace 정보
• 사용자와 권한
• Sequence 정보
• 객체 소유자와 의존 관계

Hard Parse 중에는 객체와 권한, 통계와 의존 관계를 확인하기 위해 Library Cache와 Dictionary Cache를 여러 번 접근할 수 있습니다.

4.3 Database Buffer Cache와 구분

Library Cache는 SELECT 결과 행을 저장하는 Result Cache로 이해하면 안 됩니다.

5. Shared SQL Area와 Private SQL Area

Oracle은 SQL의 공통 실행 정보와 Session별 실행 상태를 구분합니다.

Session A의 Private SQL Area ─┐
Session B의 Private SQL Area ─┼─→ 공통 Shared SQL Area
Session C의 Private SQL Area ─┘

Dedicated Server에서는 Private SQL Area가 주로 PGA에 존재합니다. Shared Server 환경에서는 UGA 일부가 Large Pool 또는 Shared Pool에 위치할 수 있습니다.

여러 Session은 같은 실행계획을 공유할 수 있지만 각 Session의 Bind 값과 Fetch 진행 상태까지 공유하지는 않습니다.

6. Soft Parse와 Hard Parse

6.1 Soft Parse

Soft Parse는 Library Cache에 재사용 가능한 SQL 실행 구조가 존재하고 현재 실행에 사용할 수 있는 경우입니다.

Parse 요청
  → Parent Cursor 후보 확인
  → 호환 Child Cursor 확인
  → 기존 실행계획 재사용
  → Execute

Soft Parse에서는 일반적으로 다음 고비용 단계를 생략합니다.

• Cost Based Optimization
• Row Source Generation
• 새 Shared SQL Area 생성

그러나 다음 비용은 남을 수 있습니다.

• Syntax와 Security Check
• Library Cache 탐색
• Cursor 호환성 확인
• 공유 구조의 Mutex·Latch 획득
• Parse API Call과 Application 왕복

따라서 Soft Parse는 Hard Parse보다 가볍지만 불필요하게 반복되면 확장성을 떨어뜨릴 수 있습니다.

6.2 Hard Parse

Hard Parse는 다음 경우에 발생할 수 있습니다.

• SQL이 Shared Pool에 존재하지 않음
• Parent Cursor는 있으나 호환 Child Cursor가 없음
• 실행 구조가 Aging Out되어 Execute 시점에 암묵적 재파싱이 필요함
• Cursor가 Invalid 상태가 되어 재검증·재최적화가 필요함

Hard Parse
  → Syntax·Semantic Check
  → Dictionary와 Library Cache 접근
  → Optimizer 실행계획 탐색
  → Row Source Generation
  → Shared Pool 메모리 할당과 등록

Oracle 공식 문서에서는 Hard Parse가 Parse Call뿐 아니라 Execute Call에서 실행 구조가 Library Cache에서 제거된 경우에도 발생할 수 있다고 설명합니다.

6.3 비교

7. Bind Variable과 Cursor 재사용

7.1 Literal SQL

다음 SQL은 값만 다르지만 SQL Text가 서로 다릅니다.

SELECT order_id FROM orders WHERE customer_id = 101;
SELECT order_id FROM orders WHERE customer_id = 205;
SELECT order_id FROM orders WHERE customer_id = 930;

각 SQL Text가 별도 Parent Cursor가 되면 다음 비용이 증가할 수 있습니다.

• Hard Parse
• Shared Pool 메모리 사용
• Library Cache 관리 비용
• SQL 모니터링과 관리 복잡성

7.2 Bind SQL

SELECT order_id
FROM orders
WHERE customer_id = :customer_id;

Bind 값이 달라도 SQL Text를 일정하게 유지하므로 같은 Parent Cursor를 공유할 가능성이 높아집니다.

하지만 Bind Variable만 사용한다고 Parse Call 자체가 자동으로 사라지는 것은 아닙니다.

Bind Variable
  → SQL Text 공유 가능성 향상

Prepared Statement·Cursor 재사용
  → Parse Call 자체 감소

따라서 Application은 다음 두 가지를 함께 적용해야 합니다.

1. 값이 달라지는 부분에 Bind Variable 사용
2. Parse한 Statement를 유지하고 여러 번 Execute

데이터 편중으로 Bind 값별 최적 계획이 달라지는 문제는 Adaptive Cursor Sharing 이론에서 다룹니다.

8. Hard Parse가 비싼 이유

8.1 Optimizer CPU

Optimizer는 Access Path, Join Order, Join Method, Query Transformation 등의 후보를 비교합니다. SQL이 복잡할수록 탐색 비용도 증가할 수 있습니다.

8.2 Dictionary와 Library Cache 접근

객체 정의, 권한, 통계, 의존 관계를 확인하기 위해 공유 Metadata를 반복 접근합니다.

8.3 Shared Pool 메모리 작업

새 Parent·Child Cursor와 실행 구조에 필요한 Shared Pool 메모리를 할당하고 Hash Bucket과 관리 구조에 등록합니다.

8.4 공유 구조 동시성 보호

여러 Session이 Shared Pool 객체를 동시에 탐색하고 변경하므로 내부 직렬화 장치가 필요합니다.

현대 Oracle에서는 다음 항목을 구분해 이해합니다.

Hard Parse가 폭증
  → Optimizer CPU 증가
  → Library Cache·Shared Pool 동시 접근 증가
  → Mutex·Latch·Lock·Pin 경합 가능
  → 다른 Session의 Parse와 Execute 지연
  → 전체 처리량 감소

Library Cache Lock·Pin은 Transaction Row Lock과 목적이 다릅니다.

9. Cursor Invalidation과 Reload

Cursor는 참조 객체와 환경에 의존합니다. 의존 정보가 변경되면 기존 Cursor가 Invalid 또는 재검증 대상이 될 수 있습니다.

9.1 대표 원인

• 테이블·인덱스·뷰 DDL
• 객체 Drop·Recreate
• PL/SQL Package·Procedure 재컴파일
• 권한과 Synonym 변경
• 일부 Optimizer 환경 변경
• 통계정보 수집과 재최적화 조건
• Shared Pool Aging으로 실행 구조가 메모리에서 제거됨

9.2 통계 수집과 Rolling Invalidation

통계 수집이 항상 모든 관련 Cursor를 즉시 한 번에 무효화하는 것은 아닙니다.

Oracle의 DBMS_STATS 기본 설정에서는 NO_INVALIDATE=AUTO가 사용될 수 있으며, 많은 Cursor가 동시에 Hard Parse되는 부하를 줄이기 위해 Rolling Invalidation이 적용될 수 있습니다.

통계 갱신
  → 기존 Cursor를 즉시 모두 폐기할 수도 있음
  → AUTO 정책에서는 일정 시간에 걸쳐 분산하여 무효화할 수도 있음

따라서 통계 수집 직후 모든 실행계획이 즉시 바뀐다고 단정하면 안 됩니다. 실제 Cursor의 LAST_LOAD_TIME, INVALIDATIONS, Child 생성 시점과 Plan 변화를 확인해야 합니다.

9.3 흐름

객체·의존 정보 변경 또는 실행 구조 Aging
  → Cursor Invalid·Reload 필요
  → 다음 Parse 또는 Execute에서 재검증·재파싱
  → LOADS·INVALIDATIONS·Hard Parse 증가 가능

10. 기본 진단 방법

10.1 Parent Cursor 집계: V$SQLAREA

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

10.2 해석 원칙

PARSE_CALLS ≈ EXECUTIONS
  → 실행할 때마다 Parse 요청하는 Application 구조 가능성

VERSION_COUNT 높음
  → Child Cursor가 많이 생성된 원인 확인 필요

LOADS 증가
  → Aging·Invalidation 후 Reload 또는 새 Cursor Load 가능성

INVALIDATIONS 증가
  → DDL·Compile·통계·환경 변경 시점 대조

PARSE_CALLS만으로 Hard Parse 횟수를 확정할 수 없습니다. Soft Parse, Session Cursor Cache Hit와 Hard Parse가 함께 포함될 수 있습니다.

10.3 System·Session Parse 통계

SELECT name,
       value
FROM v$sysstat
WHERE name IN (
    'parse count (total)',
    'parse count (hard)',
    'parse time cpu',
    'parse time elapsed',
    'session cursor cache hits'
);

Session 단위로 확인하려면 V$SESSTAT과 V$STATNAME을 연결합니다.

주요 해석은 다음과 같습니다.

누적값 자체보다 정상 구간과 문제 구간의 증가량과 초당 발생률을 비교해야 합니다.

10.4 Library Cache 전체 상태

SELECT namespace,
       gets,
       gethits,
       pins,
       pinhits,
       reloads,
       invalidations
FROM v$librarycache
ORDER BY namespace;

RELOADS는 이미 생성된 Object Handle이 존재하지만 필요한 Metadata Piece를 다시 Load한 횟수와 관련됩니다. INVALIDATIONS는 의존 객체 변경으로 Object가 Invalid로 표시된 횟수입니다.

이 View도 Instance 시작 이후 누적값이므로 문제 시간대의 Delta를 확인합니다.

11. 실전 판단 순서

1. 문제 SQL의 정확한 SQL Text와 SQL_ID를 확인한다.
2. 값만 다른 Literal SQL과 대소문자·주석·공백 변형 SQL이 증가하는지 확인한다.
3. Application이 Cursor를 Parse Once·Execute Many 방식으로 재사용하는지 확인한다.
4. PARSE_CALLS와 EXECUTIONS의 관계를 확인한다.
5. System·Session의 parse count(total)·hard와 cursor cache hits를 비교한다.
6. VERSION_COUNT, LOADS, INVALIDATIONS를 확인한다.
7. DDL·Compile·통계 수집·권한 변경 시점과 Invalidation 증가 시점을 대조한다.
8. 후속 이론에서 V$SQL_SHARED_CURSOR로 Child 공유 실패 원인을 확인한다.
9. Parse CPU, Mutex·Library Cache 대기와 전체 처리량을 함께 비교한다.
10. 변경 후 Hard Parse·Soft Parse·Execute without Parse와 응답시간을 재측정한다.

문제 유형과 대응을 구분합니다.

Shared Pool을 Flush하는 것은 원인을 해결하는 방법이 아닙니다. 기존 실행 구조를 제거하여 이후 대량 Hard Parse를 유발할 수 있으므로 진단 목적 없이 반복해서 사용하면 안 됩니다.

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

13. 핵심 정리

Application 재사용
  → Parse Once, Execute Many

Parse 요청
  → Session Cursor Cache Hit 가능
  → Syntax Check
  → Semantic Check
  → Shared Pool Check
      ├─ 호환 Cursor 존재: Soft Parse
      └─ 호환 Cursor 없음: Hard Parse
                              → Optimization
                              → Row Source Generation

• Library Cache는 SQL·PL/SQL의 실행 가능한 구조와 실행계획을 공유합니다.
• Dictionary Cache는 객체·사용자·권한 등의 Metadata 확인을 지원합니다.
• Shared SQL Area는 공통 실행 정보를, Private SQL Area는 Bind 값과 실행·Fetch 상태를 관리합니다.
• Bind Variable은 Parent Cursor 공유 가능성을 높이고, Statement 재사용은 Parse Call 자체를 줄입니다.
• Soft Parse는 Hard Parse보다 저렴하지만 과도하면 확장성을 떨어뜨립니다.
• Hard Parse는 Optimizer CPU, Shared Pool 메모리, Dictionary 조회와 Mutex·Lock·Pin 경합을 유발할 수 있습니다.
• DDL과 Compile은 Cursor Invalidation을 유발할 수 있으며 통계 수집은 Rolling Invalidation을 사용할 수 있습니다.
• Parse 진단은 SQL별 집계와 System·Session 통계의 Delta를 함께 봐야 합니다.