인덱스를 만들고 진화 작업까지 돌렸는데 진화할 계획이 하나도 없다고 나온다

기초PLSQL이커머스640 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

이커머스 고객센터의 「포인트 내역」 화면은 고객 한 명의 포인트 거래를 최신순으로 보여 준다. 상담원이 하루 약 2만 번 연다. 지난주 DBA가 이 화면을 위해 (customer_id, created_at) 인덱스를 만들었는데 계획은 그대로 FULL이다. Note에는 베이스라인 줄이 있다. 2023년 3월 사이트를 열 때 외주 업체가 상위 SQL 30개를 베이스라인으로 올리면서 "오픈 직후 계획이 흔들리지 않게" 고정해 두었다는 기록이 있다. 조민우는 18화에서 배운 대로 진화 작업을 돌렸지만, 리포트에는 검증할 계획이 없다는 말뿐이었다.

테이블과 인덱스

point_transactions: 45,000행 · 210블록 · 고객 5,230명 · 고객당 약 9건 · tx_type(EARN · USE · EXPIRE)
인덱스: PK (tx_id) · 지난주에 만든 IX_PT_CUS_DT (customer_id, created_at) · 통계 최신
대조: 같은 날 만든 IX_RV_PRD (product_id) 덕을 볼 「상품 리뷰 목록」 SQL도 2023년 3월에 같은 방식으로 베이스라인을 올렸다

원본 SQL

sql

-- 2023-03-02 오픈 대비 (외주 업체) : 상위 SQL 의 계획을 올리고 고정한다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '9w1n4c7t2k8dq');
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle      => 'SQL_6r2k8x1v9c0a5f',
                                        attribute_name  => 'FIXED',
                                        attribute_value => 'YES');
END;
/

-- 지난주 (DBA) : 포인트 내역 화면용 인덱스
CREATE INDEX IX_PT_CUS_DT ON point_transactions (customer_id, created_at);

-- 어제 (조민우) : 진화 작업
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_6r2k8x1v9c0a5f');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
END;
/

-- 포인트 내역 화면 SQL (SQL_ID 9w1n4c7t2k8dq)
SELECT tx_id, tx_type, amount, created_at
  FROM point_transactions
 WHERE customer_id = :cid
 ORDER BY created_at DESC;

실행계획 학습용 예시

xplan

[지금 계획 · ALLSTATS LAST]
Plan hash value: 3365107282

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |      1 |        |      9 |00:00:00.01 |     211 |          |
|   1 |  SORT ORDER BY     |                    |      1 |      9 |      9 |00:00:00.01 |     211 | 2048  (0)|
|*  2 |   TABLE ACCESS FULL| POINT_TRANSACTIONS |      1 |      9 |      9 |00:00:00.01 |     211 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CUSTOMER_ID"=:CID)

Note
-----
   - SQL plan baseline "SQL_PLAN_6r2k8x1v9c0a5f3d2b17" used for this statement

[DBA_SQL_PLAN_BASELINES]
 SQL_HANDLE          PLAN_NAME                      ORIGIN                         ENABLED ACCEPTED FIXED CREATED
 SQL_6r2k8x1v9c0a5f  SQL_PLAN_6r2k8x1v9c0a5f3d2b17  MANUAL-LOAD-FROM-CURSOR-CACHE  YES     YES      YES   23-03-02
 (대조 · 상품 리뷰 목록)
 SQL_2c9e4d7a1b3f60  SQL_PLAN_2c9e4d7a1b3f60e10a77  MANUAL-LOAD-FROM-CURSOR-CACHE  YES     YES      NO    23-03-02
 SQL_2c9e4d7a1b3f60  SQL_PLAN_2c9e4d7a1b3f6044c19a  AUTO-CAPTURE                   YES     NO       NO    24-10-22

[진화 리포트 요약 · 어제 · 포인트 내역 SQL]
 검증한 계획 0개 · 이 문장의 계획 이력에 ACCEPTED = NO 인 계획이 없다

문제

인덱스를 만들고 진화 작업을 돌렸는데도 검증할 계획이 하나도 없는 이유를 베이스라인 목록으로 설명하시오. 대조로 적은 상품 리뷰 목록 SQL과 무엇이 다른지도 쓰시오.
인덱스 계획이 검사를 거쳐 쓰이도록 SQL로 고치고, 앞으로 FIXED를 어떻게 다룰지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① FIXED 를 푼다 (받아들인 FULL 계획은 그대로 남으므로 그동안 계획이 흔들리지 않는다)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
         sql_handle      => 'SQL_6r2k8x1v9c0a5f',
         plan_name       => 'SQL_PLAN_6r2k8x1v9c0a5f3d2b17',
         attribute_name  => 'FIXED',
         attribute_value => 'NO');
END;
/

