작년 가을 응급으로 켠 CURSOR_SHARING=FORCE를 되돌리자는 안건이 올라왔다

실전SELECT유통561 / 700

상황

작년 가을 영수증 화면 때문에 공유 풀 경보가 울리자, DBA가 유통 운영 DB 인스턴스에 CURSOR_SHARING = FORCE를 켰다. 영수증 화면은 그 뒤 바인드 변수로 고쳤다. 이번 달 함수 기반 인덱스 사고까지 겪고 나서 박팀장이 안건을 올렸다. "FORCE를 EXACT로 되돌립시다. 다만 끄면 어디가 터지는지 먼저 보고해 주세요." 선배가 V$SQLAREA에서 FORCE가 손댄 문장(텍스트에 SYS_B_가 들어간 문장)을 실행 수로 줄 세웠다. 매출 리포트 담당자는 "월말 리포트만 유난히 느리다"는 말을 덧붙였다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT)(리프 1,393블록) · 하루 약 700건, 한 달 약 20,800건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매 한 건에 상품 약 2.2개
CF01 상품: 109행 · 5블록 · CF12 발주: 734행 · 10블록
CURSOR_SHARING = FORCE(인스턴스) · 바인드 피킹 켬 · 매출 리포트는 일 마감(하루 한 번)과 월말(한 달에 한 번)이 같은 프로그램에서 날짜 범위만 바꿔 나간다.

원본 SQL

sql

-- ① 판매상품 상세 팝업 (2011년 Java · 오전에만 약 2만 번) : 영수증 번호와 상품 번호를 이어 붙인다
SELECT d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC
  FROM CF11 d, CF01 i
 WHERE d.SLE_NO = '20231105-0412'
   AND d.ITM_NO = 1052
   AND i.ITM_NO = d.ITM_NO;

-- ② 매출 리포트 (일 마감과 월말이 같은 문장 모양) : 날짜 범위를 리터럴로 쓴다
SELECT h.SHP_NO, SUM(d.SLE_QTY * d.SLE_UPRC) AS SLE_AMT
  FROM CF10 h, CF11 d
 WHERE h.SLE_DT BETWEEN '20230601' AND '20230630'
   AND d.SLE_NO = h.SLE_NO
 GROUP BY h.SHP_NO;

-- ③ 영수증 조회 (작년에 바인드 변수로 고쳤다)
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, d.ITM_NO, d.SLE_QTY
  FROM CF10 h, CF11 d
 WHERE h.SLE_NO = :1
   AND d.SLE_NO = h.SLE_NO;

-- ④ 발주 진행 탭 (상태값 셋 · 탭마다 상수)
SELECT ORD_NO, ORD_DT, PTNR_NO FROM CF12 WHERE ORD_PRGS_CD = '12' ORDER BY ORD_DT DESC;

-- 안건 : 오늘 밤
ALTER SYSTEM SET CURSOR_SHARING = EXACT SCOPE = BOTH;

실행계획 학습용 예시

xplan

[V$SQLAREA · 오전 09 ~ 12시 · FORCE가 손댄 문장 (SQL_TEXT에 SYS_B_) · 실행 수 순]
  #   EXECUTIONS   VERSION_COUNT   SQL_TEXT (앞부분)
  ①       21,480               1   SELECT d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC FROM CF11 d, CF01 i WHERE d.SLE_NO = :"SYS_B_0" AND d.ITM_NO = :"SYS_B_1" …
  ④          412               1   SELECT ORD_NO, ORD_DT, PTNR_NO FROM CF12 WHERE ORD_PRGS_CD = :"SYS_B_0" ORDER BY ORD_DT DESC
  ②            1               1   SELECT h.SHP_NO, SUM(d.SLE_QTY * d.SLE_UPRC) AS SLE_AMT FROM CF10 h, CF11 d WHERE h.SLE_DT BETWEEN :"SYS_B_0" AND :"SYS_B_1" …
  (③은 SYS_B_가 없다 · 같은 시간 실행 18,730)

