튜닝 팩을 들여온 다음 주부터 MD의 상품별 매장 판매 화면이 이틀에 하루꼴로 하루 종일 느리다

실전PLSQL유통648 / 700

상황

본사 유통 MD 화면 「상품별 매장 판매」는 상품 번호를 넣으면 매장별 판매 수량과 금액을 보여 준다. 화면은 패키지 프로시저를 부르고, 프로시저 안의 정적 SQL이 상품 번호를 바인드로 받는다. MD 20명이 하루 약 900번 열고, 그 가운데 약 360번이 판매 1위와 2위 상품(1001, 1002)이다. 3월에 아침 첫 조회의 값에 따라 하루 종일 계획이 갈리는 사고가 난 뒤, DBA는 이 SQL을 해시 조인 계획 하나만 받아들인 베이스라인으로 지켜 왔다. 10월 6일 본사가 Tuning Pack을 계약했고, DBA는 그날 2019년부터 꺼 두었던 자동 SQL 튜닝 작업을 다시 켰다. 10월 7일부터 이틀에 하루꼴로 인기 상품 조회가 하루 종일 2초씩 걸린다. 박팀장은 지금 할 일과 다시 생기지 않게 할 일을 함께 정하자고 했다.

테이블과 인덱스

CF11 판매상품: 지금 1,118,268행 · 8,365블록(올해 신상품 판매가 더해졌다) · IX_CF11_ITM (ITM_NO): BLEVEL 2 · 리프 약 2,200 · ITM_NO에 FREQUENCY 히스토그램
CF11은 매일 01:00 적재 뒤 통계를 다시 모은다(no_invalidate => FALSE). 그래서 이 SQL은 아침 첫 조회 때 하드 파싱한다.
상품별 판매 줄은 크게 치우쳐 있다: 1001 275,135 · 1002 202,156 · 1003 30,870 · 기존 상품 가운데 가장 적은 1031 3,032 · 올해 신상품 1109 217 · 1096 140
CF10 판매: 지금 509,800행 · 4,673블록 · CF10_PK (SLE_NO): BLEVEL 2 · 판매하는 매장 5곳
운영 DB: Enterprise Edition · optimizer_use_sql_plan_baselines TRUE · 자동 캡처는 꺼져 있다
SYS_AUTO_SPM_EVOLVE_TASK의 설정은 한 번도 바꾼 적이 없다

원본 SQL

sql

-- 화면이 부르는 프로시저 (정적 SQL · SQL_ID 7w2c5n0q8k4xd)
PROCEDURE ITEM_SHOP_SALES(p_itm IN NUMBER, p_tab OUT T_SHOP_SALES_TAB) IS
BEGIN
  SELECT T_SHOP_SALES(h.SHP_NO, SUM(s.SLE_QTY), SUM(s.SLE_QTY * s.SLE_UPRC))
    BULK COLLECT INTO p_tab
    FROM CF11 s, CF10 h
   WHERE s.ITM_NO = p_itm
     AND h.SLE_NO = s.SLE_NO
   GROUP BY h.SHP_NO;
END;

-- 2025-03-04 DBA : 해시 계획(2271840516) 하나만 받아들인 베이스라인으로 올렸다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => '7w2c5n0q8k4xd',
                                             plan_hash_value => 2271840516);
END;
/

-- 2025-10-06 DBA : Tuning Pack 계약에 맞춰 자동 SQL 튜닝 작업을 다시 켰다
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
END;
/

실행계획 학습용 예시

xplan

[DBA_SQL_PLAN_BASELINES · SQL_ID 7w2c5n0q8k4xd]
 PLAN_NAME                       ORIGIN                         ENABLED ACCEPTED CREATED   LAST_VERIFIED   계획
 SQL_PLAN_6s7m9k4c1e2b5a61e4f2   MANUAL-LOAD-FROM-CURSOR-CACHE  YES     YES      25-03-04  -               HASH JOIN (CF11 FULL · CF10 FULL) · 2271840516
 SQL_PLAN_6s7m9k4c1e2b5c93b7d5   AUTO-CAPTURE                   YES     YES      25-03-05  25-10-06 22:14  NESTED LOOPS (IX_CF11_ITM → CF10_PK) · 3658024197

