어떤 날은 취소 탭이, 어떤 날은 완료 탭이 느리다

중급SELECT한빛유통547 / 700

상황

정산 상태 확인 화면은 탭(완료, 미결, 취소)마다 최근 1,000건을 보여 준다. 2019년부터 탭마다 상태값을 상수로 쓴 문장이 셋 있었는데, 지난주 개발팀이 「SQL 코딩 가이드 1조: 바인드로 넘긴다」를 보고 :st 바인드 하나로 합쳤다. 조민우가 말했다. "월요일엔 취소 탭이 한참 걸렸는데, 오늘은 취소는 괜찮고 완료 탭이 느려요." 공유 풀에 있는 이 문장의 자식 커서는 하나다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 900,000 · PENDING 80,000 · CANCEL 20,000 · FREQUENCY 히스토그램(10화 정책서 1호)
미결과 취소는 1,095일 중 219일(닷새에 하루)에만 있다. 그래서 최근 순으로 내려가며 취소 1,000건을 채우려면 약 5만 행을 지나야 한다.
인덱스 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ): 리프 약 3,000블록 · ORD_ST 인덱스는 없다
CURSOR_SHARING = EXACT · 바인드 피킹 켬 · statistics_level = TYPICAL(운영과 같다)

원본 SQL

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_ST = :st
         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000

실행계획 실측 · 교재 11화(줄별 값은 예시)

xplan

[V$SQL — 월요일 · 그날 첫 손님이 완료 탭 · 한 번 실행의 Buffers = BUFFER_GETS 증가분]
 순서  :st        CHILD  PLAN_HASH    BUFFERS
  1    COMPLETE      0   369784633      1,034
  2    CANCEL        0   369784633     50,347
  3    CANCEL        0   369784633     50,347
  4    CANCEL        0   369784633     50,347
  5    CANCEL        0   369784633     50,347

 CHILD  EXECUTIONS  IS_BIND_SENSITIVE  IS_BIND_AWARE  V$SQL_CS_HISTOGRAM (BUCKET:COUNT)
     0           5  Y                  N              0:0  1:5  2:0

[자식 0 의 계획 — 취소 탭 한 번 · FORMAT 'ALLSTATS LAST +PEEKED_BINDS']
Plan hash value: 369784633

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |   1000 |00:00:00.04 |   50347 |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |   1000 |00:00:00.04 |   50347 |
|   2 |   VIEW                         |                   |      1 |   1000 |   1000 |00:00:00.04 |   50347 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |    900K|   1000 |00:00:00.04 |   50347 |
|   4 |     INDEX FULL SCAN DESCENDING | IX_ORD_BIG_DT_SEQ |      1 |   1111 |  50191 |00:00:00.01 |     156 |
------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :ST (VARCHAR2(32), CSID=873): 'COMPLETE'

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1000)
   3 - filter("ORD_ST"=:ST)

[지난주까지 — 탭마다 상수 (문장 셋 · 각자의 계획)]
 완료  ORD_ST = 'COMPLETE'  INDEX FULL SCAN DESCENDING                  369784633   1,016
 미결  ORD_ST = 'PENDING'   TABLE ACCESS FULL + SORT ORDER BY STOPKEY   654798247   5,826
 취소  ORD_ST = 'CANCEL'    TABLE ACCESS FULL + SORT ORDER BY STOPKEY   654798247   5,826

문제

같은 문장이 날마다 다른 탭에서 느린 이유를 Peeked Binds와 V$SQL 표로 설명하시오. 적응적 커서 공유(ACS)가 이 문제를 고치지 못한 이유도 쓰시오.
어느 탭이 먼저 열리든 탭마다 제 계획을 타도록 고치시오. '바인드로 넘긴다'는 가이드와 어떻게 맞추는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

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_ST = 'COMPLETE'
         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000;

-- [미결 탭]
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 = 'PENDING'
         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000;

-- [취소 탭]
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;

개선 후 실행계획 실측 · 교재 11화(줄별 값은 예시)

xplan

[취소 탭 — ORD_ST = 'CANCEL' · 자기 문장 · 자기 계획]
Plan hash value: 654798247

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |   1000 |00:00:00.06 |    5826 |          |
|*  1 |  COUNT STOPKEY           |           |      1 |        |   1000 |00:00:00.06 |    5826 |          |
|   2 |   VIEW                   |           |      1 |  20000 |   1000 |00:00:00.06 |    5826 |          |
|*  3 |    SORT ORDER BY STOPKEY |           |      1 |  20000 |   1000 |00:00:00.06 |    5826 |  142K (0)|
|*  4 |     TABLE ACCESS FULL    | T_ORD_BIG |      1 |  20000 |  20000 |00:00:00.05 |    5826 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=1000)
   3 - filter(ROWNUM<=1000)
   4 - filter("ORD_ST"='CANCEL')

