새 커버링 인덱스를 다른 화면은 다 쓰는데 월 영업 매장 수 위젯만 옛 계획 그대로다

중급SELECT버거629 / 700

상황

본사 대시보드의 「월 영업 매장 수」 위젯은 하루 2,000번쯤 호출된다. 지난주 다른 화면을 위해 ORD_X04 (ORD_DT, SHOP_NO)를 만들자 같은 조건의 조회 화면들은 모두 그 인덱스로 바뀌었는데, 이 위젯만 그대로다. 계획의 Note에 낯선 줄이 하나 있다. 작업 기록을 뒤지자 작년 1월 오과장의 메모가 나왔다. "해가 바뀌는 기간(12/16 ~ 1/15)을 고르면 E-Rows가 수십 배로 뛰어 FULL(22,422블록)로 바뀜. 문자 날짜 탓. ORD_X01 계획의 Outline으로 프로파일을 만들어 고정함."

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 한 달 약 41,700건 · 매장 200곳 · 이 테이블을 FULL로 읽는 비용은 약 6,083
ORD_X01 (ORD_DT, ORD_HMS): 리프 3,774 · 클러스터링 팩터 22,223(날짜순 적재 · 작년부터 있다)
ORD_X04 (ORD_DT, SHOP_NO): 지난주 생성 · BLEVEL 2 · 리프 3,050블록
SQL 프로파일 PROF_WIDGET_SHOPS: 작년 1월 DBMS_SQLTUNE.IMPORT_SQL_PROFILE로 만들었다 · CATEGORY DEFAULT · FORCE_MATCHING YES · Tuning Pack 계약 있음

원본 SQL

sql

SELECT COUNT(DISTINCT SHOP_NO) AS shop_cnt
  FROM ORD
 WHERE ORD_DT BETWEEN :v_from AND :v_to

실행계획 학습용 예시 · 교재 04화의 문자 날짜 E-Rows

xplan

[지금 — 2013년 11월]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |          |      1 |        |      1 |00:00:00.04 |    1071 |
|   1 |  SORT AGGREGATE                       |          |      1 |      1 |      1 |00:00:00.04 |    1071 |
|   2 |   VIEW                                | VW_DAG_0 |      1 |    200 |    200 |00:00:00.04 |    1071 |
|   3 |    HASH GROUP BY                      |          |      1 |    200 |    200 |00:00:00.04 |    1071 |
|*  4 |     FILTER                            |          |      1 |        |  41712 |00:00:00.03 |    1071 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| ORD    |      1 |   4065 |  41712 |00:00:00.03 |    1071 |
|*  6 |       INDEX RANGE SCAN                | ORD_X01  |      1 |   4065 |  41712 |00:00:00.01 |     139 |
-------------------------------------------------------------------------------------------------------------

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

Note
-----
   - SQL profile PROF_WIDGET_SHOPS used for this statement

[프로파일 안의 힌트 — DBA_SQL_PROFILES 의 SIGNATURE 로 꺼냄]
   BEGIN_OUTLINE_DATA
   IGNORE_OPTIM_EMBEDDED_HINTS
   ALL_ROWS
   OUTLINE_LEAF(@"SEL$5771D262")
   INDEX_RS_ASC(@"SEL$5771D262" "ORD"@"SEL$1" ("ORD"."ORD_DT" "ORD"."ORD_HMS"))
   BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5771D262" "ORD"@"SEL$1")
   USE_HASH_AGGREGATION(@"SEL$5771D262")
   END_OUTLINE_DATA

[같은 조건을 쓰는 「월 매장별 주문 수」 화면 — 지난주부터]  INDEX RANGE SCAN ORD_X04 · Buffers 129

문제

새 인덱스가 있는데도 이 위젯만 옛 계획으로 도는 이유를 Note와 프로파일 안의 힌트로 설명하시오. 이런 프로파일이 SQL plan baseline과 다른 점도 쓰시오.
운영 화면에 영향을 주지 않고 프로파일 없는 계획을 먼저 확인한 뒤 바로잡으시오. 작년에 프로파일을 만든 이유가 지금도 남아 있는지 판단하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 내 세션에서만 프로파일이 없는 카테고리로 바꿔, 프로파일 없는 계획을 본다 (운영 화면은 그대로 DEFAULT)
ALTER SESSION SET SQLTUNE_CATEGORY = 'NO_PROFILE_TEST';

SELECT COUNT(DISTINCT SHOP_NO) AS shop_cnt
  FROM ORD
 WHERE ORD_DT BETWEEN :v_from AND :v_to;
-- 11월 · 12/16 ~ 1/15 · 2년 전체로 각각 : 모두 ORD_X04 (129 ~ 3,052블록)

ALTER SESSION SET SQLTUNE_CATEGORY = 'DEFAULT';

-- ② 확인했으면 프로파일을 끈다 (한 주 지켜본 뒤 DROP · 되돌릴 때는 ENABLED)
BEGIN
  DBMS_SQLTUNE.ALTER_SQL_PROFILE(name           => 'PROF_WIDGET_SHOPS',
                                 attribute_name => 'STATUS',
                                 value          => 'DISABLED');