[DBA_ADVISOR_EXECUTIONS · SYS_AUTO_SPM_EVOLVE_TASK]  첫 실행 25-10-06 22:00 · 그 뒤 매일 22:00 · 그 전 기록 없음
[DBA_ADVISOR_PARAMETERS · SYS_AUTO_SPM_EVOLVE_TASK]  ACCEPT_PLANS = TRUE (기본값)

[DBMS_SPM.REPORT_AUTO_EVOLVE_TASK · 10-06 22:00 실행 · 이 SQL 부분만]
 Plan: SQL_PLAN_6s7m9k4c1e2b5c93b7d5
                         Base Plan (HASH)   Test Plan (NL)
   Elapsed Time (s)                .2480            .0060
   Buffer Gets                    13,038              558
   Rows Processed                      5                5
 FINDINGS
  1. The plan was verified in 0.31000 seconds. It passed the benefit criterion because its verified
     performance was 22.91347 times better than that of the baseline plan.
  2. The plan was automatically accepted.
 (시험 실행의 바인드 :B1 = 1096 · 그 계획이 3월 5일 대기 줄에 들어올 때 엿본 값)

[DBA_HIST_SQLSTAT · 7w2c5n0q8k4xd · 날마다]
 날짜    아침 첫 조회의 :B1   그날 계획     EXECUTIONS   BUFFER_GETS   ELAPSED(초)
 10-06   1002                 2271840516          904    11,786,352           224
 10-07   1109                 3658024197          911   281,947,205           742
 10-08   1001                 2271840516          897    11,695,086           221
 10-09   1096                 3658024197          906   280,316,844           737
 10-10   1003                 2271840516          893    11,642,934           220

[10-09 10:21 · :B1 = 1001 · 그날 08:02 첫 조회(1096)로 만든 커서]
Plan hash value: 3658024197

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |      5 |00:00:02.11 |    834K |
|   1 |  HASH GROUP BY                        |             |      1 |      5 |      5 |00:00:02.11 |    834K |
|   2 |   NESTED LOOPS                        |             |      1 |    140 |    275K|00:00:01.98 |    834K |
|   3 |    NESTED LOOPS                       |             |      1 |    140 |    275K|00:00:01.12 |    559K |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF11       |      1 |    140 |    275K|00:00:00.31 |    8697 |
|*  5 |      INDEX RANGE SCAN                 | IX_CF11_ITM |      1 |    140 |    275K|00:00:00.06 |     552 |
|*  6 |     INDEX UNIQUE SCAN                 | CF10_PK     |    275K|      1 |    275K|00:00:00.58 |    550K |
|   7 |    TABLE ACCESS BY INDEX ROWID        | CF10        |    275K|      1 |    275K|00:00:00.71 |    275K |
--------------------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :B1 (NUMBER): 1096

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("S"."ITM_NO"=:B1)
   6 - access("H"."SLE_NO"="S"."SLE_NO")

[같은 SQL을 두 계획으로 잰 Buffers · 시험 DB(운영 복제)]
 :B1    판매 줄    HASH (2271840516)   NL (3658024197)
 1001   275,135              13,038            834,102
 1002   202,156              13,038            614,873
 1003    30,870              13,038            100,772
 1031     3,032              13,038             11,647
 1109       217              13,038                864
 1096       140              13,038                558

문제

3월부터 대기 줄에 있던 NL 계획이 10월 6일 밤에 받아들여진 경위를 쓰고, 진화를 통과한 그 계획이 인기 상품에서 지는 이유를 리포트와 바인드 값으로 설명하시오. 이 화면에서 ACS가 이 일을 고쳐 주지 못하는 이유도 쓰시오.
지금 할 조치와 다시 생기지 않게 할 조치를 SQL로 쓰시오. 그 계획을 지우는 안과 끄는 안의 차이도 쓰시오.
앞으로 대기 계획을 받아들이는 절차를 쓰고, 두 계획을 모두 살리는 안과 비교해 고른 안의 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 지금 : 자동으로 받아들여진 NL 계획만 끈다 (지우지 않는다 · 해시 계획은 그대로)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(sql_handle      => 'SQL_6c1e9a4f2b7d3058',
                                        plan_name       => 'SQL_PLAN_6s7m9k4c1e2b5c93b7d5',
                                        attribute_name  => 'ENABLED',
                                        attribute_value => 'NO');
END;
/

-- ② 지금 NL 로 도는 커서를 공유 풀에서 내린다 (다음 호출이 해시 계획으로 다시 파싱)
SELECT ADDRESS || ',' || HASH_VALUE AS obj FROM V$SQLAREA WHERE SQL_ID = '7w2c5n0q8k4xd';
EXEC DBMS_SHARED_POOL.PURGE('&obj', 'C');

