하드 파싱은 한 번뿐인데 TKPROF의 Parse 줄이 실행 수만큼 찍힌다

기초DDL유통552 / 700

상황

본사 레거시 유통의 「매장 재고 확인」 화면은 매장 직원이 상품을 고를 때마다 재고 한 건을 읽는다. 작년에 이 화면도 PreparedStatement로 바꿨고, 하드 파싱은 사라졌다. 그런데 오전 10시대 ASH에는 library cache: mutex X가 여전히 보인다. 선배가 파라미터 이력을 뒤지다가 몇 해 전 DBA가 남긴 변경 기록을 찾았다. "세션 메모리 절약 (2017)"

테이블과 인덱스

CF18 일별상품재고: 408행 · 6블록 · PK (SHP_NO, ITM_NO, INV_BS_DT) · SHP_NO, ITM_NO는 NUMBER이고 INV_BS_DT는 VARCHAR2(8)다.
한 번 실행하면 PK로 2블록을 읽는다.
session_cached_cursors = 0 · open_cursors = 300 · 화면 서버는 요청마다 PreparedStatement를 만들고 실행한 뒤 닫는다(문장 캐시는 쓰지 않는다).

원본 SQL

sql

-- 2017년 DBA 변경 기록 (지금도 그대로다)
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 0 SCOPE = SPFILE;

// StockDao.java : 요청마다 문장을 준비하고, 실행하고, 닫는다
String sql = "SELECT INV_QTY FROM CF18 WHERE SHP_NO = ? AND ITM_NO = ? AND INV_BS_DT = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setInt(1, shpNo);
    ps.setInt(2, itmNo);
    ps.setString(3, bsDt);
    try (ResultSet rs = ps.executeQuery()) { /* 한 건 */ }
}

실행계획 학습용 예시

xplan

[10046 → TKPROF · 화면 서버 세션 하나 · 오전 10시대 한 시간]
SELECT INV_QTY FROM CF18 WHERE SHP_NO = :1 AND ITM_NO = :2 AND INV_BS_DT = :3

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     4120      0.29       0.41          0          0          0           0
Execute   4120      0.11       0.13          0          0          0           0
Fetch     4120      0.04       0.05          0       8240          0        4120
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    12360      0.44       0.59          0       8240          0        4120

Misses in library cache during parse: 1

[V$SYSSTAT · 같은 한 시간 증가분 (인스턴스)]
  execute count                  182,400
  parse count (total)            179,900
  parse count (hard)                  96
  session cursor cache hits            0

[ASH · 10~11시 · 화면 서버 세션의 표본 비율]
  CPU                            71%
  library cache: mutex X          8%
  cursor: pin S                   2%

문제

TKPROF의 Parse 줄과 Misses in library cache during parse 줄로 이 문장이 어떤 파싱을 하는지 설명하시오.
세션 커서 캐시가 무엇을 덜어 주는지 쓰고 파라미터를 고치시오. 반영하는 방법과 이 조치의 한계도 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 세션 커서 캐시를 되돌린다 (시스템 수준은 SPFILE에 쓰고 다음 재기동부터)
ALTER SYSTEM SET SESSION_CACHED_CURSORS = 50 SCOPE = SPFILE;

-- 재기동 전까지는 화면 서버의 커넥션 풀이 새 연결을 맺을 때 한 줄 (풀의 초기화 SQL)
ALTER SESSION SET SESSION_CACHED_CURSORS = 50;

-- 반영 뒤 확인 : 파싱 중 얼마가 세션 커서 캐시에서 끝나는가
SELECT name, value
  FROM v$sysstat
 WHERE name IN ('parse count (total)', 'parse count (hard)', 'session cursor cache hits');

개선 후 실행계획 학습용 예시

xplan

[반영 뒤 같은 시간대 한 시간 (학습용 예시)]
  parse count (total)            180,300
  parse count (hard)                  94
  session cursor cache hits      176,800    (파싱의 98%)
  ASH library cache: mutex X     8% → 2%

[TKPROF · 같은 화면 서버 세션]
  Parse 4118 · Execute 4118 · Misses in library cache during parse: 1    (파스 호출 수는 그대로다)

모범 답안 서술

Misses in library cache during parse: 1이므로 하드 파싱은 처음 한 번뿐이다. 그런데 Parse 줄의 count가 Execute와 같은 4,120이다. 화면 서버가 요청마다 문장을 준비하고 닫으므로 실행할 때마다 파스 호출이 오고, 오라클은 그때마다 소프트 파싱을 한다. 소프트 파싱은 실행계획을 다시 만들지는 않지만, 문장 텍스트의 해시로 라이브러리 캐시의 해시 체인을 따라가 커서를 찾고, 그동안 뮤텍스를 잡는다. 여러 세션이 같은 문장을 동시에 찾으면 library cache: mutex X로 기다린다. 세션 커서 캐시는 자주 닫았다 다시 여는 커서의 위치를 세션이 기억해 두는 장치다. 캐시에 있으면 해시 체인을 따라가지 않고 그 자리로 바로 간다. session_cached_cursors = 0이라 이 장치가 꺼져 있고, 그래서 session cursor cache hits가 0이다. 기본값인 50으로 되돌린다. 이 파라미터는 시스템 수준에서는 SCOPE = SPFILE로 바꾸고 재기동해야 반영되므로, 그 전까지는 커넥션 풀의 초기화 SQL에 ALTER SESSION을 넣는다. 반영하면 파싱의 98%가 캐시에서 끝나고 뮤텍스 대기는 8% → 2%로 준다. 한계도 있다. 세션 커서 캐시는 파스 호출을 가볍게 할 뿐 없애지는 못한다. TKPROF의 Parse 줄은 고친 뒤에도 4,120 언저리다. 파스 호출 자체를 없애려면 화면 서버가 문장을 재사용해야 한다(JDBC 문장 캐시).

해설

파싱 네 가지: 하드 파싱, 소프트 파싱, 세션 커서 캐시 적중, 커서를 열어 둔 채 다시 실행하기다(교재 11화 §2). 이 문제는 두 번째를 세 번째로 옮기는 일이고, 네 번째는 애플리케이션이 해야 한다.
세션 하나만 놓고 재면 소프트 파싱과 캐시 적중의 차이는 거의 보이지 않는다(교재 11화 실측: 2,000번에 7cs 대 6~9cs). 차이는 여러 세션이 같은 문장을 동시에 찾을 때 뮤텍스 대기로 드러난다.
커서는 같은 세션에서 몇 번(보통 세 번) 파싱된 뒤에 캐시에 오른다. 한 번 쓰고 마는 문장은 캐시와 상관이 없다.
캐시는 세션 메모리를 쓰므로 무작정 키우지 않는다. 세션이 되풀이해 쓰는 문장 수를 보고 정한다(50으로도 모자란 경우는 PARSE-015).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황