-- ② 화면 SQL 이 다시 하드 파싱되면 옵티마이저가 만든 인덱스 계획이 대기 줄(AUTO-CAPTURE · ACCEPTED NO)에 들어온다
--    기다리지 않으려면 그 커서만 지운다 : EXEC DBMS_SHARED_POOL.PURGE('&address,&hash_value', 'C');

-- ③ 진화 : 두 계획을 실제로 돌려 보고 더 나을 때만 받아들인다
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_6r2k8x1v9c0a5f');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
  DBMS_OUTPUT.PUT_LINE(t);
END;
/
SELECT DBMS_SPM.REPORT_EVOLVE_TASK(task_name => '&task') FROM dual;
-- 리포트에서 통과를 확인한 뒤
DECLARE
  r NUMBER;
BEGIN
  r := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => '&task');
END;
/

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

xplan

Plan hash value: 1488305316

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |      1 |        |      9 |00:00:00.01 |      12 |
|   1 |  TABLE ACCESS BY INDEX ROWID | POINT_TRANSACTIONS |      1 |      9 |      9 |00:00:00.01 |      12 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_PT_CUS_DT       |      1 |      9 |      9 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CUSTOMER_ID"=:CID)

Note
-----
   - SQL plan baseline "SQL_PLAN_6r2k8x1v9c0a5f9e41c08a" used for this statement

모범 답안 서술

포인트 내역 SQL의 베이스라인에는 계획이 하나뿐이고 그 계획이 FIXED = YES다. 2023년 오픈 때 외주 업체가 커서 캐시에서 올리면서 고정한 계획이다. 베이스라인에 고정 계획이 있으면 옵티마이저는 그 계획을 우선 쓰고, 새로 만든 계획을 계획 이력의 대기 줄에 더하지 않는다(공식 문서의 설명). 그래서 지난주 IX_PT_CUS_DT가 생겨 옵티마이저가 인덱스 계획을 만들 수 있게 되었어도 그 계획은 어디에도 기록되지 않았고, 진화 작업은 검증할 대상(ACCEPTED NO)이 없다며 끝났다. 대조로 본 상품 리뷰 목록 SQL은 같은 날 올린 베이스라인이지만 FIXED가 NO라서, 새 인덱스로 만든 계획이 2024년 10월 22일에 AUTO-CAPTURE, ACCEPTED NO로 대기 줄에 들어와 있다. 고치는 순서는 이렇다. 먼저 ALTER_SQL_PLAN_BASELINE으로 FIXED를 NO로 바꾼다. 받아들인 FULL 계획은 그대로 남으므로 그동안 계획이 흔들리지는 않는다. 화면 SQL이 다시 하드 파싱되면(기다리지 않으려면 그 커서만 DBMS_SHARED_POOL.PURGE로 지운다) 옵티마이저가 만든 인덱스 계획이 대기 줄에 들어온다. 그다음 진화 작업으로 두 계획을 실제로 돌려 비교하고, 리포트에서 통과를 확인한 뒤 IMPLEMENT_EVOLVE_TASK로 받아들인다. 고객 한 명의 거래 9건을 인덱스로 거꾸로 읽으면 정렬도 사라지고 Buffers는 211 → 12다. 하루 2만 번이면 약 422만 → 24만 블록이다. 앞으로 FIXED는 기본으로 쓰지 않는다. FIXED는 베이스라인의 장점인 진화라는 검사를 스스로 끄는 설정이다. 꼭 써야 한다면 베이스라인 대장에 이유와 해제 조건(인덱스나 데이터 규모가 바뀌면 푼다)을 함께 적는다.

해설

FIXED의 동작은 18화에서 재지 않았다. 교재 18화 §2.1은 '고정 계획이 우선하고 새 계획을 대기 줄에 더하지 않는다'를 공식 문서의 설명으로만 적었다. 이 문제의 상황이 그 설명대로 움직인 경우다. 운영에서 FIXED를 풀기 전에 시험 DB에서 같은 순서를 한 번 밟아 본다.
다른 길도 있다. 내 세션에서만 OPTIMIZER_USE_SQL_PLAN_BASELINES = FALSE로 화면 SQL을 돌려 인덱스 계획을 커서 캐시에 만든 뒤, LOAD_PLANS_FROM_CURSOR_CACHE에 원래 문장의 sql_handle과 그 계획의 plan_hash_value를 주면 바로 받아들인 계획으로 올라간다. 빠르지만 진화의 시험 운행을 건너뛴다.
베이스라인을 통째로 지우는 것(DROP_SQL_PLAN_BASELINE)은 답이 아니다. 지우면 다음 파싱에서 옵티마이저가 인덱스를 고르겠지만, 오픈 때 지키려던 이유가 아직 있는지 확인하지 않은 채 장치를 없애는 일이다.
상위 SQL 30개 가운데 FIXED인 것이 더 있는지 DBA_SQL_PLAN_BASELINES에서 함께 찾는다. 같은 업체가 같은 날 올렸다면 같은 상태일 가능성이 크다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황