인덱스를 더 잘 타게 하는 설정이 들어간 다음 날부터 월 상태별 매출 집계가 블록을 다섯 배 읽는다

기초SELECT한빛유통624 / 700

상황

지난주 화요일, 고객 화면에서 "인덱스가 있는데 안 탄다"는 민원이 들어오자 오과장은 운영에 설정 한 줄을 넣었다. 민원 화면은 빨라졌다고 한다. 수요일부터 정산팀의 「월 상태별 매출」 리포트(하루 약 300번)가 읽는 블록이 다섯 배 가까이 늘었다. 리포트 SQL과 인덱스, 통계는 지난주와 같다. 아래는 6월 한 달을 넘겼을 때의 계획이다. 비교하려고 같은 SQL에 FULL 힌트를 넣어 한 번 더 떴다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT NDV 1,095(2022-01-01 ~ 2024-12-30) · 6월 27,390행 · ORD_ST 3종
인덱스 IX_ORD_BIG_DT (ORD_DT): BLEVEL 2 · 리프 2,653블록 · 클러스터링 팩터 1,000,000
시스템 통계는 모은 적이 없다(기본값). 이 테이블을 FULL로 읽는 비용은 1,612(IO 1,606)다.
경고 로그(alert log): ALTER SYSTEM SET optimizer_index_cost_adj=5 SCOPE=BOTH; (지난주 화요일 17:42)

원본 SQL

sql

SELECT ORD_ST, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to
 GROUP BY ORD_ST

실행계획 학습용 예시 · 교재 04화 · 10화의 비용 숫자

xplan

[지금 — 6월 한 달]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |  1426 (100)|      3 |00:00:00.21 |   27466 |
|   1 |  HASH GROUP BY                       |               |      1 |      3 |  1426   (1)|      3 |00:00:00.21 |   27466 |
|*  2 |   FILTER                             |               |      1 |        |            |  27390 |00:00:00.19 |   27466 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG    |      1 |  28336 |  1421   (0)|  27390 |00:00:00.18 |   27466 |
|*  4 |     INDEX RANGE SCAN                 | IX_ORD_BIG_DT |      1 |  28336 |     4   (0)|  27390 |00:00:00.01 |      76 |
------------------------------------------------------------------------------------------------------------------------

[같은 SQL + /*+ FULL(T_ORD_BIG) */ — 비교용]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |  1616 (100)|      3 |00:00:00.09 |    5826 |
|   1 |  HASH GROUP BY                       |               |      1 |      3 |  1616   (1)|      3 |00:00:00.09 |    5826 |
|*  2 |   FILTER                             |               |      1 |        |            |  27390 |00:00:00.08 |    5826 |
|*  3 |    TABLE ACCESS FULL                 | T_ORD_BIG     |      1 |  28336 |  1612   (1)|  27390 |00:00:00.08 |    5826 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   4 - access("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO)       (위 계획)
   3 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))     (아래 계획)

[V$PARAMETER]
 NAME                       VALUE   ISMODIFIED
 optimizer_index_cost_adj   5       SYSTEM_MOD

문제

인덱스 경로의 Cost 1,421이 어떻게 나왔는지 기본값(100)일 때의 비용과 함께 손으로 계산하고, 옵티마이저가 FULL(1,612)을 버린 이유를 쓰시오.
설정을 바로잡는 SQL을 쓰고, 되돌리기 전에 확인할 것과 민원 화면은 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 되돌리기 전에 : 내 세션에서만 기본값으로 놓고, 지난주부터 계획이 바뀐 SQL 과 민원 화면 SQL 을 견준다
ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 100;

-- ② 운영 되돌리기 (메모리와 spfile 둘 다)
ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 100 SCOPE = BOTH;
-- spfile 에서 줄 자체를 지우려면 : ALTER SYSTEM RESET OPTIMIZER_INDEX_COST_ADJ SCOPE = SPFILE;

