오후 2시만 넘으면 보류 판매 목록 조회가 Top SQL에 올라왔다가 다음 날 아침이면 사라진다

기초PLSQL유통566 / 700

상황

본사 레거시 유통의 매장 단말 6대는 「정산 보류 판매」 목록을 1분마다 새로 고친다(08:00 ~ 22:00, 하루 약 5,000번). 단말은 프로시저 PKG_SLE_HOLD.hold_list를 부르고, 넘기는 판매 구분은 늘 '90'(정산 보류)이다. 지난주 반품 정산 배치를 새로 연 뒤로 DBA가 이상한 모양을 보고했다. "오후 2시부터 이 조회가 Top SQL 1위로 올라오고, 다음 날 아침에는 아무 일 없었다는 듯 사라집니다. 배치가 2시에 같은 SQL을 한 번 부르기는 하지만 한 번뿐이고, 보류 목록 SQL은 아무도 손대지 않았어요."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15) · SLE_DT VARCHAR2(8) · SLE_HMS · SHP_NO · SLE_NO 순(날짜순)으로 적재
이 문제의 변형 컬럼 SLE_DV_CD VARCHAR2(2) 판매 구분: '10' 정상 488,600건 · '20' 반품 11,300건 · '90' 정산 보류 100건 · FREQUENCY 히스토그램 · 반품과 보류는 날짜와 상관없이 테이블 곳곳에 흩어져 있다
인덱스 IX_CF10_DV_CD (SLE_DV_CD): 높이 3 · 리프 약 1,000블록
통계는 매일 01:00에 모은다(no_invalidate => FALSE). 그래서 이 문장의 커서는 매일 밤 무효화되고, 그 뒤 첫 호출은 08:00 단말이다.
DB 문자셋 AL32UTF8 · NLS_LENGTH_SEMANTICS = BYTE · CURSOR_SHARING = EXACT · 바인드 피킹 켬

원본 SQL

sql

-- PKG_SLE_HOLD.hold_list (2018) : 매장 단말이 1분마다 부른다 (단말은 늘 '90'을 넘긴다)
PROCEDURE hold_list(p_cd IN VARCHAR2, rc OUT SYS_REFCURSOR) IS
  v_cd  VARCHAR2(2) := p_cd;
BEGIN
  OPEN rc FOR
    SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
      FROM CF10
     WHERE SLE_DV_CD = v_cd;
END;

-- PKG_RTN_STLM.run (지난주 신규) : 반품 정산 · 매일 14:00에 한 번 · SQL은 위 패키지에서 복사했다
PROCEDURE run IS
  v_cd  VARCHAR2(100) := '20';        -- 구분 코드가 길어질지 몰라 넉넉하게
BEGIN
  FOR r IN (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
              FROM CF10
             WHERE SLE_DV_CD = v_cd) LOOP
    ins_rtn_stlm(r.SLE_NO, r.SLE_DT, r.SHP_NO);
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위

xplan

[V$SQL · 16:00 · 텍스트 하나에 자식 둘]
 SQL_TEXT : SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO FROM CF10 WHERE SLE_DV_CD = :B1
 CHILD  IS_SHAREABLE  :B1 MAX_LENGTH  PLAN_HASH   EXECUTIONS  BUFFER_GETS/EXEC  FIRST_LOAD_TIME      처음 부른 곳
     0  N                         32  2704158391       2,160               103  2023-06-15/08:00:04  PKG_SLE_HOLD.hold_list
     1  Y                        128  1186043257         721             4,587  2023-06-15/14:00:02  PKG_RTN_STLM.run

[V$SQL_SHARED_CURSOR · Y인 칸]   child 1 : BIND_LENGTH_UPGRADEABLE = Y

[자식 0 · 단말 한 번 (08:00 ~ 13:59) · FORMAT 'ALLSTATS LAST +PEEKED_BINDS']
Plan hash value: 2704158391

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |    100 |00:00:00.01 |     103 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | CF10          |      1 |    100 |    100 |00:00:00.01 |     103 |
|*  2 |   INDEX RANGE SCAN                   | IX_CF10_DV_CD |      1 |    100 |    100 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by name):
----------------------------------
   1 - :B1 (VARCHAR2(32), CSID=873): '90'

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_DV_CD"=:B1)

