한 번에 11블록 읽는 조회가 오전마다 공유 풀 경보를 울린다

기초SELECT유통546 / 700

상황

본사 레거시 유통의 「영수증 조회 · 반품」 화면(2011년, Java)은 매장 6곳에서 영수증 번호로 판매 한 건을 연다. 오전에만 3만 번쯤 호출된다. DBA는 이렇게 말했다. "오전마다 공유 풀 경보와 latch: shared pool 대기가 뜹니다. CURSOR_SHARING을 FORCE로 바꾸면 한 번에 정리된다니 오늘 밤 반영하겠습니다." 같은 DB에는 다른 화면과 배치도 붙어 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · SLE_NO VARCHAR2(15) '20230601-0123'(영수증 번호)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증 한 장에 상품 1~4개
CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO)
한 번 실행하면 인덱스로 11블록 안팎을 읽는다. 통계는 최신이다.

원본 SQL

sql

// ReceiptDao.java (2011) — 영수증 번호를 SQL 문자열에 이어 붙인다
String sql = "SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC "
           + "FROM CF10 h, CF11 d, CF01 i "
           + "WHERE h.SLE_NO = '" + sleNo + "' "
           + "AND d.SLE_NO = h.SLE_NO AND i.ITM_NO = d.ITM_NO";
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[오전 3만 번 — 연습 DB 에서 화면 그대로 재현 · 한 세션 (세션 통계 증가분)]
  경과 시간                     27.8 초
  parse count (total)           30,214
  parse count (hard)            30,012
  parse time cpu                2,472 cs
  CPU used by this session      2,630 cs
  session logical reads         391,830   (한 번에 약 13 — 실행 11 + 하드 파싱의 딕셔너리 조회)

[V$SQL 에 보이는 것]
  SELECT h.SLE_NO, h.SLE_DT, … WHERE h.SLE_NO = '20231105-0412' AND d.SLE_NO = h.SLE_NO AND i.ITM_NO = d.ITM_NO
  SELECT h.SLE_NO, h.SLE_DT, … WHERE h.SLE_NO = '20231105-0413' AND d.SLE_NO = h.SLE_NO AND i.ITM_NO = d.ITM_NO
  …

[3만 번 직후 V$SQLAREA 를 FORCE_MATCHING_SIGNATURE 로 묶어 센 것]
  FMS                     PARENTS   EXECS    MB   SAMPLE
  11893327705119548241        386     386   7.9   SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, d.ITM_NO, i.ITM_NM, d.SLE_Q…

[한 번 실행의 계획]
----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |      2 |00:00:00.01 |      11 |
|   1 |  NESTED LOOPS                          |         |      1 |      2 |      2 |00:00:00.01 |      11 |
|   2 |   NESTED LOOPS                         |         |      1 |      2 |      2 |00:00:00.01 |       9 |
|   3 |    NESTED LOOPS                        |         |      1 |      2 |      2 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID        | CF10    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX UNIQUE SCAN                 | CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11    |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  7 |      INDEX RANGE SCAN                  | CF11_PK |      1 |      2 |      2 |00:00:00.01 |       3 |
|*  8 |    INDEX UNIQUE SCAN                   | CF01_PK |      2 |      1 |      2 |00:00:00.01 |       1 |
|   9 |   TABLE ACCESS BY INDEX ROWID          | CF01    |      2 |      1 |      2 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------
   5 - access("H"."SLE_NO"='20231105-0412')
   7 - access("D"."SLE_NO"='20231105-0412')
   8 - access("I"."ITM_NO"="D"."ITM_NO")

문제

한 번에 11블록을 읽는 조회가 CPU를 다 쓰는 이유를 위 통계로 설명하시오. V$SQL에 남은 문장은 386개뿐인데 '3만 개'라고 말할 수 있는 근거도 쓰시오.
SQL과 화면 코드를 고치시오. DBA가 내놓은 CURSOR_SHARING = FORCE 안을 평가하시오.

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

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

모범답안

모범 답안 SQL

sql

// ReceiptDao.java — 값은 바인드로 · 문장 텍스트는 하나
String sql = "SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC "
           + "FROM CF10 h, CF11 d, CF01 i "
           + "WHERE h.SLE_NO = ? "
           + "AND d.SLE_NO = h.SLE_NO AND i.ITM_NO = d.ITM_NO";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, sleNo);
ResultSet rs = ps.executeQuery();

-- DB 에 도착하는 문장 — 하나
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d, CF01 i
 WHERE h.SLE_NO = :1
   AND d.SLE_NO = h.SLE_NO
   AND i.ITM_NO = d.ITM_NO;