[완료 탭 — 'COMPLETE']  369784633 · INDEX FULL SCAN DESCENDING · Buffers 1,016 (어느 날이든)
[미결 탭 — 'PENDING']   654798247 · TABLE ACCESS FULL + SORT ORDER BY STOPKEY · Buffers 5,826

모범 답안 서술

자식 0은 월요일 첫 손님의 값 'COMPLETE'를 엿보고(바인드 피킹, Peeked Binds) 세운 계획이다. 완료는 열에 아홉이라 최근 순 인덱스 IX_ORD_BIG_DT_SEQ를 거꾸로 내려가면 1,000건이 금방 찬다(1,016). 취소 탭도 이 계획을 같이 쓴다. 취소는 50건에 한 건이라 4번 줄이 약 5만 행(A-Rows 50,191)을 내주고, 3번 줄은 그 행을 전부 테이블까지 가서 확인한 뒤 버린다. 그래서 취소 탭의 Buffers는 5,826 → 50,347이 된다. 반대로 첫 손님이 취소면 완료 탭이 FULL 계획을 타서 1,016 → 5,826이 된다. 날마다 느린 탭이 바뀌는 까닭이 여기에 있다. 히스토그램이 있어서 오라클은 이 문장의 계획이 값에 따라 갈린다는 것을 안다(IS_BIND_SENSITIVE = Y). 그러나 ACS는 실행의 일량을 세 칸으로 나눠 세는데, 1,034와 50,347이 모두 1번 칸에 들어가서(1:5) 두 실행을 다른 일로 보지 않았다. 그래서 IS_BIND_AWARE = N이고 자식은 끝까지 하나다. 1,000건 화면에서는 아무리 기다려도 ACS가 고쳐 주지 않는다. 상태값은 셋뿐이고 값마다 맞는 계획이 다르다. 이런 코드값은 탭마다 상수 리터럴로 문장을 따로 둔다. 문장이 셋이면 커서도 셋이라 하드 파싱은 세 번뿐이다. 화면 입력을 이어 붙이지 않고 코드가 문장을 고르므로 가이드 1조(화면이 고르는 값은 바인드)와 부딪히지 않는다. 이 경우는 2조(값마다 계획이 달라야 하는 코드값은 상수)에 해당한다. 취소 탭은 50,347 → 5,826(FULL + Top-N 정렬)으로 줄고, 완료 탭은 어느 날이든 1,016이다.

해설

/*+ BIND_AWARE */ 힌트를 쓰면 처음부터 값의 범위마다 자식을 나눈다(ACS의 '나쁜 한 번'을 건너뛴다). 바인드를 지켜야 할 때 쓸 수 있는 방법이지만, 어느 값이 어느 계획을 타는지가 문장 밖(V$SQL_CS_SELECTIVITY)으로 숨는다. 값이 셋인 코드라면 리터럴 쪽이 읽기 쉽다.
인덱스 (ORD_ST, ORD_DT, ORD_SEQ)를 만들면 세 값 모두 '범위로 1,000건을 읽고 멈추는' 계획이 맞게 되므로 바인드를 써도 안전하다(완료와 취소 모두 1,000블록 남짓). 대신 100만 행 테이블의 인덱스가 넷이 되고 재적재 비용이 는다(14화).
재는 도구가 결과를 바꾼다. statistics_level = ALL(또는 GATHER_PLAN_STATISTICS)로 재면 ACS가 세는 칸이 달라져서 문제가 고쳐지는 것처럼 보였다(11화). ACS는 운영과 같은 TYPICAL에서 V$SQL.BUFFER_GETS로 잰다.
같은 화면이 100건이었다면 ACS가 나쁜 실행 한 번(8,334) 뒤에 자식을 나눴다(5,826). 그 한 번도 누군가의 화면이다. PL/SQL이 커서를 열어 둔 채 다시 호출하면 그 한 번 뒤에도 나누지 못한다(파스 호출이 없기 때문이다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 11화(줄별 값은 예시) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 11화(줄별 값은 예시) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황