튜닝 담당이 재면 920블록인데 운영 화면은 한 번에 5,826블록을 읽는다

중급SELECT한빛유통567 / 700

상황

정산 「기간 주문 목록」 화면은 시작일과 종료일을 받아 최근 순으로 앞의 100건만 보여 준다('더 보기'는 없다). 기본값은 어제 하루이고, 정산팀은 분기 단위로도 연다. 하루 6,000번쯤 호출된다. 조민우가 SQL*Plus에서 재현해 보았다. 날짜를 VAR(VARCHAR2)로 받아 TO_DATE로 바꿔 넣고 어제 하루로 돌리니 920블록이었다. "이 정도면 문제없는데요?" 그런데 DBA가 보낸 Top SQL 목록에는 이 문장이 실행당 5,826블록으로 올라와 있다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 하루 약 913건 · 행은 ORD_DT 순서와 상관없이 흩어져 있다
인덱스 IX_ORD_BIG_DT (ORD_DT) · IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)
화면(Java)은 날짜를 java.time.LocalDate로 넘긴다(DATE 바인드). 바인드 피킹 켬 · 히스토그램은 ORD_ST에만 있다 · statistics_level = TYPICAL

원본 SQL

sql

// PeriodOrderDao.java : 화면은 앞의 100건만 그린다 (setMaxRows)
String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG "
           + "WHERE ORD_DT >= ? AND ORD_DT < ? ORDER BY ORD_DT DESC, ORD_SEQ DESC";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, from);                 // LocalDate → DATE
ps.setObject(2, to.plusDays(1));
ps.setMaxRows(100);

-- 조민우의 재현 (SQL*Plus)
VAR d1 VARCHAR2(10)
VAR d2 VARCHAR2(10)
EXEC :d1 := '2024-06-16'; :d2 := '2024-06-17'
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:d1, 'YYYY-MM-DD') AND ORD_DT < TO_DATE(:d2, 'YYYY-MM-DD')
 ORDER BY ORD_DT DESC, ORD_SEQ DESC;

실행계획 학습용 예시

xplan

[조민우의 재현 — 텍스트가 다른 새 문장 · 그가 넣은 하루로 태어났다]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |    913 |00:00:00.01 |     920 |          |
|   1 |  SORT ORDER BY                       |               |      1 |    913 |    913 |00:00:00.01 |     920 |80896  (0)|
|*  2 |   FILTER                             |               |      1 |        |    913 |00:00:00.01 |     920 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG    |      1 |    913 |    913 |00:00:00.01 |     920 |          |
|*  4 |     INDEX RANGE SCAN                 | IX_ORD_BIG_DT |      1 |    913 |    913 |00:00:00.01 |       5 |          |
---------------------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:D2,'YYYY-MM-DD')>TO_DATE(:D1,'YYYY-MM-DD'))
   4 - access("ORD_DT">=TO_DATE(:D1,'YYYY-MM-DD') AND "ORD_DT"<TO_DATE(:D2,'YYYY-MM-DD'))

[운영 커서 — V$SQL · 08:31에 태어났다]
 CHILD  PLAN_HASH    EXECUTIONS  BUFFER_GETS/EXEC  IS_BIND_SENSITIVE  IS_BIND_AWARE  V$SQL_CS_HISTOGRAM
     0  3305918526        4,388             5,826  Y                  N              0:0  1:4,388  2:0

[운영 커서 · 같은 텍스트와 DATE 바인드로 재현(분기 → 하루 순서) · 'ALLSTATS LAST +PEEKED_BINDS' · 하루 실행]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |    913 |00:00:00.21 |    5826 |          |
|   1 |  SORT ORDER BY                       |               |      1 |  83103 |    913 |00:00:00.21 |    5826 |80896  (0)|
|*  2 |   FILTER                             |               |      1 |        |    913 |00:00:00.21 |    5826 |          |
|*  3 |    TABLE ACCESS FULL                 | T_ORD_BIG     |      1 |  83103 |    913 |00:00:00.20 |    5826 |          |
---------------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (DATE): 04/01/2024 00:00:00
   2 - :2 (DATE): 07/01/2024 00:00:00

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:2>:1)
   3 - filter(("ORD_DT">=:1 AND "ORD_DT"<:2))

문제

조민우의 재현과 운영 화면의 일량이 다른 이유를 설명하시오. 운영의 느린 실행을 보려면 무엇을 보고, 재현할 때는 무엇을 운영과 같게 맞춰야 하는지 쓰시오.
하루를 넘기든 분기를 넘기든, 어느 값이 먼저 파싱되든 같은 계획이 맞도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

