인프라팀의 통보: 「CURSOR_SHARING을 FORCE로, 오늘 밤 반영합니다」

모범 답안 SQL ​

-- 인스턴스 값은 EXACT 그대로 둔다 (변경 요청 보류)

-- ① 정산 목록 '다음' — 포스트잇은 바인드로, 다음 페이지 조건은 OR 없이 (Q4 rev03)
--    값을 엿보든 못 엿보든(피킹을 끄거나 DBMS_SQL 로 부를 때) 같은 계획이 나오는 모양
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_DT <= :p_dt
           AND NOT (ORD_DT = :p_dt AND ORD_SEQ > :p_seq)
         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 11;

-- ② 정산 상태 확인 — 고치지 않는다. 값이 셋이고 값마다 계획이 다르다 → 탭마다 상수가 맞다
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
         WHERE ORD_ST = 'CANCEL' ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000;

-- ③ 재는 것 — 리터럴만 다른 문장 무리 (부모가 많고 실행이 부모 수와 비슷하면 '한 번 쓰고 버리는 문장')
SELECT force_matching_signature, COUNT(*) AS parents, SUM(executions) AS execs,
       ROUND(SUM(sharable_mem) / 1048576, 1) AS mb, MIN(sql_text) AS sample
  FROM v$sqlarea
 WHERE force_matching_signature <> 0
 GROUP BY force_matching_signature
HAVING COUNT(*) >= 10
 ORDER BY parents DESC;

-- (꼭 켜야 한다면) 정산 목록 서비스의 세션에만 · 기한 = 개발팀 반영일 · 그날 지운다
-- CREATE OR REPLACE TRIGGER TRG_STLM_LIST_FORCE AFTER LOGON ON DATABASE
-- BEGIN
--   IF SYS_CONTEXT('USERENV', 'SERVICE_NAME') = 'STLM_LIST' THEN
--     EXECUTE IMMEDIATE 'ALTER SESSION SET CURSOR_SHARING = FORCE';
--   END IF;
-- END;
-- 그 서비스가 부르는 상태 탭 문장에는 /*+ CURSOR_SHARING_EXACT */

모범 답안 서술 ​

좋아지는 것: 정산 목록은 리터럴만 다른 문장(지문 하나에 부모 308개)이라, FORCE가 상수를 바인드 자리로 바꿔 끼우면 하드 파싱이 30,431 → 329, 경과 시간이 36초 → 1.45초가 된다. 효과는 분명히 있다. 그러나 1이 되지는 않는다. FORCE는 DATE '…'를 안전하지 않은 리터럴로 다뤄 날짜마다 자식을 새로 만들기 때문이다(HASH_MATCH_FAILED, VERSION_COUNT 329, 부모 하나가 10MB). 소스를 바인드로 고치면 하드 파싱 1번, 0.67초다. 망가지는 것: 정산 상태 확인의 문장 셋은 일부러 상수로 쓴 리터럴이다. 히스토그램이 있어서 세 문장은 각자 맞는 계획을 탄다(완료는 인덱스로 1,016, 미결과 취소는 FULL로 5,826). FORCE는 세 문장을 ORD_ST = :"SYS_B_0" 하나로 묶고, 그날 첫 손님의 값을 엿봐서(바인드 피킹) 계획 하나를 정한다. 완료 탭이 먼저 열리면 취소 탭이 인덱스 계획을 타서 5,826 → 50,347(8.6배)이 되고, 취소 탭이 먼저 열리면 완료 탭이 1,016 → 5,826(5.7배)이 된다. 1,000건 화면에서는 두 일량이 ACS의 같은 칸에 들어가므로 자식도 갈라지지 않는다. 서버 스위치는 어느 리터럴을 일부러 썼는지 알지 못한다. 재는 것: V$SQLAREA를 FORCE_MATCHING_SIGNATURE로 묶어 부모가 많고 실행 수가 부모 수와 비슷한 무리를 찾고, 그 문장의 출처(화면, 소스)를 찾는다. 반대로 히스토그램이 있는 컬럼에 상수를 쓴 문장(일부러 쓴 리터럴)은 따로 목록으로 만든다. 고친 뒤에는 같은 3만 클릭으로 parse count (hard)를 다시 잰다. 고치는 것: 정산 목록을 바인드(:p_dt, :p_seq)로 고친다. 다음 페이지 조건은 OR 없이 써서, 값을 엿보지 못한 채 파싱돼도 같은 계획이 나오게 한다(rev03). 피킹을 끄거나 DBMS_SQL로 호출하면 rev02의 OR는 OR-Expansion으로 풀려 2페이지에서 1,005,991블록을 읽는다. 상태 탭은 고치지 않는다. 규칙은 SQL 코딩 가이드에 남긴다. 화면이 고르는 값은 바인드로, 값마다 계획이 달라야 하는 코드값은 상수 리터럴로 쓰고, CURSOR_SHARING은 EXACT로 둔다. 꼭 켜야 한다면: 인스턴스 전체(ALTER SYSTEM)에 거는 것은 반대한다. 정산 목록 서비스의 세션에만(로그온 트리거의 ALTER SESSION) 켜고, 그 서비스가 호출하는 상태 탭 문장에는 /*+ CURSOR_SHARING_EXACT */를 붙인다. 개발팀 반영일을 기한으로 적어 두고 그날 지운다. 그 세션 범위의 화면과 파일은 결과 칸 길이가 바뀌는지(SUBSTR 결과 16 → 160바이트)도 함께 점검한다.

해설 ​

• FORCE의 이득은 진짜다. 인프라팀의 관찰(같은 지문의 문장이 수만 개 지나간다)은 맞았다. 반박만 하지 말고 숫자를 나란히 적는다(30,431 → FORCE 329 · 바인드 1).
• 두 번째 안 '피킹을 끄자'(_optim_peek_user_binds = FALSE)를 따르면 상태 탭은 값과 관계없이 인덱스 계획을 타고(취소는 늘 50,347), rev02는 2페이지에서 1,005,991블록을 읽는다. 밑줄 파라미터는 지원 없이 건드리지 않는다.
• 결과 칸 길이도 바뀐다. FORCE를 켜면 SUBSTR(CUS_ID, 1, 4)의 결과 칸이 16 → 160바이트, 'A'가 1 → 32가 됐다. 그래서 고정폭 화면이나 파일이 깨지는데, 실행계획이나 Buffers로는 보이지 않는다.
• ACS에 기대지 않는다. 1,000건 화면은 두 일량이 같은 칸에 들어가 고치지 못했고, 100건 화면도 나쁜 실행을 한 번 겪은 뒤에야 고쳤다.
• Free 한 대에서는 ORA-04031을 재현하지 못했다. 3만 개를 넣어도 공유 풀은 오래된 커서를 내보내며 버텼다. 그 대가로 밀려난 이웃(Q1)이 다시 하드 파싱을 해야 했다.

관련 교재 ​

• 11화 · 같은 SQL 이 수만 개
• 11화 · CURSOR_SHARING=FORCE 의 이득과 함정