Cursor 재사용 계층: Library Cache·Session Cursor Cache·JDBC Statement Cache

핵심 요약

같은 SQL을 반복 실행할 때의 재사용은 하나의 Cache로 끝나지 않습니다. 애플리케이션/JDBC, Session, Library Cache는 서로 다른 위치에서 서로 다른 비용을 줄입니다.

애플리케이션·Oracle JDBC/UCP Statement Cache
→ Statement Object·Database Cursor 재생성, 반복 Prepare와 통신 비용을 줄임

Session Cursor Cache
→ 같은 Session이 닫은 Session Cursor의 Shared Child Cursor 참조를 보관
→ 후속 Parse Call의 실제 Reparse 작업을 피하거나 줄임

Library Cache
→ Shared Pool의 Parsed·Executable SQL과 PL/SQL을 여러 실행·Session이 공유
→ Hard Parse와 실행구조 재생성 비용을 줄임

따라서 다음 두 문장은 같은 뜻이 아닙니다.

Hard Parse가 적다
≠ Parse Call이 적다

Session Cursor Cache Hit가 발생했다
≠ 애플리케이션이 Parse Call을 보내지 않았다

Session Cursor Cache에서 Cursor를 재사용해도 Parse Call 통계에는 기록될 수 있지만 실제 Reparse는 피할 수 있습니다. Database에 전달되는 Prepare·Parse 요청 자체를 줄이려면 애플리케이션이 Prepared Statement를 계속 열어 재실행하거나, Oracle JDBC/UCP의 Statement Cache가 같은 Physical Connection에서 Statement를 재사용해야 합니다.

학습 목표

• Parse Call, Reparse, Soft Parse, Hard Parse의 관계를 구분한다.
• Library Cache, Session Cursor Cache, JDBC Statement Cache의 저장 위치와 재사용 범위를 설명한다.
• Session Cursor Cache가 활성화되는 반복 기준과 통계 해석 방법을 설명한다.
• SESSION_CACHED_CURSORS와 OPEN_CURSORS가 독립적인 Parameter인 이유를 설명한다.
• Connection Pool의 Physical Connection별 Statement Cache와 Open Cursor 자원 총량을 계산한다.
• V$SESSTAT, V$SQLSTATS, V$OPEN_CURSOR, V$SQL_SHARED_CURSOR를 연결해 Parse 비용을 진단한다.
• Cache 크기 확대보다 SQL Text·Bind·Statement 생명주기 개선을 우선한다.

1. 먼저 재사용 계층과 Cursor 종류를 구분한다

SQL 실행에는 다음 요소가 서로 연결됩니다.

Application Statement Object
→ Database Session Cursor
→ Shared Child Cursor
→ Library Cache의 실행 가능한 구조

여기서 Session Cursor는 특정 Session이 Shared Child Cursor를 사용하는 인스턴스입니다. Library Cache의 Shared Child Cursor와 Session Cursor는 같은 객체가 아닙니다.

2. Parse Call·Reparse·Soft Parse·Hard Parse

2.1 Parse Call

Parse Call은 애플리케이션이나 Database 내부 코드가 SQL을 사용할 수 있도록 준비를 요청한 횟수입니다. parse count (total)에는 Hard·Soft·Describe Parse Call이 포함됩니다.

2.2 Soft Parse

Library Cache에 공유 가능한 Parsed Representation이 있어 재사용하는 경우입니다. Optimizer가 새 실행계획을 만드는 Hard Parse는 피하지만 다음 비용이 완전히 0이 되는 것은 아닙니다.

• SQL Text와 Shared Cursor 탐색
• 참조 객체·권한·Session 환경 확인
• Shared Child Cursor의 공유 가능성 검사
• Library Cache 관련 동기화와 CPU

2.3 Hard Parse

공유 가능한 실행구조가 없거나 기존 구조를 사용할 수 없어 새로운 실행 가능한 형태를 만드는 경우입니다.