-- ③ 재발 방지 : 자동 진화는 밤마다 시험하고 리포트만 남긴다 (받아들이지 않는다)
BEGIN
  DBMS_SPM.SET_EVOLVE_TASK_PARAMETER(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK',
                                     parameter => 'ACCEPT_PLANS',
                                     value     => 'FALSE');
END;
/
SELECT PARAMETER_NAME, PARAMETER_VALUE
  FROM DBA_ADVISOR_PARAMETERS
 WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK'
   AND PARAMETER_NAME = 'ACCEPT_PLANS';

-- ④ 앞으로 : 밤마다 남는 리포트를 사람이 읽고, 대표 값(1001 · 1002 · 1003 · 1031 · 신상품)으로
--    두 계획을 시험 DB에서 잰 뒤에만 IMPLEMENT_EVOLVE_TASK 나 LOAD_PLANS_FROM_CURSOR_CACHE 로 받아들인다
SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM dual;

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

xplan

[ALTER_SQL_PLAN_BASELINE 뒤 · DBA_SQL_PLAN_BASELINES]
 SQL_PLAN_6s7m9k4c1e2b5a61e4f2   HASH   ENABLED YES · ACCEPTED YES
 SQL_PLAN_6s7m9k4c1e2b5c93b7d5   NL     ENABLED NO  · ACCEPTED YES   (이력에 남는다 · 쓰이지 않는다)

[커서를 내린 뒤 · :B1 = 1001]
Plan hash value: 2271840516

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      5 |00:00:00.25 |   13038 |          |
|   1 |  HASH GROUP BY      |      |      1 |      5 |      5 |00:00:00.25 |   13038 |          |
|*  2 |   HASH JOIN         |      |      1 |    275K|    275K|00:00:00.21 |   13038 |   27M (0)|
|*  3 |    TABLE ACCESS FULL| CF11 |      1 |    275K|    275K|00:00:00.09 |    8365 |          |
|   4 |    TABLE ACCESS FULL| CF10 |      1 |    509K|    509K|00:00:00.04 |    4673 |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("H"."SLE_NO"="S"."SLE_NO")
   3 - filter("S"."ITM_NO"=:B1)

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

모범 답안 서술