[② 매출 리포트 · 지난달 커서 하나로 일 마감 30번과 월말 1번을 처리 · 아래는 월말 실행]
Plan hash value: 3318806174   (6월 1일 일 마감 때 하드 파싱 · Peeked Binds '20230601', '20230601')

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |      6 |00:00:00.61 |   83614 |
|   1 |  HASH GROUP BY                         |                |      1 |      6 |      6 |00:00:00.61 |   83614 |
|   2 |   NESTED LOOPS                         |                |      1 |   1540 |  45760 |00:00:00.55 |   83614 |
|   3 |    NESTED LOOPS                        |                |      1 |   1540 |  45760 |00:00:00.31 |   62781 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    706 |  20833 |00:00:00.04 |     251 |
|*  5 |      INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    706 |  20833 |00:00:00.01 |      60 |
|*  6 |     INDEX RANGE SCAN                   | CF11_PK        |  20833 |      2 |  45760 |00:00:00.19 |   62530 |
|   7 |    TABLE ACCESS BY INDEX ROWID         | CF11           |  45760 |      1 |  45760 |00:00:00.17 |   20833 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("H"."SLE_DT">=:SYS_B_0 AND "H"."SLE_DT"<=:SYS_B_1)
   6 - access("D"."SLE_NO"="H"."SLE_NO")

  (같은 커서로 일 마감 하루치를 돌리면 Buffers 2,836)

[참고 · 월말 문장을 내 세션에서 EXACT로 따로 파싱하면 (ALTER SESSION)]
  HASH JOIN · CF10 인덱스 범위 251 + CF11 FULL 8,204 → Buffers 8,455 · 0.31초

[① 팝업 · 내 세션만 EXACT로 바꿔 2만 번 재현 (ALTER SESSION SET CURSOR_SHARING = EXACT)]
  parse count (hard) 20,014 · parse time cpu 1,640 cs · 경과 19.2초   (FORCE에서는 1.9초)

문제

오늘 밤 그대로 EXACT로 바꾸면 ①~④가 각각 어떻게 되는지 위 자료로 예측하시오.
바꾸기 전에 고칠 SQL을 고쳐 쓰고, 그대로 둘 SQL은 왜 그대로 두는지 쓰시오.
전환 순서와 전환 뒤에 지켜볼 숫자, 되돌릴 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 0) 전환 전 목록 : FORCE가 손댄 문장을 실행 수로 줄 세운다 (전환 전 2주 · 매일 오전)
SELECT sql_id, executions, version_count, SUBSTR(sql_text, 1, 80) AS sample
  FROM v$sqlarea
 WHERE sql_text LIKE '%SYS\_B\_%' ESCAPE '\'
 ORDER BY executions DESC
 FETCH FIRST 30 ROWS ONLY;

// 1) 전환 전에 고친다 : ① 판매상품 상세 팝업 · 값은 바인드 변수로
String sql = "SELECT d.ITM_NO, i.ITM_NM, d.SLE_QTY, d.SLE_UPRC FROM CF11 d, CF01 i "
           + "WHERE d.SLE_NO = ? AND d.ITM_NO = ? AND i.ITM_NO = d.ITM_NO";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, sleNo);
ps.setInt(2, itmNo);

-- 2) 그대로 둔다 : ② 리포트(하루 한 번 · 한 달에 한 번, 리터럴이 맞다) · ③ 이미 바인드 · ④ 상태값 셋

-- 3) 전환 : 먼저 화면 서버 한 대의 세션만 (커넥션 풀 초기화 SQL) → 이틀 지켜본 뒤 인스턴스 전체
ALTER SESSION SET CURSOR_SHARING = EXACT;
ALTER SYSTEM SET CURSOR_SHARING = EXACT SCOPE = BOTH;

-- 4) 전환 뒤 매일 오전 : 하드 파싱 증가분과 리터럴만 다른 문장 무리
SELECT name, value FROM v$sysstat WHERE name IN ('parse count (hard)', 'parse count (total)');

SELECT force_matching_signature, COUNT(*) AS parents, SUM(executions) AS execs
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) >= 50
 ORDER BY parents DESC;

-- 되돌릴 조건 : 오전 하드 파싱이 초당 20을 넘거나 공유 풀 경보가 다시 울리면 그날 바로
-- ALTER SYSTEM SET CURSOR_SHARING = FORCE SCOPE = BOTH;

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

xplan