문법·의미 분석
→ 객체·권한 확인
→ Query Transformation
→ Cardinality·Cost 추정
→ 실행계획과 Row Source 생성
→ Library Cache 등록

Hard Parse는 Soft Parse보다 CPU·Memory·동기화 비용이 큽니다. 그러나 Hard Parse 비율이 낮더라도 실행마다 Soft Parse가 발생하면 전체 Parse CPU와 확장성 비용은 커질 수 있습니다.

2.4 Session Cursor Cache Hit의 통계적 의미

Session Cursor Cache에서 재사용해도 Oracle 공식 설명상 Parse Call로 등록됩니다. 반면 session cursor cache hits는 해당 SQL 또는 PL/SQL이 실제로 Reparse될 필요가 없었던 횟수입니다.

대략적인 실제 Parse 작업 횟수
= parse count (total) - session cursor cache hits

이 계산은 Session Cursor Cache 효과를 해석하는 보조 지표입니다. 애플리케이션이 보내는 Parse Call 자체를 측정하려면 Application Trace와 parse count (total)의 구간 Delta를 함께 봅니다.

3. Library Cache: Shared SQL과 실행구조 재사용

Library Cache는 Shared Pool에서 최근 사용한 SQL·PL/SQL의 Parsed·Executable Form을 관리합니다.

Parent Cursor
→ SQL Text와 SQL_ID를 중심으로 Statement를 식별

Child Cursor
→ 실행계획·Bind Metadata·Optimizer 환경·참조 객체 등
→ 실제 공유 가능 조건을 만족하는 실행 가능한 형태

3.1 공유 조건

일반적인 Exact Sharing에서는 다음 조건이 중요합니다.

• SQL Text가 공백·대소문자·주석까지 문자 단위로 동일
• 참조하는 Schema Object가 동일
• Bind 이름·Data Type·길이가 호환
• Optimizer Goal과 Session 환경이 호환
• 기존 Cursor가 유효하고 필요한 실행구조가 Memory에 존재

다음 SQL은 업무 의미가 비슷해도 Text가 달라 별도 Parent Cursor가 될 수 있습니다.

SELECT customer_name FROM customer WHERE customer_id = 1001;
SELECT customer_name FROM CUSTOMER WHERE customer_id = 1002;

Bind를 사용해 Text를 안정화합니다.

SELECT customer_name
FROM   customer
WHERE  customer_id = :customer_id;

3.2 Child Cursor 증가

SQL_ID가 같아도 Bind Metadata, Optimizer 환경, Object 상태, 권한, Adaptive Cursor Sharing 등의 이유로 여러 Child Cursor가 존재할 수 있습니다.

SELECT sql_id,
       plan_hash_value,
       executions,
       parse_calls,
       loads,
       invalidations,
       version_count,
       avg_hard_parse_time,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sqlstats
WHERE  sql_id = :sql_id;

VERSION_COUNT가 높다는 사실만으로 원인을 확정하지 않습니다. 동일 SQL의 공유 실패 사유는 V$SQL_SHARED_CURSOR의 Flag를 확인하고, Bind Metadata·Optimizer 환경·Invalidation 이력을 함께 분석합니다.

3.3 Load와 Invalidation

• LOADS: SQL Object가 Library Cache에 Load 또는 Reload된 횟수
• INVALIDATIONS: 의존 객체 변경 등으로 Child Cursor가 Invalid 처리된 횟수
• V$LIBRARYCACHE.RELOADS: 이전에 Cache됐던 Object를 다시 Load해야 했던 활동

이 값들은 누적값이므로 Instance 시작 이후 총량만 보지 말고 장애 전후 Delta를 비교합니다.

4. Session Cursor Cache: 닫힌 Session Cursor의 참조 재사용

SESSION_CACHED_CURSORS는 각 Session이 Cache할 수 있는 닫힌 Session Cursor 수의 최대값입니다. Oracle AI Database 26ai의 기본값은 50입니다.

4.1 Cache 진입 조건과 동작

공식 동작은 다음과 같습니다.