경위: 3월 4일 DBA는 해시 계획 하나만 받아들인 베이스라인을 올렸다. 그 뒤 아침 첫 조회가 신상품이면 옵티마이저는 히스토그램으로 140줄을 보고 NL을 만들었지만, 받아들인 계획이 아니라 쓰지 못하고 대기 줄(3월 5일, AUTO-CAPTURE, ACCEPTED NO)에만 남겼다. 대기 계획을 시험하는 SPM 자동 진화 과제는 이 버전에서 자동 SQL 튜닝 작업(sql tuning advisor)과 같은 정비 창 작업으로 도는데, 그 작업이 2019년부터 꺼져 있어 일곱 달 동안 아무도 시험하지 않았다. 10월 6일 작업을 켜자 그날 밤 22:00 정비 창에서 SYS_AUTO_SPM_EVOLVE_TASK가 처음 돌았고(DBA_ADVISOR_EXECUTIONS의 첫 줄), ACCEPT_PLANS가 기본값 TRUE라 통과한 계획을 사람의 확인 없이 받아들였다(LAST_VERIFIED 22:14). 통과했는데 왜 지나: 시험 실행은 그 계획이 대기 줄에 들어올 때 엿본 값 하나(1096, 140줄)로 했다. 그 값에서는 NL 558 대 해시 13,038로 23배쯤 낫다. 신상품 1096으로 시험했으니 통과하는 것이 당연했다. 그러나 1001(275,135줄)을 넣으면 NL은 판매상품 27만 줄마다 CF10_PK와 판매를 찾아가 834,102블록을 읽는다. 해시 13,038의 64배다. 진화는 '그 값에서 더 나은가'만 보았고 '어느 값에서도 크게 지지 않는가'는 보지 않았다. 왜 이틀에 하루꼴인가: 이제 받아들인 계획이 둘이다. 매일 01:00 CF11 통계를 다시 모으며 커서가 무효화되므로 아침 첫 조회의 값으로 그날 커서를 하드 파싱한다. 첫 값이 신상품이면 옵티마이저가 고른 NL이 받아들인 목록에 있으니 NL로 정해지고, 그 커서를 하루 종일 같이 쓴다(10-07, 10-09: 하루 약 2억 8천만 블록). 첫 값이 기존 상품이면 해시가 정해져 하루 약 1,170만 블록이다. ACS도 이 일을 고쳐 주지 못한다. 화면은 프로시저 안의 정적 SQL로 묻는데, PL/SQL은 커서를 열어 둔 채 다시 실행해서 파스 호출이 없으므로 자식을 다시 고를 기회가 오지 않는다(교재 11화 §7 [E]). 지금 할 조치: NL 계획만 ENABLED NO로 끈다. 받아들인 목록에서 쓸 수 있는 계획은 다시 해시 하나가 된다. 지금 NL로 도는 커서는 공유 풀에서 내려 다음 호출부터 해시로 다시 파싱하게 한다(1001 기준 834,102 → 13,038블록). 지우지 않고 끄는 이유가 있다. 지우면 계획 이력에서 사라지므로 다음에 신상품으로 파싱할 때 같은 NL이 새 대기 계획으로 다시 들어오고, ACCEPT_PLANS가 TRUE인 채라면 그날 밤 또 받아들여진다. 끈 계획은 이력에 남아 있어 다시 들어오지 않고, 왜 껐는지도 이력으로 남는다. 다시 생기지 않게: SET_EVOLVE_TASK_PARAMETER로 SYS_AUTO_SPM_EVOLVE_TASK의 ACCEPT_PLANS를 FALSE로 바꾼다. 자동 과제는 밤마다 대기 계획을 시험하고 리포트를 남기지만 받아들이지는 않는다. 자동 SQL 튜닝 작업 전체를 끄는 안도 있지만, 그러면 새로 계약한 튜닝 팩의 권고까지 사라진다. 받아들이는 절차: 반기 점검이나 리포트가 나온 다음 날, 대기 계획마다 시험 DB에서 대표 바인드 값(인기 상품 1001과 1002, 중간 1003, 적은 1031, 신상품)으로 두 계획을 각각 잰다. 어느 값에서도 크게 지지 않는 계획만 IMPLEMENT_EVOLVE_TASK나 LOAD_PLANS_FROM_CURSOR_CACHE로 받아들이고, 값별 숫자를 베이스라인 대장에 남긴다. 두 계획을 모두 살리는 안과 비교: 해시 하나로 되돌리면 신상품 조회(하루 약 90번)는 558 대신 13,038블록을 읽어 하루 약 110만 블록을 손해 본다. 두 계획을 다 살리려면 값에 따라 커서가 갈라져야 하는데, 지금 구조에서는 첫 값이 하루를 정한다. 프로시저를 신상품용과 기존 상품용 두 문장으로 나누거나(상품 등록일로 분기), 동적 SQL로 매번 파싱하게 하고 BIND_AWARE 힌트로 ACS가 첫 실행부터 값을 보게 하는 길이 있다. 둘 다 코드를 고쳐 배포해야 하고 나누는 기준을 코드에 박는다. 사고 한 번에 더 읽는 블록은 약 2억 7천만이라, 오늘은 해시 하나로 되돌리고 나누는 안은 다음 배포에서 검토한다.

해설

진화가 검사하는 것은 '그 값'이다. 18화의 두 진화(63.7배 통과, 0.33배 탈락)도 각각 한 가지 조건의 시험 실행이었다. 값마다 일량이 크게 다른 SQL에서는 통과가 곧 안전은 아니다.
자동 과제를 켜는 날에는 대기 줄부터 본다. 일곱 달 동안 쌓인 대기 계획이 첫날 밤에 한꺼번에 시험대에 오른다. 켜기 전에 ACCEPT_PLANS를 FALSE로 두고 첫 리포트를 사람이 읽는다(OPT-057의 반기 점검과 같은 판정).
Free에는 자동 진화가 없다(교재 18화 §3). 연습 DB에서는 재현되지 않는 사고라 운영의 설정을 DBA_ADVISOR_PARAMETERS로 직접 확인한다.
3월의 결정에도 근거를 적어 둔다. 해시 하나만 받아들인 것은 신상품 조회에 13,038블록을 쓰는 대가로 예측할 수 있는 계획을 산 결정이다. 그 근거(인기 상품이 조회의 40%)가 대장에 있었다면 자동 과제를 켜는 날 같은 질문을 먼저 했을 것이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황