// 화면은 100건만 그린다 → 문장도 100건에서 멈춘다 (정렬 키와 같은 IX_ORD_BIG_DT_SEQ 를 거꾸로)
String sql = "SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM ("
           + " SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG"
           + " WHERE ORD_DT >= ? AND ORD_DT < ?"
           + " ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 100";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, from);
ps.setObject(2, to.plusDays(1));

-- 운영 커서를 보는 법 : 새 문장을 만들지 않고 운영 자식을 연다 (엿본 값 · 실제 바인드 타입)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'TYPICAL +PEEKED_BINDS'));
SELECT child_number, name, datatype_string, value_string
  FROM v$sql_bind_capture WHERE sql_id = '&sql_id';

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

xplan

[고친 문장 — 하루든 분기든, 어느 값으로 태어나든 같은 계획]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |    100 |00:00:00.01 |     104 |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |    100 |00:00:00.01 |     104 |
|   2 |   VIEW                         |                   |      1 |    100 |    100 |00:00:00.01 |     104 |
|*  3 |    FILTER                      |                   |      1 |        |    100 |00:00:00.01 |     104 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |      1 |  83103 |    100 |00:00:00.01 |     104 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_DT_SEQ |      1 |    100 |    100 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=100)
   3 - filter(:2>:1)
   5 - access("ORD_DT"<:2 AND "ORD_DT">=:1)

[하루로 태어난 날의 하루 실행]  같은 계획 · Buffers 104 (예전 IX_ORD_BIG_DT + 정렬 920)

모범 답안 서술

조민우가 잰 것은 화면의 커서가 아니다. 그의 문장은 TO_DATE(:d1, 'YYYY-MM-DD')가 들어가 텍스트가 다르고, 바인드도 VARCHAR2라서 새 부모 커서로 하드 파싱되었다. 그 순간 그가 넣은 하루를 엿봤으므로 하루에 맞는 계획(IX_ORD_BIG_DT 범위 + 테이블 + 정렬, 920블록)이 섰다. 화면의 운영 커서는 08:31에 정산팀이 분기(:1 = 2024-04-01, :2 = 2024-07-01)를 넘겨 처음 만들었다(Peeked Binds). 분기면 83,103건이라 흩어진 행을 인덱스로 한 건씩 찾아가는 것보다 FULL이 싸다. 그 뒤로 하루를 넘기는 화면도 같은 커서를 써서, 913건을 위해 5,826블록을 읽고 정렬한다. 하루와 분기 모두 FULL이라 일량이 ACS의 같은 칸(1:4,388)에 들어가고 자식도 갈라지지 않는다. 운영의 느린 실행은 새 문장으로 재지 않고 운영 커서를 sql_id로 열어 본다. DISPLAY_CURSOR(… '+PEEKED_BINDS')로 엿본 값을, V$SQL_BIND_CAPTURE로 실제 바인드 타입(DATE)을 확인한다. 직접 재현하려면 텍스트를 한 글자도 바꾸지 않고, 바인드 타입을 운영과 같게 맞추고(SQL*Plus의 VAR에는 DATE가 없으므로 PL/SQL 블록의 DATE 변수로 넘긴다), 운영과 같은 순서(분기를 먼저, 하루를 다음)로 돌린다. 고치는 길은 어느 값을 엿보든 같은 계획이 맞는 모양으로 문장을 바꾸는 것이다. 화면은 앞의 100건만 그리므로 ROWNUM <= 100의 Top-N으로 쓰고, 정렬을 IX_ORD_BIG_DT_SEQ와 같은 ORD_DT DESC, ORD_SEQ DESC로 둔다. 그러면 기간이 하루든 분기든 인덱스를 거꾸로 읽다가 100건에서 멈춘다(COUNT STOPKEY). 화면 한 번은 Buffers 5,826 → 104이고, 하루로 태어난 날에도 920 → 104로 준다.

해설

setMaxRows(100)은 클라이언트가 100행만 받고 그만두게 할 뿐이다. 옵티마이저는 몇 건이면 되는지 모르므로 기간 전체를 읽고 정렬하는 계획을 세운다. 필요한 건수는 문장에 써야 옵티마이저가 안다.
문장을 하루용과 기간용으로 나누는 방법(PARSE-020)도 있다. 하지만 이 화면은 기간이 길어도 보여 주는 것은 100건이라, Top-N 하나로 두 경우를 함께 해결할 수 있다.
교재 11화의 SQL 코딩 가이드 3조는 바인드 문장을 "엿본 값이 무엇이든, 엿보지 못해도 같은 계획이 나오는 모양"으로 쓰라고 한다. 이 문장이 그 예다.
재현할 때 statistics_level = ALL이나 GATHER_PLAN_STATISTICS를 켜면 ACS가 세는 일량이 달라질 수 있다(교재 11화의 함정). 운영과 같은 조건에서는 V$SQL.BUFFER_GETS의 증가분으로 잰다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황