같은 Session에서 동일 Statement Parse·사용
→ Cursor Close가 반복됨
→ 세 번 닫힌 뒤 반복 Statement로 판단
→ Session Cursor Cache에 Shared Child Cursor 참조 보관
→ 후속 Parse Call에서 Session 배열의 Pointer 탐색
→ Session·System 환경이 호환되면 재사용

Cache는 LRU 방식으로 공간을 확보하고, 장시간 유휴 Cursor는 내부 시간 기준으로 Aging될 수 있습니다.

중요한 점은 Session Cursor Cache가 Parse Call을 애플리케이션에서 제거하는 기능이 아니라는 것입니다. 재사용은 Parse 통계에 기록될 수 있지만 실제 Reparse 작업과 Shared Cursor 탐색 비용을 줄입니다.

4.2 SESSION_CACHED_CURSORS와 OPEN_CURSORS는 독립적이다

Session Cursor Cache의 Cursor는 Open 상태가 아니므로 SESSION_CACHED_CURSORS를 OPEN_CURSORS보다 크게 설정할 수도 있습니다. 둘의 기본값이 모두 50이더라도 역할은 다릅니다.

4.3 Session 통계

SELECT n.name,
       s.value
FROM   v$sesstat s
JOIN   v$statname n
  ON   n.statistic# = s.statistic#
WHERE  s.sid = :sid
AND    n.name IN (
         'parse count (total)',
         'parse count (hard)',
         'parse time cpu',
         'parse time elapsed',
         'session cursor cache hits',
         'session cursor cache count',
         'opened cursors current',
         'opened cursors cumulative'
       )
ORDER BY n.name;

다음 조건이 함께 성립할 때 증설을 검토합니다.

같은 Session이 동일 SQL을 반복 Parse
+ session cursor cache count가 설정 최대값에 가까움
+ session cursor cache hits / parse count (total)이 낮음
+ Application Statement 재사용만으로 해결되지 않음

단순히 Count가 최대값에 가깝다는 이유만으로 확대하지 않습니다. SQL 종류가 계속 바뀌는 Dynamic SQL이라면 Cache를 키워도 Hit가 개선되지 않을 수 있습니다.

4.4 V$SESSION_CURSOR_CACHE의 한계

V$SESSION_CURSOR_CACHE는 현재 Session의 MAXIMUM, COUNT, OPENS, HITS, HIT_RATIO를 보여 줍니다. 그러나 Oracle 공식 문서는 이 View가 SESSION_CACHED_CURSORS 효과를 측정하는 View가 아니라고 명시합니다.

따라서 다음을 함께 사용합니다.

• 대상 Session의 V$SESSTAT Delta
• 반복 SQL의 Application Trace
• parse count (total)과 session cursor cache hits
• Parameter 변경 전후 동일 부하의 CPU·응답시간

5. Oracle JDBC·UCP Statement Cache: Physical Connection별 재사용

Oracle JDBC Statement Cache는 반복 실행되는 Prepared·Callable Statement를 Client 측 Physical Connection에 보관합니다. Oracle은 Implicit Statement Cache 사용을 강하게 권장합니다.

5.1 Physical Connection별 Cache

Connection Pool
├─ Physical Connection A → Statement Cache A
├─ Physical Connection B → Statement Cache B
└─ Physical Connection C → Statement Cache C

Logical Connection이 닫혀 Pool에 반환돼도 Underlying Physical Connection이 유지되면 그 Connection의 Cache를 이후 Logical Connection이 이용할 수 있습니다. 다음 요청이 다른 Physical Connection을 빌리면 다른 Cache가 적용됩니다.

5.2 Implicit Cache의 대상과 Matching

Implicit Statement Cache는 OraclePreparedStatement와 OracleCallableStatement에 적용됩니다. SQL String 없이 생성되는 일반 OracleStatement는 대상이 아닙니다.

Cache Hit를 위한 대표 조건은 다음과 같습니다.

