인덱스 정리를 시작한 날 오후, 계획을 고정해 두었던 매장 일 판매 화면이 판매 테이블을 통째로 읽는다

중급DDL유통636 / 700

상황

오과장이 인덱스 정리를 시작했다. 04화의 절차대로 지울 후보를 먼저 INVISIBLE로 바꿔 두고 일주일 지켜볼 생각이었다. 14:10에 첫 후보 IX_CF10_DT_SHP를 INVISIBLE로 바꿨다. "SLE_NO가 날짜로 시작하니까 PK로도 충분할 거예요." 14:30부터 점장 화면 「오늘 매장 판매」(하루 약 600번)가 느려졌다. 이 화면 SQL에는 작년 9월에 베이스라인을 올려 두었다. 조민우가 물었다. "베이스라인이 있는데 계획이 왜 바뀌어요?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 하루 약 700건 · 매장 6곳
CF10의 인덱스: CF10_PK (SLE_NO) · IX_CF10_DT_SHP (SLE_DT, SHP_NO)(오늘 14:10 INVISIBLE) · 다른 인덱스 없음
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO)
베이스라인: 이 SQL에 받아들인 계획 하나(MANUAL-LOAD-FROM-CURSOR-CACHE · 작년 9월 · IX_CF10_DT_SHP → CF11_PK의 NL · 389블록)
OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE(기본값)

원본 SQL

sql

-- 오과장 14:10
ALTER INDEX IX_CF10_DT_SHP INVISIBLE;

-- 점장 화면 「오늘 매장 판매」
SELECT a.SLE_NO, a.SLE_HMS, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :dt
   AND a.SHP_NO = :shp
   AND b.SLE_NO = a.SLE_NO

실행계획 학습용 예시 · OPT-005와 같은 규모

xplan

[14:30 이후]
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |    256 |00:00:00.12 |    4961 |
|   1 |  NESTED LOOPS                |         |      1 |    250 |    256 |00:00:00.12 |    4961 |
|   2 |   NESTED LOOPS               |         |      1 |    250 |    256 |00:00:00.12 |    4821 |
|*  3 |    TABLE ACCESS FULL         | CF10    |      1 |    114 |    117 |00:00:00.11 |    4583 |
|*  4 |    INDEX RANGE SCAN          | CF11_PK |    117 |      2 |    256 |00:00:00.01 |     238 |
|   5 |   TABLE ACCESS BY INDEX ROWID| CF11    |    256 |      2 |    256 |00:00:00.01 |     140 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("A"."SLE_DT"=:DT AND "A"."SHP_NO"=:SHP))
   4 - access("B"."SLE_NO"="A"."SLE_NO")

(Note 절 없음 · 14:10 이전에는 "SQL plan baseline SQL_PLAN_8u2vbm4c0q7xa5d91e3f0 used" · 389블록)

[DBA_SQL_PLAN_BASELINES — 이 SQL · 15:00]
 PLAN_NAME                        ORIGIN                          ENABLED  ACCEPTED  REPRODUCED  LAST_EXECUTED
 SQL_PLAN_8u2vbm4c0q7xa5d91e3f0   MANUAL-LOAD-FROM-CURSOR-CACHE   YES      YES       NO          14:09
 SQL_PLAN_8u2vbm4c0q7xa2b4c6e81   AUTO-CAPTURE                    YES      NO        YES         -

[받아들인 계획의 Outline — DISPLAY_SQL_PLAN_BASELINE 에서 필요한 줄만]
   INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("CF10"."SLE_DT" "CF10"."SHP_NO"))
   LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
   USE_NL(@"SEL$1" "B"@"SEL$1")

문제

베이스라인이 있는데도 계획이 바뀐 이유를 베이스라인이 계획을 저장하는 방식과 REPRODUCED 칸으로 설명하시오.
지금 할 응급 조치를 SQL로 쓰고, 인덱스를 감추거나 지우기 전에 확인할 것을 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 응급 : 인덱스를 다시 보이게 한다 (INVISIBLE 동안에도 DML 로 고쳐 왔으므로 바로 쓸 수 있다)
ALTER INDEX IX_CF10_DT_SHP VISIBLE;

-- ② 감추기 전에 : 이 인덱스를 쓰는 SQL 을 계획 이력에서 찾는다
SELECT DISTINCT sql_id, plan_hash_value
  FROM DBA_HIST_SQL_PLAN
 WHERE object_owner = 'PUP'
   AND object_name  = 'IX_CF10_DT_SHP';

-- ③ 그 SQL 가운데 베이스라인이 있는 것은 Outline 을 꺼내 본다
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => '&handle', format => 'OUTLINE'));