END;
/

개선 후 실행계획 학습용 예시 · 교재 04화의 문자 날짜 E-Rows

xplan

[프로파일을 끈 뒤 — 2013년 11월]
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      1 |00:00:00.02 |     129 |
|   1 |  SORT AGGREGATE       |          |      1 |      1 |      1 |00:00:00.02 |     129 |
|   2 |   VIEW                | VW_DAG_0 |      1 |    200 |    200 |00:00:00.02 |     129 |
|   3 |    HASH GROUP BY      |          |      1 |    200 |    200 |00:00:00.02 |     129 |
|*  4 |     FILTER            |          |      1 |        |  41712 |00:00:00.01 |     129 |
|*  5 |      INDEX RANGE SCAN | ORD_X04  |      1 |   4065 |  41712 |00:00:00.01 |     129 |
-----------------------------------------------------------------------------------------------

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

모범 답안 서술

계획의 Note에 SQL profile PROF_WIDGET_SHOPS used가 있고, 프로파일 안에는 INDEX_RS_ASC(... ("ORD"."ORD_DT" "ORD"."ORD_HMS"))가 든 Outline 힌트 묶음이 통째로 들어 있다. 작년 1월에 ORD_X01 계획을 고정하려고 만든 프로파일이다. 이 프로파일은 추정을 보정하는 것이 아니라 계획을 못 박으므로, 지난주 ORD_X04가 생겨도 옵티마이저는 ORD_X01과 테이블 액세스만 고를 수 있다. 같은 조건의 다른 화면은 프로파일이 없어서 새 인덱스로 바뀌었다. SQL plan baseline이었다면 달랐다. 베이스라인은 옵티마이저가 새 계획을 만들면 대기 줄(ACCEPTED NO)에 두고, 진화로 시험해 더 나을 때 받아들인다. Outline 프로파일은 그런 검사 없이 계획을 그대로 붙인다. 새 계획이 생겼다는 기록도 남지 않는다. 바로잡기 전에 운영 화면에 영향을 주지 않고 확인한다. 프로파일은 카테고리에 속하므로, 내 세션에서만 ALTER SESSION SET SQLTUNE_CATEGORY = 'NO_PROFILE_TEST'(프로파일이 없는 카테고리)로 바꾸고 같은 SQL을 11월, 해가 바뀌는 12/16 ~ 1/15, 2년 전체로 돌려 본다. 모두 ORD_X04로 처리된다. 그다음 ALTER_SQL_PROFILE로 STATUS를 DISABLED로 바꾸고 한 주 지켜본 뒤 지운다. 한 달 범위의 Buffers는 1,071 → 129이고, 하루 2,000번이면 약 214만 → 26만 블록이다. 작년에 프로파일을 만든 이유는 지금은 없다. 그때의 흔들림은 문자 날짜의 수직선 탓에 해가 바뀌는 기간의 E-Rows가 수십 배로 뛰면, 테이블까지 가는 ORD_X01 경로가 FULL보다 비싸 보여 생긴 것이었다. ORD_X04는 SHOP_NO까지 담은 커버링 인덱스라 테이블에 가지 않는다. 2년 전체를 읽어도 리프 3,050블록 남짓으로 FULL(비용 약 6,083, 22,422블록)보다 싸므로, E-Rows가 얼마로 나오든 같은 인덱스를 고른다. 해가 바뀌는 기간도 계획을 흔들지 못한다.

해설

인덱스를 만들 때의 점검표: 새 인덱스를 만들거나 지우면 그 테이블을 쓰는 SQL의 프로파일(DBA_SQL_PROFILES)과 베이스라인(DBA_SQL_PLAN_BASELINES)부터 확인한다. 프로파일과 베이스라인이 있는 SQL은 인덱스가 생겨도 그대로다.
프로파일의 두 얼굴: 어드바이저가 만든 프로파일은 대개 OPT_ESTIMATE 같은 보정이고, IMPORT_SQL_PROFILE로 만든 프로파일은 Outline 전체일 때가 많다. 앞의 것은 통계가 바뀌면 보정이 어긋나고, 뒤의 것은 이번처럼 좋은 새 계획을 막는다. 어느 쪽인지 안의 힌트를 꺼내 봐야 안다.
다른 답: 작년의 흔들림만 막으려면 그때도 베이스라인이 나았다. ORD_X01 계획을 받아들인 계획으로 두면 새 인덱스 계획이 대기 줄에 쌓여, 진화로 시험해 받아들일 수 있었다(18화 장면 A).
E-Rows 4,065(실제 41,712)는 그대로 남는다. 커버링 인덱스가 계획을 그 숫자에 둔감하게 만들었을 뿐 숫자를 고친 것은 아니다(04화 §5, 10화 §12).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 04화의 문자 날짜 E-Rows ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 04화의 문자 날짜 E-Rows ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황