[자식 1 · 단말 한 번 (14:00 이후) · FORMAT 'ALLSTATS LAST +PEEKED_BINDS']
Plan hash value: 1186043257

-----------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    100 |00:00:00.04 |    4587 |
|*  1 |  TABLE ACCESS FULL| CF10 |      1 |  11300 |    100 |00:00:00.04 |    4587 |
-----------------------------------------------------------------------------------

Peeked Binds (identified by name):
----------------------------------
   1 - :B1 (VARCHAR2(128), CSID=873): '20'

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SLE_DV_CD"=:B1)

[그제와 어제도 같다]  08:00 자식 0('90', 인덱스) → 14:00 자식 1('20', FULL) → 01:00 통계 수집으로 무효화

문제

오후 2시부터 보류 목록이 테이블 전체를 읽는 이유를 위 자료로 설명하시오. 다음 날 아침에는 다시 인덱스로 돌아오는 이유도 쓰시오.
시각과 상관없이 보류 목록이 인덱스를 타도록 두 프로시저를 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- PKG_SLE_HOLD.hold_list : 단말이 쓰는 값은 '90' 하나다 → 리터럴로 쓴다 (텍스트가 달라 배치와 부모 커서가 갈린다)
PROCEDURE hold_list(p_cd IN VARCHAR2, rc OUT SYS_REFCURSOR) IS
  v_cd  CF10.SLE_DV_CD%TYPE := p_cd;
BEGIN
  IF v_cd = '90' THEN
    OPEN rc FOR
      SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
        FROM CF10
       WHERE SLE_DV_CD = '90';
  ELSE
    -- 다른 구분은 거의 쓰지 않는다 : 바인드로 두되 그릇은 컬럼 크기(32바이트 구간)
    OPEN rc FOR
      SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
        FROM CF10
       WHERE SLE_DV_CD = v_cd;
  END IF;
END;

-- PKG_RTN_STLM.run : 변수는 컬럼에 묶는다 (그릇 32바이트 · 그릇 크기 때문에 자식이 늘지 않는다)
PROCEDURE run IS
  v_cd  CF10.SLE_DV_CD%TYPE := '20';
BEGIN
  FOR r IN (SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
              FROM CF10
             WHERE SLE_DV_CD = v_cd) LOOP
    ins_rtn_stlm(r.SLE_NO, r.SLE_DT, r.SHP_NO);
  END LOOP;
  COMMIT;
END;

개선 후 실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위

xplan

[보류 목록 문장 · SLE_DV_CD = '90' · 몇 시든 이 계획]
Plan hash value: 2704158391

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |    100 |00:00:00.01 |     103 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | CF10          |      1 |    100 |    100 |00:00:00.01 |     103 |
|*  2 |   INDEX RANGE SCAN                   | IX_CF10_DV_CD |      1 |    100 |    100 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_DV_CD"='90')

[다음 날 16:00 · V$SQL]
 ... WHERE SLE_DV_CD = '90'    자식 1개 · EXECUTIONS 2,880 · 실행당 103블록 (14:00 앞뒤가 같다)
 ... WHERE SLE_DV_CD = :B1     자식 1개 · 그릇 32 · 배치 1번 · FULL (반품 11,300건에 맞는 계획)

모범 답안 서술