-- ④ 대신할 인덱스를 정하기 전에는 지우지 않는다 (SLE_DT 조건을 CF10_PK 가 받지 못한다)

개선 후 실행계획 학습용 예시 · OPT-005와 같은 규모

xplan

[VISIBLE 로 되돌린 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    256 |00:00:00.01 |     389 |
|   1 |  NESTED LOOPS                         |                |      1 |    250 |    256 |00:00:00.01 |     389 |
|   2 |   NESTED LOOPS                        |                |      1 |    250 |    256 |00:00:00.01 |     249 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    114 |    117 |00:00:00.01 |      11 |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |    114 |    117 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN                   | CF11_PK        |    117 |      2 |    256 |00:00:00.01 |     238 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | CF11           |    256 |      2 |    256 |00:00:00.01 |     140 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SLE_DT"=:DT AND "A"."SHP_NO"=:SHP)
   5 - access("B"."SLE_NO"="A"."SLE_NO")

Note
-----
   - SQL plan baseline SQL_PLAN_8u2vbm4c0q7xa5d91e3f0 used for this statement

모범 답안 서술

SQL plan baseline은 계획 자체가 아니라 그 계획을 다시 만드는 Outline 힌트 묶음을 저장한다. 받아들인 계획의 Outline에는 INDEX_RS_ASC(... ("CF10"."SLE_DT" "CF10"."SHP_NO"))가 있다. 14:10에 IX_CF10_DT_SHP가 INVISIBLE이 되자, 옵티마이저 눈에는 그 컬럼 순서의 인덱스가 없다. 02화에서 따를 수 없는 힌트가 조용히 버려졌듯이, 베이스라인의 힌트도 따를 수 없게 되어 받아들인 계획을 다시 만들지 못했다. DBA_SQL_PLAN_BASELINES의 REPRODUCED가 NO로 바뀐 것이 그 기록이다. 받아들인 계획을 하나도 재현하지 못하면 옵티마이저는 자기가 만든 계획을 쓴다. 남은 인덱스로는 SLE_DT 조건을 받을 수 없으므로 CF10을 FULL로 읽었다(3번 줄 4,583블록). 그래서 Note에서 베이스라인 줄이 빠졌고, 새 계획은 대기 줄(AUTO-CAPTURE, ACCEPTED NO)에 들어갔다. Buffers는 389 → 4,961이고 하루 600번이면 약 23만 → 298만 블록이다. 베이스라인은 계획을 지키는 장치지만 계획이 기대는 객체까지 지켜 주지는 않는다. 응급 조치는 ALTER INDEX IX_CF10_DT_SHP VISIBLE이다. INVISIBLE은 모든 세션의 옵티마이저에게서 인덱스를 감출 뿐, INSERT나 갱신이 있을 때마다 인덱스는 계속 고쳐 왔으므로 다시 보이게 하는 즉시 받아들인 계획이 재현되어 389블록으로 돌아간다. 인덱스를 감추거나 지우기 전에는 그 인덱스를 쓰는 SQL부터 찾는다. V$SQL_PLAN과 DBA_HIST_SQL_PLAN에서 OBJECT_NAME이 그 인덱스인 SQL을 뽑고, 그 가운데 베이스라인이나 프로파일이 있는 SQL은 DISPLAY_SQL_PLAN_BASELINE으로 Outline을 꺼내 그 인덱스를 가리키는지 본다. 대신할 인덱스가 있는지(이번에는 없었다)도 그 SQL들의 조건으로 확인한 다음에 감춘다.

해설

오과장의 판단이 틀린 곳: SLE_NO가 날짜로 시작해도 화면 SQL의 조건은 SLE_DT다. 옵티마이저는 SLE_DT = :dt를 SLE_NO LIKE '20231130%'로 바꿔 주지 않는다. 인덱스가 대신할 수 있는지는 컬럼의 뜻이 아니라 SQL의 조건으로 정해진다.
세션에서만 시험하는 길: 반대로 새 인덱스를 INVISIBLE로 만들어 두고 ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE인 세션에서만 계획을 보는 것은 안전하다. 지우려는 인덱스를 감출 때는 그 효과가 모든 세션에 바로 미친다(04화 §8).
베이스라인이 있다고 안심하면 안 되는 경우가 하나 더 있다. 받아들인 계획이 가리키는 인덱스를 지우고 다시 만들면서 컬럼 순서를 바꿔도 같은 일이 생긴다.
인덱스 정리 절차에 "그 인덱스를 Outline에 담은 베이스라인과 프로파일 목록"을 한 줄 더한다(ACC 단원의 사용하지 않는 인덱스 찾기와 함께).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · OPT-005와 같은 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · OPT-005와 같은 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황