-- 반영 다음 날 오전 확인 — 리터럴만 다른 무리가 남았나
SELECT force_matching_signature AS fms, COUNT(*) AS parents, SUM(executions) AS execs,
       SUBSTR(MIN(sql_text), 1, 60) AS sample
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) >= 50
 ORDER BY parents DESC;

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준

xplan

[같은 3만 번 — 바인드 (학습용 예시)]
  경과 시간                  1.1 초
  parse count (hard)         1
  parse time cpu             0 cs
  CPU used by this session   97 cs
  session logical reads      330,412   (한 번 11)
  V$SQLAREA                  부모 1 · 자식 1 · 실행 30,000

모범 답안 서술

영수증 번호가 SQL 텍스트 안에 들어 있어서, 번호가 바뀔 때마다 오라클에게는 처음 보는 문장이 된다. 오라클은 텍스트의 해시로 같은 SQL인지 가리므로, 3만 번 모두 공유 풀에서 커서를 찾지 못하고 하드 파싱(문법 검사, 의미 확인, 최적화, 실행계획 생성)을 한다. parse count (hard)는 30,012이고, parse time cpu 2,472cs가 CPU 2,630cs의 94%를 차지한다. CPU는 데이터를 읽는 데(한 번 11블록)가 아니라 어떻게 읽을지 정하는 데 쓰였다. 새 커서를 공유 풀에 넣느라 latch: shared pool을 두고 다투고, 자리가 모자라면 오래 안 쓴 커서를 밀어내므로 이웃 화면의 SQL까지 다시 하드 파싱해야 한다. V$SQL에 386개만 남은 것은 3만 개가 쌓이지 않고 지나갔기 때문이다. 공유 풀은 오래 안 쓴 커서부터 내보낸다. 그래서 개수는 V$SYSSTAT이나 세션 통계의 parse count (hard) 증가분으로 세고, 남아 있는 무리는 V$SQLAREA를 FORCE_MATCHING_SIGNATURE로 묶어 찾는다. 리터럴을 바인드로 바꿔 놓고 보면 386개가 모두 같은 지문이어서 한 무리로 묶인다. 고친 판은 영수증 번호를 바인드 변수(?)로 넘기는 PreparedStatement다. 문장 텍스트가 하나라서 하드 파싱은 한 번만 하고, 그 뒤로는 공유 커서를 쓴다. 경과 시간은 27.8초 → 1.1초, 하드 파싱은 30,012 → 1로 줄었다. CURSOR_SHARING = FORCE는 응급 처치일 뿐이다. DB 전체의 리터럴을 :"SYS_B_0"으로 바꾸므로 이 화면은 나아지지만, 다른 화면에서 일부러 리터럴로 쓴 문장(분포가 크게 치우친 상수, 교재 11화의 취소 탭)까지 하나로 묶여 첫 손님의 값이 계획을 정한다. 결과 칸 길이가 바뀌는 부작용도 있다. 근본 대책은 화면 코드를 바인드로 고치는 것이다. FORCE는 그 코드를 반영하기 전 며칠만 쓰고, 켠다면 상수가 필요한 문장에 CURSOR_SHARING_EXACT 힌트를 붙여 지킨다.

해설

바인드가 늘 정답은 아니다. 값마다 다른 실행계획을 타야 하는 곳(분포가 크게 치우친 상수, 탭마다 다른 상태값)에는 리터럴이 맞다. 어느 값을 바인드로 쓰고 어느 값을 리터럴로 쓸지는 SQL을 쓰는 사람이 정한다(교재 11화, SQL 코딩 가이드). 이 조회는 PK로 한 건을 찾으므로 값에 따라 계획이 달라질 일이 없다.
소프트 파싱도 공짜는 아니다. 클릭할 때마다 prepareStatement를 새로 호출하면 하드 파싱은 1번이어도 소프트 파싱은 3만 번이다. JDBC 문장 캐시를 켜거나 PreparedStatement를 재사용하면 파스 호출 자체가 줄어든다(교재 11화, 파싱 네 가지).
IN 리스트처럼 값의 개수가 바뀌는 자리는 바인드만으로는 텍스트가 하나로 모이지 않는다. 칸 수를 고정한 바인드나 컬렉션을 쓴다(교재 11화).
문자열 연결은 SQL 주입 구멍이기도 하다. 영수증 번호 칸에 ' OR '1'='1을 넣으면 남의 영수증이 열린다. 바인드로 바꾸면 성능 문제와 보안 문제를 함께 고칠 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 파싱 비율은 교재 11화 실측과 같은 수준 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황