• SQL String이 대소문자를 포함해 동일
• Prepared 또는 Callable Statement Type이 동일
• 생성되는 Result Set의 Forward-Only·Scrollable Type이 동일

Implicit Cache가 활성화된 상태에서 Prepared/Callable Statement의 close()를 호출하면 Logical 사용은 종료되지만 Statement가 Cache로 반환될 수 있습니다. Cache에서 다시 꺼낼 때 State와 Data는 기본값으로 초기화되고 Metadata는 재사용됩니다.

5.3 UCP 설정과 기본 상태

UCP의 Statement Cache는 maxStatements가 설정되지 않았거나 0이면 비활성화됩니다. 크기는 Physical Connection당 Distinct Statement 수를 기준으로 산정하며, 무조건 큰 값이 정답은 아닙니다.

예상 Client Statement Cache 최대량
= Physical Connection 수 × Connection당 Cache Size

Cached Statement는 Database Cursor 등 자원을 유지할 수 있으므로 Pool Connection 수와 Cache Size의 곱이 OPEN_CURSORS 한도와 Memory에 미치는 영향을 검증합니다.

5.4 구조 변경 주의

Statement를 생성·실행한 뒤 Table 구조를 변경하고 이전 Cached Statement를 재사용하면 오류가 발생할 수 있습니다. 배포 중 DDL과 장기 Connection Pool이 함께 있는 환경에서는 Cache Purge·Connection Refresh 정책을 준비합니다.

6. Prepared Statement 생명주기와 Bind

가장 직접적인 개선은 반복문 바깥에서 Prepared Statement를 한 번 준비하고 Bind 값만 변경해 실행하는 것입니다.

String sql =
    "SELECT customer_name " +
    "FROM customer " +
    "WHERE customer_id = ?";

try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (long id : customerIds) {
        ps.setLong(1, id);

        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                // 결과 처리
            }
        }
    }
}

다음 구조는 동일 SQL을 반복 Prepare하고 Close하므로 Application Statement 재사용이 없습니다.

for (long id : customerIds) {
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setLong(1, id);
        ps.executeQuery();
    }
}

JDBC Implicit Statement Cache가 활성화돼 있다면 두 번째 구조의 비용 일부를 줄일 수 있지만, 첫 번째 구조처럼 하나의 Statement Object를 직접 재실행하는 방식이 생명주기와 의도를 더 명확하게 통제할 수 있습니다.

Bind Variable은 Parent Cursor 폭증을 줄이는 핵심이지만 다음을 자동으로 해결하지는 않습니다.

• Bind Data Type·길이 불일치
• 데이터 편중과 Adaptive Cursor Sharing
• Optional Predicate로 인한 Plan 부적합
• Optimizer 환경 차이
• Application의 반복 Prepare 구조

7. Open Cursor 자원을 정확히 계산한다

7.1 Server Session Cursor Cache와 JDBC Cache를 혼동하지 않는다

Session Cursor Cache는 닫힌 Session Cursor를 보관하며 OPEN_CURSORS와 독립적입니다. 반면 JDBC/UCP Statement Cache에 보관된 Statement는 Physical Connection과 Database Resource를 유지할 수 있어 Open Cursor 총량에 영향을 줄 수 있습니다.

7.2 V$OPEN_CURSOR

V$OPEN_CURSOR는 Session별로 Open·Parsed 또는 Cached Cursor를 나열하며 CURSOR_TYPE으로 상태를 구분할 수 있습니다.

SELECT sid,
       cursor_type,
       COUNT(*) AS cursor_count
FROM   v$open_cursor
WHERE  sid = :sid
GROUP BY sid, cursor_type
ORDER BY cursor_count DESC;

V$OPEN_CURSOR의 전체 행 수를 곧바로 opened cursors current와 같다고 보지 않습니다. View에는 SESSION CURSOR CACHED, PL/SQL Cache 등 여러 Cursor Type이 포함될 수 있으므로 통계와 유형을 함께 봅니다.

7.3 누수와 정상 Cache 구분

