지운 자리를 돌려받으려고 주문상품 테이블을 재구성한 다음 날 아침, 주문 상세 화면이 한 번에 0.5초씩 걸린다

중급DDL버거645 / 700

상황

버거 본사 POS DB는 Diagnostic Pack 계약이 없어서 AWR 대신 1시간마다 Statspack 스냅샷(level 7)을 찍는다(교재 17화). 일요일 밤 DBA가 2011년 시험 데이터 30만 줄을 지운 ORD_ITEM의 빈 공간을 돌려받으려고 테이블을 재구성했다. 월요일 아침부터 점장 화면 「주문 상세」(주문 번호로 상품 줄을 보는 화면, 하루 약 4,000번)가 한 번에 0.5초씩 걸린다. 오과장은 고개를 갸웃했다. "블록이 줄었으니 더 빨라져야 하는데요." POS 마감 적재는 매일 밤 23:30에 ORD와 ORD_ITEM에 전날 주문을 넣는다.

테이블과 인덱스

ORD_ITEM: 지금 2,472,717행 · 55,265블록(재구성 전에는 2011년 줄 30만 개를 지운 빈자리까지 약 62,000블록) · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
ITEM: 92행 · 5블록 · ITEM_PK (ITEM_ID)
SKIP_UNUSABLE_INDEXES 기본값(TRUE) · Statspack 1시간 간격 level 7(실행계획까지 남긴다)

원본 SQL

sql

-- 일요일 23:30 DBA 작업
DELETE FROM ORD_ITEM WHERE ORD_DT < '20120101';   -- 2011년 시험 데이터 300,000줄
COMMIT;
ALTER TABLE ORD_ITEM MOVE;                         -- 빈 공간을 돌려받는다
-- 통계는 자동 수집에 맡긴다

-- 점장 화면 「주문 상세」 (SQL_ID 6hd0k3w8z1q5m)
SELECT i.ITEM_ID, t.ITEM_NM, i.ORD_ITEM_QTY
  FROM ORD_ITEM i, ITEM t
 WHERE i.ORD_NO = :ord_no
   AND t.ITEM_ID = i.ITEM_ID;

실행계획 학습용 예시

xplan

[Statspack SQL 보고서 (sprepsql.sql) · SQL_ID 6hd0k3w8z1q5m · 일 22:00 ~ 월 11:00]
 Snap Id  시작        Plan Hash Value  Executions  Buffer Gets  Gets/Exec  Physical Reads
   8841   일 22:00        1712459063          36          360       10.0               0
   8853   월 10:00         396217480         412   22,772,064   55,272.0      22,769,180
   8854   월 11:00         396217480         431   23,822,232   55,272.0      23,819,215

 Known Optimizer Plan(s) for this Old Hash Value
  1712459063 : NESTED LOOPS → TABLE ACCESS BY INDEX ROWID ORD_ITEM → INDEX RANGE SCAN ORD_ITEM_PK → TABLE ACCESS BY INDEX ROWID ITEM → INDEX UNIQUE SCAN ITEM_PK
   396217480 : NESTED LOOPS → TABLE ACCESS FULL ORD_ITEM → TABLE ACCESS BY INDEX ROWID ITEM → INDEX UNIQUE SCAN ITEM_PK

[DBA_OBJECTS · DBA_INDEXES]
 ORD_ITEM      TABLE  LAST_DDL_TIME 일 23:41
 ORD_ITEM_PK   INDEX  STATUS UNUSABLE
 ORD_ITEM_X01  INDEX  STATUS UNUSABLE

[지금 커서 · ALLSTATS LAST · 주문 하나]
Plan hash value: 396217480

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |      3 |00:00:00.49 |   55272 |  55265 |
|   1 |  NESTED LOOPS                |          |      1 |      3 |      3 |00:00:00.49 |   55272 |  55265 |
|*  2 |   TABLE ACCESS FULL          | ORD_ITEM |      1 |      3 |      3 |00:00:00.49 |   55266 |  55265 |
|   3 |   TABLE ACCESS BY INDEX ROWID| ITEM     |      3 |      1 |      3 |00:00:00.01 |       6 |      0 |
|*  4 |    INDEX UNIQUE SCAN         | ITEM_PK  |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("I"."ORD_NO"=:ORD_NO)
   4 - access("T"."ITEM_ID"="I"."ITEM_ID")

문제

팩이 없는 이 DB에서 계획이 언제, 무엇 때문에 바뀌었는지 위 자료로 설명하시오. 오류가 하나도 나지 않은 이유도 쓰시오.
지금 할 조치를 SQL로 쓰시오. 오늘 밤 23:30 적재 전에 꼭 끝내야 하는 이유와, 다음에 같은 재구성을 할 때의 방법도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 두 인덱스를 다시 만든다 (다시 만들 때 인덱스 통계도 함께 모인다)
ALTER INDEX ORD_ITEM_PK  REBUILD;
ALTER INDEX ORD_ITEM_X01 REBUILD;