자식 1은 14:00에 반품 정산 배치가 처음 부르면서 생겼다(BIND_LENGTH_UPGRADEABLE = Y). 배치는 v_cd를 VARCHAR2(100)으로 선언해서 바인드 그릇이 128바이트 구간이고, 아침에 단말(VARCHAR2(2), 32바이트)이 만든 자식 0의 그릇에는 들어가지 않는다. 그래서 오라클은 자식을 새로 만든다. 자식을 새로 만드는 것은 하드 파싱이고, 하드 파싱은 그 순간의 바인드 값을 엿본다(바인드 피킹). 그때 값이 배치의 '20'(반품 11,300건)이었으므로 테이블 전체를 읽는 계획이 섰다. 자식 1의 E-Rows 11,300과 Peeked Binds의 '20'이 그 흔적이다. 그릇이 작은 손님은 큰 그릇의 자식을 같이 쓸 수 있다. 그래서 자식 0은 IS_SHAREABLE = N이 되고, 14:00 뒤로는 단말의 호출도 자식 1을 쓴다. 단말은 여전히 '90'(100건)을 넘기지만 계획은 '20'을 위해 세운 FULL이라 한 번에 103 → 4,587블록을 읽는다. 단말 6대가 1분마다 부르니 시간당 약 3만 7천 블록이 약 165만 블록이 되고, 이것이 오후의 Top SQL이다. PARSE-019처럼 계획이 같으면 길이 때문에 생긴 자식은 하드 파싱과 메모리를 조금 더 쓰는 낭비로 끝난다. 그러나 분포가 치우친 컬럼에서는 길이 때문에 자식이 생기는 순간이 계획을 다시 정하는 순간이다. 다음 날 아침에 돌아오는 것은 01:00 통계 수집이 커서를 무효화하기 때문이다. 그 뒤 첫 호출은 08:00 단말('90', 32바이트)이라 다시 인덱스 계획의 자식이 서고, 14:00에 배치가 오면 같은 일이 되풀이된다. 단말이 넘기는 값은 언제나 '90'이다. 늘 같은 값은 바인드 변수일 필요가 없으므로 보류 목록 문장에 SLE_DV_CD = '90'을 리터럴로 쓴다. PL/SQL 정적 SQL에서도 리터럴은 리터럴로 남는다. 텍스트가 달라 배치와 부모 커서가 갈리고, 값이 하나라 커서도 하나이며, 계획은 시각과 상관없이 인덱스다. 오후 단말 한 번은 Buffers 4,587 → 103이 된다. 인자는 지키려고 다른 구분이 올 때 쓰는 바인드 문장을 ELSE 쪽에 남기고, 두 프로시저의 변수를 모두 CF10.SLE_DV_CD%TYPE으로 선언해 그릇 크기 때문에 자식이 늘지 않게 한다. 변수 크기만 맞추면 길이 때문에 생기는 자식은 사라지지만, 하나 남은 자식의 계획은 여전히 그날 처음 온 값이 정한다. 커서가 공유 풀에서 밀려난 직후 배치가 먼저 오면 그날 하루 단말이 FULL을 탄다. 그래서 값마다 맞는 계획이 다른 두 호출을 문장째 나누는 것이 근본 대책이다.

해설

증상이 매일 같은 시각에 시작되면 그 시각에 처음 도는 프로그램을 찾는다. V$SQL.FIRST_LOAD_TIME과 V$SQL_SHARED_CURSOR의 Y 칸은 "그 자식을 누가 언제 왜 만들었는가"를, Peeked Binds는 "누구의 값으로 세운 계획인가"를 알려 준다.
적응적 커서 공유(ACS)는 돕지 못한다. 자식 1에서는 배치도 단말도 FULL로 4,587블록 남짓을 읽어 일량이 같은 칸에 들어가므로, 값마다 자식을 나눌 근거가 쌓이지 않는다(교재 11화 §7).
바인드를 지키고 싶다면 보류 목록 문장에 /*+ BIND_AWARE */를 붙여 처음부터 선택도마다 자식을 나누게 할 수도 있다. 다만 어느 값이 어느 계획을 타는지가 문장 밖으로 숨고, 그릇 크기가 다르면 자식은 여전히 따로 생긴다.
바인드 그릇은 PL/SQL 변수 말고도 드라이버의 바인드 크기(ODP.NET의 Size, python-oracledb의 setinputsizes)로 정해진다. 그릇이 다른 프로그램이 같은 문장을 쓰면 언제든 이런 순간이 생길 수 있다(PARSE-029, PARSE-030).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 바인드 그릇 크기는 교재 11화 실측과 같은 단위 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황