opened cursors current가 요청 종료 후에도 지속 증가
+ 같은 SQL_ID·Cursor Type이 반복 누적
+ Application에서 ResultSet·Statement Close 누락
→ Cursor Leak 가능성

Statement Cache Size 변경 뒤 일정 수준에서 안정
+ Cache Hit 증가
+ 응답시간·Parse CPU 감소
→ 의도한 Cache 유지 가능성

8. SQL·Session·Application을 연결하는 진단 절차

8.1 SQL별 Parse 재사용

SELECT sql_id,
       plan_hash_value,
       executions,
       parse_calls,
       loads,
       invalidations,
       version_count,
       ROUND(parse_calls / NULLIF(executions, 0), 4) AS parses_per_exec,
       ROUND(avg_hard_parse_time / 1000, 3) AS avg_hard_parse_ms,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM   v$sqlstats
WHERE  executions > 0
ORDER BY parse_calls DESC
FETCH FIRST 30 ROWS ONLY;

V$SQLSTATS는 SQL_ID 단위로 집계되고 Cursor가 Shared Pool에서 Aging된 뒤에도 통계가 더 오래 남을 수 있습니다. 다음처럼 해석합니다.

PARSE_CALLS / EXECUTIONS가 높음
→ 실행마다 Parse 요청이 발생하는지 Application Trace 확인

LOADS·INVALIDATIONS 증가
→ Shared Pool Aging, DDL, Dependency 변경 확인

VERSION_COUNT 증가
→ V$SQL_SHARED_CURSOR에서 공유 실패 Flag 확인

Parse Call은 Execute 수보다 클 수도 있고 작을 수도 있습니다. Cursor가 한 번 Parse된 뒤 여러 번 Execute되거나, Parse만 하고 Execute하지 않는 경로, 여러 Session의 집계가 섞일 수 있기 때문입니다.

8.2 구간 Delta

누적 통계를 그대로 비교하지 말고 동일 부하 구간의 Delta를 사용합니다.

변경 전 10분
→ Session별 Total Parse·Hard Parse·Cache Hit·Parse CPU
→ SQL별 Parse Calls·Executions·Loads·Invalidations
→ Pool별 Physical Connection·Statement Cache Size
→ Open Cursor와 응답시간

변경 후 동일 10분
→ 같은 항목을 같은 요청량으로 비교

8.3 Application 식별 정보

Connection Pool 환경에서는 DBMS_APPLICATION_INFO 또는 Driver의 Module·Action·Client Identifier를 설정해 Session과 업무 요청을 연결합니다. 하나의 Session만 표본으로 보고 Pool 전체를 일반화하지 않습니다.

9. 개선 순서

1. 같은 업무 SQL의 Text·Bind가 안정적인가?
2. 반복문·요청마다 prepareStatement()를 다시 호출하는가?
3. 하나의 Prepared Statement를 Bind만 바꿔 재실행할 수 있는가?
4. Oracle JDBC/UCP Statement Cache가 Physical Connection별로 적절한가?
5. Physical Connection 수 × Cache Size가 OPEN_CURSORS·Memory 범위 안인가?
6. Session Cursor Cache Count·Hit·Total Parse Delta는 어떤가?
7. Library Cache의 Hard Parse·Loads·Invalidations·Version Count 원인은 무엇인가?
8. 변경 후 Parse CPU·응답시간·Open Cursor·Memory가 함께 개선됐는가?

Cache Parameter 확대는 마지막이 아니라 증거가 있는 계층에서만 수행하는 조치입니다.

혼동하기 쉬운 판단

핵심 판단 순서

Parse Calls가 많은가?
→ Application이 Statement를 반복 Prepare하는가?
→ SQL Text와 Bind Metadata가 안정적인가?
→ 같은 Physical Connection에서 JDBC Cache가 Hit하는가?
→ Session Cursor Cache가 반복 SQL을 재사용하는가?
→ Library Cache의 Shared Child Cursor가 호환되는가?
→ Open Cursor·Memory 비용보다 CPU·응답시간 이득이 큰가?