[전환 2주 뒤 (학습용 예시)]
  ① 팝업                부모 1 · 오전 실행 21,300 · 하드 파싱 1
  ② 월말 리포트         자기 문장 · HASH JOIN · Buffers 83,614 → 8,455 · 0.61 → 0.31초
     일 마감            자기 문장 · NESTED LOOPS · Buffers 2,836 (그대로)
  ③ 영수증 조회         변화 없음
  ④ 발주 탭             문장 셋 (상태값마다) · 각 10블록
  오전 parse count (hard)     시간당 약 1,900 (FORCE 때 약 1,700)
  FMS 무리 (부모 50개 이상)   없음

모범 답안 서술

그대로 EXACT로 바꾸면: ① 판매상품 상세 팝업은 지금 FORCE 덕에 커서 하나(실행 21,480)를 쓰고 있다. 소스는 여전히 영수증 번호와 상품 번호를 이어 붙이므로, 끄는 순간 오전에만 2만 번 하드 파싱을 한다. 내 세션에서 재 보니 2만 번에 19.2초(FORCE 1.9초), 파싱 CPU 1,640cs였다. 작년 가을의 공유 풀 경보가 그대로 돌아온다. ② 매출 리포트는 좋아진다. FORCE 아래에서는 일 마감과 월말이 BETWEEN :"SYS_B_0" AND :"SYS_B_1" 하나로 묶여, 6월 1일 일 마감 때 하루치 값을 엿보고 세운 NL 계획을 월말도 그대로 썼다. 한 달치 20,833건마다 CF11_PK를 찾아 83,614블록을 읽는다. EXACT에서는 날짜 리터럴마다 제 문장이 되어 월말은 해시 조인(8,455블록)을, 일 마감은 NL(2,836블록)을 따로 고른다. ③ 영수증 조회는 이미 바인드 변수라 텍스트에 SYS_B_가 없고 변화가 없다. ④ 발주 탭은 상태값마다 문장이 셋으로 나뉠 뿐이고 10블록짜리 표라 어느 쪽이든 괜찮다. 고치는 것과 두는 것: 전환 전에 ①만 바인드 변수로 고친다. 값을 바인드 변수로 넘기면 FORCE 없이도 커서는 하나다. ②는 그대로 둔다. 하루 한 번, 한 달에 한 번 도는 리포트라 리터럴로 써도 하드 파싱은 날마다 한 번뿐이고, 날짜 범위마다 맞는 계획을 고를 수 있어 리터럴이 오히려 맞다. ③은 고칠 것이 없고, ④는 값이 셋뿐인 상수라 리터럴로 둔다. 전환 순서: 전환 전 2주 동안 SYS_B_가 들어간 문장을 실행 수로 줄 세워 ① 같은 문장이 더 없는지 확인한다. 실행 수가 많은 것을 모두 바인드 변수로 고친 뒤, 화면 서버 한 대의 세션에만 EXACT를 걸어(ALTER SESSION) 이틀 지켜보고 인스턴스 전체를 바꾼다. 전환 뒤에는 parse count (hard) 증가분과 FORCE_MATCHING_SIGNATURE 무리를 매일 오전 확인한다. 오전 하드 파싱이 초당 20을 넘거나 공유 풀 경보가 다시 울리면 그날 바로 FORCE로 되돌리고, 원인 문장을 고친 다음 다시 시도한다.

해설

FORCE를 끄는 일은 켜는 일보다 위험하다. 켜 둔 동안 새로 들어온 리터럴 코드는 FORCE 덕에 문제가 드러나지 않았다. 그래서 끄기 전에 FORCE가 덮어 주던 문장, 곧 SYS_B_가 든 문장의 목록부터 만든다.
② 같은 리포트는 ACS도 고쳐 주지 못했다. 일 마감(2,836블록)과 월말(83,614블록)이 ACS가 일량을 세는 같은 칸에 들어가기 때문이다(교재 11화 §7). 전환을 기다리지 않고 당장 고치려면 리포트 문장에 /*+ CURSOR_SHARING_EXACT */를 붙인다.
전환 직후 하드 파싱이 조금 느는 것(시간당 1,700 → 1,900)은 정상이다. 리터럴로 둔 리포트와 상태값 문장 몫이다. 문제는 값마다 새 문장이 생기는 무리이고, FMS로 묶어 보면 가려진다.
끄면 FORCE가 바꿔 놓았던 결과 칸 길이도 원래대로 돌아온다(교재 11화 §8 함정 3). 그사이 FORCE의 칸 길이에 맞춰 만든 화면이나 파일이 있는지도 점검 목록에 넣는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황