접속 풀을 화면과 같이 쓰게 한 뒤로 연간 주문 추출 SQL이 읽은 블록 수 1위에 올랐다

기초SELECT한빛유통623 / 700

상황

정산팀의 「연간 주문 추출」 배치는 매년 1월과 감사 요청이 올 때 한 해 치 주문을 파일로 내린다. 지난달 조민우가 배치 서버의 데이터소스 설정을 정리하면서 배치도 화면 서버와 같은 접속 풀(HANORDER_WEB)을 쓰게 했다. 그 뒤로 AWR의 「SQL ordered by Gets」 1위에 이 추출 SQL이 올라왔다. SQL은 한 글자도 바뀌지 않았다. 접속 풀 설정에는 2년 전에 넣은 초기화 SQL이 한 줄 있다. 목록 화면들이 첫 10행을 빨리 띄우게 하려고 넣은 줄이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정) · 2023년 한 해 333,245행 · 통계 최신(ORD_DT NDV 1,095)
인덱스 IX_ORD_BIG_DT (ORD_DT): BLEVEL 2 · 리프 2,653블록 · 클러스터링 팩터 1,000,000(같은 날짜의 주문이 테이블 전체에 흩어져 있다)
인스턴스의 OPTIMIZER_MODE는 기본값 ALL_ROWS다. 배치는 추출한 행을 끝까지 받아 파일로 쓴다(fetch size 500).
접속 풀 HANORDER_WEB의 초기화 SQL: ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to
 ORDER BY ORD_DT

실행계획 학습용 예시 · 교재 04화 규모

xplan

[배치 세션 — :v_from = 2023-01-01, :v_to = 2024-01-01 · 끝까지 받은 뒤]
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |      1 |        |    333K|00:00:02.71 |     334K|
|*  1 |  FILTER                      |               |      1 |        |    333K|00:00:02.71 |     334K|
|   2 |   TABLE ACCESS BY INDEX ROWID| T_ORD_BIG     |      1 |    335K|    333K|00:00:02.58 |     334K|
|*  3 |    INDEX RANGE SCAN          | IX_ORD_BIG_DT |      1 |     10 |    333K|00:00:00.19 |     887 |
----------------------------------------------------------------------------------------------------

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

Outline Data (+OUTLINE 에서 필요한 줄만)
-------------
      FIRST_ROWS(10)
      INDEX_RS_ASC(@"SEL$1" "T_ORD_BIG"@"SEL$1" ("T_ORD_BIG"."ORD_DT"))

[V$SES_OPTIMIZER_ENV — 배치 세션]
 NAME              VALUE           ISDEFAULT
 optimizer_mode    first_rows_10   NO

(334K = 334,132 · 지난해 배치 서버 전용 풀에서 돈 같은 SQL : TABLE ACCESS FULL + SORT ORDER BY · 5,826블록)

문제

SQL이 그대로인데 계획이 바뀐 이유를 실행계획과 세션 정보의 어느 줄에서 알 수 있는지 쓰시오.
옵티마이저가 이 계획을 고른 이유를 최적화 목표로 설명하고, 화면은 그대로 두면서 추출만 바로잡으시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 배치 세션에서만 최적화 목표를 되돌린다 (접속 직후 한 번 · 화면 풀의 초기화 SQL 은 그대로)
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to
 ORDER BY ORD_DT;

-- ② 근본적으로는 배치 서버에 초기화 SQL 이 없는 전용 풀을 다시 둔다
-- ③ 풀을 못 나누면 문장에만 : SELECT /*+ ALL_ROWS */ ORD_SEQ, ... (힌트 대장에 이유를 적는다)

개선 후 실행계획 학습용 예시 · 교재 04화 규모

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |    333K|00:00:00.62 |    5826 |          |
|   1 |  SORT ORDER BY      |           |      1 |    335K|    333K|00:00:00.62 |    5826 |   21M (0)|
|*  2 |   FILTER            |           |      1 |        |    333K|00:00:00.11 |    5826 |          |
|*  3 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    335K|    333K|00:00:00.09 |    5826 |          |
----------------------------------------------------------------------------------------------------------------

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

모범 답안 서술

SQL에는 힌트가 없는데 Outline Data에 FIRST_ROWS(10)이 있고, V$SES_OPTIMIZER_ENV에서 배치 세션의 optimizer_mode가 first_rows_10(ISDEFAULT NO)이다. 배치가 화면용 접속 풀로 옮겨 오면서 풀의 초기화 SQL ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10이 배치 세션에도 걸린 것이다. 같은 SQL이라도 세션의 최적화 목표가 다르면 다른 자식 커서가 생기고 계획도 따로 세운다. FIRST_ROWS_10은 "앞의 10행을 가장 빨리 내는 계획"을 고르라는 뜻이다. 그 목표로 보면 333K 행을 다 읽고 정렬해야 첫 행이 나오는 FULL + SORT보다, 이미 ORD_DT 순서로 줄 서 있는 인덱스를 따라가 정렬을 피하는 경로가 훨씬 싸다(3번 줄 E-Rows 10이 그 계산이다). 화면이라면 옳은 판단이다. 하지만 배치는 33만 행을 끝까지 받는다. 클러스터링 팩터가 1,000,000이라 인덱스 순서대로 가면 행마다 다른 테이블 블록을 찾아가므로, 테이블 랜덤 액세스만 333,245번이고 Buffers는 334K(334,132)다. 테이블을 한 번 통째로 읽고 정렬하면 5,826블록이면 된다. 그래서 배치 세션에서만 ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS로 목표를 되돌린다. 계획은 TABLE ACCESS FULL + SORT ORDER BY가 되고 Buffers는 334K → 5,826, 시간은 2.71초 → 0.62초로 준다. 화면 풀의 초기화 SQL은 그대로 두어 목록 화면에는 영향이 없게 한다. 길게 보면 배치용 데이터소스를 화면과 나누어 전용 풀로 되돌리는 것이 맞다.

해설

다른 정답: 풀을 나눌 수 없으면 추출 SQL에만 /*+ ALL_ROWS */ 힌트를 넣어도 된다. 대신 그 힌트가 왜 있는지(화면 풀의 FIRST_ROWS_10을 이기려고)를 힌트 대장에 적어 둔다(06화).
함정: 정렬 메모리(21M)를 아끼려고 인덱스 경로를 지키려는 생각은 틀렸다. 정렬은 한 번이지만 테이블 랜덤 액세스는 33만 번이다. 기간이 길수록 차이가 커진다(04화 곡선표: 1년이면 인덱스 강제 334,132 대 FULL 5,826).
화면과 배치가 같은 SQL을 쓰면 V$SQL_SHARED_CURSOR에 OPTIMIZER_MODE_MISMATCH로 자식 커서가 갈라진다(11화 §5). 계획이 둘이라는 사실을 거기서 먼저 확인할 수 있다.
인스턴스 전체를 FIRST_ROWS_10으로 바꾸는 안은 반대 방향으로 같은 일을 만든다. 최적화 목표는 화면과 배치처럼 결과를 받는 방식이 다른 곳마다 세션 단위로 정한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 04화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 04화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황