일별 판매 목록 SQL 하나에 자식 커서가 넷이고, 리포트 도구에서만 6,000블록을 읽는다

실전SELECT유통574 / 700

상황

본사 레거시 유통의 「일별 판매 목록」 SQL은 세 프로그램이 같은 문자열을 복사해 쓴다. Java 본사 화면(2011), 매장 단말 프로그램(C#, ODP.NET, 2018), 경영지원팀의 리포트 도구(BI)다. 리포트 도구는 접속할 때 세션의 OPTIMIZER_MODE를 FIRST_ROWS_1로 바꾼다. 지난달 인스턴스의 CURSOR_SHARING을 EXACT로 되돌린 뒤 이 문장이 AWR 「Version Count」 상위에 올랐다. 경영지원팀은 리포트가 느리다고 하고, 본사 화면과 매장 단말은 빠르다. 박팀장이 말했다. "자식이 왜 넷인지 하나씩 가려서, 고칠 것만 고쳐 주세요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15)는 날짜로 시작한다('20230601-0123') · SLE_NO 순(날짜순)으로 적재 · SLE_DT VARCHAR2(8) 하루 약 706건
인덱스 CF10_PK (SLE_NO): 리프 약 1,700블록 · IX_CF10_SLE_DT (SLE_DT): 리프 1,393블록
통계: 매일 01:00 기본값(AUTO_INVALIDATE) · 히스토그램 없음 · CURSOR_SHARING = EXACT · 바인드 피킹 켬
호출 수(하루): 본사 화면 약 3,500 · 매장 단말 약 9,000 · 리포트 도구 약 600(리포트는 하루치를 모두 받아 엑셀로 내린다)

원본 SQL

sql

-- 「일별 판매 목록」 (세 프로그램이 같은 문자열)
SELECT SLE_NO, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_DT = :dt
 ORDER BY SLE_NO

// 매장 단말 (C#, ODP.NET)
var p = new OracleParameter("dt", OracleDbType.Varchar2);
p.Value = dt;
p.Size  = 200;        // "넉넉하게"
cmd.Parameters.Add(p);

-- 리포트 도구 접속 설정 (데이터 원본 초기화 SQL)
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

실행계획 학습용 예시

xplan

[V$SQL — 15:00 · 자식 넷]
 CHILD  SHAREABLE  OPTIMIZER_MODE  :DT MAX_LENGTH  EXEC   BUF/EXEC  PLAN_HASH    처음 부른 곳 · 시각
     0  N          ALL_ROWS                    32  2,104        13  1932847016   본사 화면 · 전날 09:02
     1  N          ALL_ROWS                    32  1,388        13  1932847016   본사 화면 · 03:12
     2  Y          ALL_ROWS                  2000  9,415        13  1932847016   매장 단말 · 07:30
     3  Y          FIRST_ROWS                  32    612     6,285  2410375521   리포트 도구 · 09:10

[V$SQL_SHARED_CURSOR — Y인 칸 (그 줄의 자식이 새로 생긴 이유)]
   child 1 : ROLL_INVALID_MISMATCH = Y
   child 2 : BIND_LENGTH_UPGRADEABLE = Y
   child 3 : OPTIMIZER_MODE_MISMATCH = Y

[자식 2 (ALL_ROWS) · 한 번]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.01 |      13 |          |
|   1 |  SORT ORDER BY                       |                |      1 |    706 |    706 |00:00:00.01 |      13 |  59392 (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    706 |    706 |00:00:00.01 |      13 |          |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    706 |    706 |00:00:00.01 |       4 |          |
-------------------------------------------------------------------------------------------------------------------
   3 - access("SLE_DT"=:DT)

[자식 3 (FIRST_ROWS_1) · 리포트 도구 한 번 · 하루치를 끝까지 받는다]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    706 |00:00:00.12 |    6285 |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID         | CF10           |      1 |      1 |    706 |00:00:00.12 |    6285 |          |
|   2 |   INDEX FULL SCAN                    | CF10_PK        |      1 |    708 |   500K |00:00:00.04 |    1702 |          |
-------------------------------------------------------------------------------------------------------------------
   1 - filter("SLE_DT"=:DT)

문제

자식 넷이 각각 왜 생겼는지 Y 칸으로 설명하고, 해로운 자식과 낭비일 뿐인 자식, 정상인 자식으로 가르시오.
해로운 자식과 낭비인 자식이 생기지 않도록 각 프로그램을 고치시오.
고친 방법이 미치는 범위와 부작용(리포트 도구의 다른 리포트, 다른 화면)을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 리포트 도구 : 이 리포트의 SQL 에만 ALL_ROWS (하루치를 끝까지 받는 리포트다)
--   텍스트가 달라져 리포트 도구만의 부모가 생긴다 · 다른 리포트의 FIRST_ROWS_1 은 건드리지 않는다
SELECT /*+ ALL_ROWS */ SLE_NO, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_DT = :dt
 ORDER BY SLE_NO

// 매장 단말 (C#) : 그릇을 컬럼 크기에 맞춘다 (VARCHAR2(8) → 32바이트 구간 · 본사 화면과 같은 자식)
var p = new OracleParameter("dt", OracleDbType.Varchar2);
p.Value = dt;
p.Size  = 8;
cmd.Parameters.Add(p);

-- 본사 화면 : 그대로 · 자식 1(ROLL_INVALID_MISMATCH)은 통계 수집 뒤 생기는 정상적인 자식이다

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

xplan

[다음 날 15:00]
 본사 화면 + 매장 단말 문장   VERSION_COUNT 2 (전날 자식 · 03:xx 롤링 무효화 자식) · 실행당 13블록 · 그릇 32
 리포트 도구 문장(힌트)       VERSION_COUNT 1 · OPTIMIZER_MODE FIRST_ROWS 세션이지만 ALL_ROWS로 최적화
                              IX_CF10_SLE_DT 범위 + SORT ORDER BY · 6,285 → 13블록 · 0.12 → 0.01초

모범 답안 서술

자식 넷의 이유: Y 칸은 그 줄의 자식이 새로 생긴 이유다. 자식 0은 전날 만들어진 원래 자식이다. 자식 1은 ROLL_INVALID_MISMATCH로, 밤 01:00 통계 수집(AUTO_INVALIDATE)이 커서에 시한을 걸어 두었다가 03:12의 첫 파스 때 새로 만든 것이다. 이것은 통계가 바뀌면 계획을 다시 세우라는 정상적인 동작이다. 자식 2는 BIND_LENGTH_UPGRADEABLE이다. 매장 단말이 Size = 200으로 바인드를 넘겨 그릇이 2000바이트 구간이 되었고, 32바이트 그릇의 자식 1에 들어가지 않으니 새로 만들었다. 계획(1932847016)은 같으므로 하드 파싱 한 번과 메모리만 더 쓴 낭비다. 지금은 본사 화면도 큰 그릇의 자식 2를 같이 쓰고 자식 1은 IS_SHAREABLE = N이다. 자식 3은 OPTIMIZER_MODE_MISMATCH다. 리포트 도구의 세션이 FIRST_ROWS_1이라 같은 문장이라도 최적화 목표가 달라 따로 만들었다. 해로운 자식: 자식 3만 계획이 다르다(2410375521). FIRST_ROWS_1은 "첫 행을 빨리"를 목표로 정렬을 피하는 길을 고른다. ORDER BY SLE_NO와 순서가 같은 CF10_PK를 처음부터 훑으며 테이블에서 날짜를 거르는 계획이다. 첫 행만 받는다면 싸 보이지만, 리포트 도구는 하루치 706행을 끝까지 받아 엑셀로 내리므로 PK 리프 500K개(1,702블록)와 테이블 전체를 읽어 6,285블록이 된다. ALL_ROWS의 13블록보다 480배 많다. 고침: 리포트 도구의 이 리포트 SQL에만 /*+ ALL_ROWS */를 넣는다. 텍스트가 달라져 리포트 도구만의 부모가 생기고, 세션이 FIRST_ROWS_1이어도 이 문장은 ALL_ROWS로 최적화되어 날짜 인덱스와 정렬(13블록)을 탄다. 매장 단말은 Size = 8로 컬럼 크기에 맞춘다. 그릇이 32바이트 구간으로 내려와 본사 화면과 같은 자식을 쓰므로, 낭비인 자식 2가 더 생기지 않는다. 자식 1 같은 롤링 무효화 자식은 고칠 대상이 아니다. 범위와 부작용: 리포트 도구의 접속 설정(데이터 원본 초기화 SQL)을 ALL_ROWS로 바꾸는 방법도 있지만, 그 데이터 원본을 쓰는 다른 리포트 전부에 걸린다. 화면에 첫 행을 빨리 띄우려고 FIRST_ROWS_1을 쓰던 리포트가 있다면 그쪽이 느려질 수 있으므로, 이 리포트 하나에 힌트로 거는 것이 범위가 가장 작다. 힌트는 문장에 남아 의도가 보이는 대신, 데이터가 바뀌면 다시 볼 대상 목록(힌트 대장)에 적어 둔다. 매장 단말의 Size를 줄이면 8바이트보다 긴 값이 들어올 때 오류가 나지만, SLE_DT는 VARCHAR2(8)이라 그런 값은 원래 들어올 수 없다.

해설

V$SQL_SHARED_CURSOR의 REASON 칸(XML)은 "그 줄의 자식을 다음 파스가 쓰지 못한 이유"에 가까워 Y 칸과 한 칸씩 어긋나 보인다. "왜 자식이 생겼나"는 Y 칸으로 답한다(교재 11화 §5).
VERSION_COUNT가 크다고 모두 고칠 일은 아니다. 롤링 무효화 자식처럼 정상인 것, 그릇 크기처럼 낭비인 것, 최적화 목표처럼 계획을 바꾸는 것을 갈라서 계획이 다른 자식부터 본다.
바인드 크기를 넉넉하게 잡는 습관은 PL/SQL 변수(PARSE-019)나 Python 드라이버(PARSE-030)에서도 같은 자식을 만든다. 크기는 컬럼에 맞춘다.
FIRST_ROWS_n은 화면에 앞의 n건만 띄우고 멈추는 조회에서 쓸모가 있다(교재 09화). 끝까지 받는 리포트에 걸면 정렬을 피하려다 훨씬 많이 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황