-- ② 확인 : STATUS = VALID · 「주문 상세」 의 다음 하드 파싱부터 INDEX RANGE SCAN ORD_ITEM_PK
SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'ORD_ITEM';

-- ③ 30만 줄을 지운 뒤라 테이블 통계도 새로 모은다
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD_ITEM', cascade => FALSE);

-- ④ 다음 재구성부터는 인덱스를 함께 고친다
-- ALTER TABLE ORD_ITEM MOVE UPDATE INDEXES;

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

xplan

Plan hash value: 1712459063

------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |      1 |        |      3 |00:00:00.01 |      10 |      0 |
|   1 |  NESTED LOOPS                |             |      1 |      3 |      3 |00:00:00.01 |      10 |      0 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ORD_ITEM    |      1 |      3 |      3 |00:00:00.01 |       4 |      0 |
|*  3 |    INDEX RANGE SCAN          | ORD_ITEM_PK |      1 |      3 |      3 |00:00:00.01 |       3 |      0 |
|   4 |   TABLE ACCESS BY INDEX ROWID| ITEM        |      3 |      1 |      3 |00:00:00.01 |       6 |      0 |
|*  5 |    INDEX UNIQUE SCAN         | ITEM_PK     |      3 |      1 |      3 |00:00:00.01 |       3 |      0 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."ORD_NO"=:ORD_NO)
   5 - access("T"."ITEM_ID"="I"."ITEM_ID")

모범 답안 서술

언제, 무엇이 바뀌었나: 팩이 없으니 Statspack의 SQL 보고서로 본다. Plan Hash Value가 일요일 22:00 스냅샷의 1712459063(ORD_ITEM_PK로 찾는 NL)에서 월요일의 396217480(ORD_ITEM FULL)으로 바뀌었고, 실행당 블록은 10에서 55,272로, 실행당 디스크 읽기는 0에서 55,265로 늘었다. 그 사이에 있었던 변경은 ORD_ITEM의 DDL(LAST_DDL_TIME 일 23:41), 곧 ALTER TABLE ... MOVE다. 지금 두 인덱스는 UNUSABLE이다. 왜 그렇게 되었나: MOVE는 행을 새 블록으로 옮기므로 rowid가 모두 바뀐다. 인덱스에 적힌 rowid는 옛 자리를 가리키게 되어 오라클은 인덱스를 UNUSABLE로 표시한다. SKIP_UNUSABLE_INDEXES가 TRUE라 옵티마이저는 UNUSABLE 인덱스를 오류 없이 건너뛰고 남은 길, 곧 테이블 FULL을 골랐다. 주문 하나의 상품 세 줄을 보려고 55,265블록을 디스크에서 읽는다. 하루 4,000번이면 약 2억 2천만 블록이다. 조회에는 오류가 나지 않으므로 아무도 알아채지 못했다. 지금 할 조치: 두 인덱스를 REBUILD로 다시 만든다. 다시 만들면 인덱스 통계도 함께 모이고, 다음 하드 파싱부터 다시 ORD_ITEM_PK로 찾는다(Buffers 55,272 → 10). 30만 줄을 지운 뒤이므로 테이블 통계도 새로 모은다. 23:30 전에 끝내야 하는 이유: ORD_ITEM_PK는 유니크 인덱스다. 조회는 UNUSABLE 인덱스를 건너뛰지만, 유니크 인덱스가 UNUSABLE인 테이블에 행을 넣으면 ORA-01502로 실패한다. 그대로 두면 오늘 밤 POS 마감 적재가 멈춘다. 다음 재구성 방법: ALTER TABLE ORD_ITEM MOVE UPDATE INDEXES로 옮기는 동안 인덱스도 함께 고치게 한다. Enterprise Edition이라면 MOVE ONLINE으로 업무 중에도 DML을 막지 않고 옮길 수 있다. 어느 쪽이든 작업 뒤에 USER_INDEXES의 STATUS를 확인하는 줄을 작업 절차에 넣는다.

해설

팩이 없어도 계획 이력은 남길 수 있다. Statspack은 level 6 이상이면 실행계획까지 남기고, SQL 보고서(sprepsql.sql)가 스냅샷마다 Plan Hash Value와 실행당 숫자를 보여 준다. 다만 미리 깔아 두고 미리 찍어 두어야 한다(교재 17화).
REBUILD 하는 동안 DML이 막힌다. 온라인이 아닌 REBUILD는 그동안 테이블에 DML을 허용하지 않는다(조회는 된다). 적재가 없는 낮 시간에 하는 이유다. REBUILD ONLINE은 Enterprise Edition 기능이다.
블록이 줄면 빨라진다는 기대는 FULL로 읽는 SQL에만 맞는다. 재구성으로 줄어든 것은 약 62,000 → 55,265블록이고, 인덱스로 한 주문을 찾는 화면에는 원래 상관이 없었다. 재구성의 이득과 대가를 적을 때는 어떤 SQL이 그 테이블을 어떻게 읽는지부터 본다(교재 05화).
같은 일은 파티션을 지우거나 비울 때 글로벌 인덱스에서도 생긴다. 그때도 UPDATE INDEXES를 붙인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황