-- ③ 리포트 SQL 은 그대로
SELECT ORD_ST, COUNT(*) AS cnt, SUM(ORD_AMT) AS amt
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to
 GROUP BY ORD_ST;

개선 후 실행계획 학습용 예시 · 교재 04화 · 10화의 비용 숫자

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |      1 |        |  1616 (100)|      3 |00:00:00.09 |    5826 |
|   1 |  HASH GROUP BY                       |               |      1 |      3 |  1616   (1)|      3 |00:00:00.09 |    5826 |
|*  2 |   FILTER                             |               |      1 |        |            |  27390 |00:00:00.08 |    5826 |
|*  3 |    TABLE ACCESS FULL                 | T_ORD_BIG     |      1 |  28336 |  1612   (1)|  27390 |00:00:00.08 |    5826 |
------------------------------------------------------------------------------------------------------------------------

모범 답안 서술

옵티마이저가 믿는 선택도는 28,336 / 1,000,000 = 0.028336이다. 기본값(100)이라면 인덱스 경로 비용은 BLEVEL 2 + 리프 2,653 × 0.028336(약 75) + 클러스터링 팩터 1,000,000 × 0.028336(약 28,336) = 약 28,413이다. FULL 1,612보다 훨씬 비싸므로 원래는 FULL을 골랐다. OPTIMIZER_INDEX_COST_ADJ = 5는 인덱스로 가는 경로의 비용을 5%로 치라는 뜻이다. 28,413 × 0.05 ≒ 1,421이 되어 FULL 1,612보다 싸 보였고, 옵티마이저는 비용대로 인덱스를 골랐다. 하지만 비용을 깎았을 뿐 실제 일은 그대로다. 클러스터링 팩터가 100만이라 27,390행을 행마다 다른 블록에서 찾아가므로 Buffers는 27,466이고, FULL이면 5,826이다. 하루 300번이면 약 824만 → 175만 블록이다. 되돌리는 SQL은 ALTER SYSTEM SET OPTIMIZER_INDEX_COST_ADJ = 100 SCOPE = BOTH다. 이 파라미터는 시스템 전체 값이라 지난주부터 모든 SQL의 인덱스 비용이 함께 바뀌었다. 그러니 되돌리기 전에 ALTER SESSION으로 내 세션만 기본값으로 놓고, 지난주 이후 계획이 바뀐 SQL과 민원 화면 SQL을 견주어 본다. 되돌린 뒤 리포트는 FULL로 돌아가 Buffers 27,466 → 5,826이 된다. 민원 화면은 그 SQL의 원인을 따로 찾아 고친다. "인덱스가 있는데 안 탄다"의 흔한 원인은 컬럼 가공, 묵시적 형변환, 틀린 통계다. 원인은 그대로 두고 비용 계산만 바꾸면 그 화면은 빨라져도 다른 SQL이 대가를 치른다.

해설

함정: 실행계획만 보면 "인덱스를 탔으니 좋아졌다"고 읽기 쉽다. Cost는 1,616 → 1,426으로 줄었지만 Buffers는 5,826 → 27,466으로 늘었다. 판단은 Buffers로 한다(04화 §5).
이 파라미터가 있던 시절: 시스템 통계(CPU 비용 모델)가 없던 때에는 싱글블록 읽기와 멀티블록 읽기의 차이를 이 값으로 맞추기도 했다. 지금은 기본값 100이 출발점이고, 바꾸려면 근거와 영향 범위를 함께 적는다.
다른 답: 리포트에 (ORD_DT, ORD_ST, ORD_AMT) 커버링 인덱스를 두면 한 달에 약 100블록으로 준다. 그래도 파라미터는 되돌려야 한다. 같은 왜곡이 다른 SQL에 그대로 남는다.
OPTIMIZER_INDEX_CACHING도 같은 부류다(NL 안쪽 인덱스를 캐시에 있다고 본다). 세션에서 이런 값을 바꾸는 배치는 OPT-036에서 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 04화 · 10화의 비용 숫자 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 04화 · 10화의 비용 숫자 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황