본사 ERP의 월 판매 명세 추출은 SQL Developer에서는 0.3초인데 ERP에서는 1초가 걸린다

중급SELECT유통627 / 700

상황

본사 유통 ERP는 외부 업체의 패키지다. 업체 설치 지침에 따라 ERP는 로그인하자마자 세션에 ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10을 건다. 지침을 바꾸면 유지보수 계약에서 벗어나고, SQL은 암호화된 패키지 안에 있어 한 글자도 고칠 수 없다. 매일 아침 본부 담당자 서른 명이 「월 판매 명세」를 내려받는다(지난달 전체, 끝까지 받는다). 선배가 같은 글자의 SQL을 SQL Developer에서 한 번 돌려 보고 말했다. "SQL은 못 고쳐도 계획은 고칠 수 있지."

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 2023년 6월 20,953건 · IX_CF10_SLE_DT (SLE_DT) 리프 1,393 · 클러스터링 팩터 4,601(날짜순 적재)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 판매 한 건에 평균 2.2줄(같은 블록)
CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO)
인스턴스 OPTIMIZER_MODE는 ALL_ROWS · 통계 최신 · SQL Plan Management 사용 가능(베이스라인은 아직 없다)

원본 SQL

sql

SELECT a.SLE_NO, a.SLE_DT, a.SHP_NO, b.ITM_NO, c.ITM_NM, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b, CF01 c
 WHERE a.SLE_DT BETWEEN :1 AND :2
   AND b.SLE_NO = a.SLE_NO
   AND c.ITM_NO = b.ITM_NO

실행계획 학습용 예시

xplan

[ERP 세션 — '20230601' ~ '20230630' · 끝까지 받은 뒤]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |      1 |        |  45960 |00:00:00.97 |     176K|
|*  1 |  FILTER                            |                |      1 |        |  45960 |00:00:00.97 |     176K|
|   2 |   NESTED LOOPS                     |                |      1 |     10 |  45960 |00:00:00.95 |     176K|
|   3 |    NESTED LOOPS                    |                |      1 |     10 |  45960 |00:00:00.71 |     130K|
|   4 |     NESTED LOOPS                   |                |      1 |     10 |  45960 |00:00:00.52 |   84500 |
|   5 |      NESTED LOOPS                  |                |      1 |     10 |  45960 |00:00:00.30 |   63112 |
|   6 |       TABLE ACCESS BY INDEX ROWID  | CF10           |      1 |      5 |  20953 |00:00:00.02 |     253 |
|*  7 |        INDEX RANGE SCAN            | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      60 |
|*  8 |       INDEX RANGE SCAN             | CF11_PK        |  20953 |      2 |  45960 |00:00:00.21 |   62859 |
|   9 |      TABLE ACCESS BY INDEX ROWID   | CF11           |  45960 |      2 |  45960 |00:00:00.19 |   21388 |
|* 10 |     INDEX UNIQUE SCAN              | CF01_PK        |  45960 |      1 |  45960 |00:00:00.11 |   45960 |
|  11 |    TABLE ACCESS BY INDEX ROWID     | CF01           |  45960 |      1 |  45960 |00:00:00.12 |   45960 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(:1<=:2)
   7 - access("A"."SLE_DT">=:1 AND "A"."SLE_DT"<=:2)
   8 - access("B"."SLE_NO"="A"."SLE_NO")
  10 - access("C"."ITM_NO"="B"."ITM_NO")

[V$SQL · V$SQL_OPTIMIZER_ENV — SQL_ID 6t1d0c4m8z2qk]
 CHILD  optimizer_mode   PLAN_HASH_VALUE  EXECUTIONS  BUFFER_GETS/EXEC  SQL_PLAN_BASELINE
     0  first_rows_10         3871190422          31           176,420  (없음)
     1  all_rows               912774650           1             8,464  (없음)
 (자식 1 은 선배가 SQL Developer 에서 같은 글자로 한 번 돌린 것 · V$SQL_SHARED_CURSOR : OPTIMIZER_MODE_MISMATCH = Y)

[자식 1 — ALL_ROWS 세션]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |      1 |        |  45960 |00:00:00.29 |    8464 |          |
|*  1 |  FILTER                            |                |      1 |        |  45960 |00:00:00.29 |    8464 |          |
|*  2 |   HASH JOIN                        |                |      1 |   5858 |  45960 |00:00:00.28 |    8464 | 1236K (0)|
|   3 |    TABLE ACCESS FULL               | CF01           |      1 |    109 |    109 |00:00:00.01 |       7 |          |
|*  4 |    HASH JOIN                       |                |      1 |   5858 |  45960 |00:00:00.25 |    8457 | 1653K (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10       |      1 |   2673 |  20953 |00:00:00.02 |     253 |          |
|*  6 |      INDEX RANGE SCAN              | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      60 |          |
|   7 |     TABLE ACCESS FULL              | CF11           |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
------------------------------------------------------------------------------------------------------------------------
(176K = 176,420 · 130K = 130,460)

문제

같은 SQL_ID에 자식 커서가 둘이고 계획이 다른 이유를 쓰고, ERP 세션의 계획이 끝까지 받는 추출에 왜 나쁜지 실행계획으로 설명하시오.
SQL과 ERP의 세션 설정을 하나도 바꾸지 않고 ERP 세션이 자식 1의 계획을 쓰게 하시오. 적용됐는지 확인하는 방법과 이 방법을 유지할 때 주의할 점도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 자식 1(ALL_ROWS · 912774650)의 계획만 골라 이 문장의 베이스라인으로 올린다
--    plan_hash_value 를 빼면 자식 0 의 NL 까지 함께 받아들여진다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => '6t1d0c4m8z2qk',
                                             plan_hash_value => 912774650);
END;
/

-- ② 확인 : 받아들인 계획이 하나인지, ERP 세션의 다음 실행 Note 에 베이스라인이 뜨는지
SELECT sql_handle, plan_name, origin, enabled, accepted, fixed
  FROM DBA_SQL_PLAN_BASELINES
 WHERE sql_text LIKE 'SELECT a.SLE_NO, a.SLE_DT, a.SHP_NO%';

-- ③ SQL 과 ERP 의 세션 설정은 그대로 (자식 0 은 다음 하드 파싱 때 베이스라인으로 다시 만들어진다)

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

xplan

[ERP 세션(first_rows_10) — 베이스라인을 올린 뒤]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                |      1 |        |  45960 |00:00:00.30 |    8464 |          |
|*  1 |  FILTER                            |                |      1 |        |  45960 |00:00:00.30 |    8464 |          |
|*  2 |   HASH JOIN                        |                |      1 |   5858 |  45960 |00:00:00.29 |    8464 | 1236K (0)|
|   3 |    TABLE ACCESS FULL               | CF01           |      1 |    109 |    109 |00:00:00.01 |       7 |          |
|*  4 |    HASH JOIN                       |                |      1 |   5858 |  45960 |00:00:00.26 |    8457 | 1653K (0)|
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10       |      1 |   2673 |  20953 |00:00:00.02 |     253 |          |
|*  6 |      INDEX RANGE SCAN              | IX_CF10_SLE_DT |      1 |   2673 |  20953 |00:00:00.01 |      60 |          |
|   7 |     TABLE ACCESS FULL              | CF11           |      1 |   1096K|   1096K|00:00:00.11 |    8204 |          |
------------------------------------------------------------------------------------------------------------------------

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

[DBA_SQL_PLAN_BASELINES]
 SQL_HANDLE            PLAN_NAME                        ORIGIN                          ENABLED  ACCEPTED  FIXED
 SQL_6c1a8e2f0b7d43c9  SQL_PLAN_6g2c4h1r9xw7m3a1f5d20   MANUAL-LOAD-FROM-CURSOR-CACHE   YES      YES       NO

모범 답안 서술

자식 0과 1은 글자가 같은 한 문장(SQL_ID 6t1d0c4m8z2qk)이다. 다른 것은 세션의 optimizer_mode 하나뿐이고, 그래서 OPTIMIZER_MODE_MISMATCH로 자식이 갈라져 계획을 따로 세웠다. ERP 세션은 FIRST_ROWS_10이라 첫 10행을 가장 빨리 내는 계획, 곧 인덱스와 NL로 이어지는 계획을 골랐다(E-Rows가 10, 5인 것이 그 계산이다). 화면이라면 맞는 판단이다. 하지만 이 추출은 45,960행을 끝까지 받는다. 그러자 판매 20,953건마다 CF11_PK를 찾아가고(8번 줄 Starts 20,953, 62,859블록), 판매상품 45,960줄마다 CF01_PK와 CF01을 다시 찾아갔다(10, 11번 줄 Starts 45,960). Buffers는 176K(176,420)다. ALL_ROWS 세션의 자식 1은 CF11을 한 번 통째로 읽어 해시 조인하므로 8,464블록이다. SQL과 세션 설정을 못 바꾸므로 계획을 문장에 붙인다. SQL plan baseline은 세션이 아니라 정규화한 텍스트의 서명에 붙는다. 같은 글자의 문장이므로 자식 1의 계획을 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id, plan_hash_value => 912774650)로 올리면 ERP 세션의 문장에도 그대로 붙는다. 받아들인 계획은 Outline 힌트 묶음으로 저장되고 그 안에 ALL_ROWS와 해시 조인 순서가 들어 있으므로, 세션의 FIRST_ROWS_10과 상관없이 이 문장만 그 계획으로 돈다. plan_hash_value를 빼면 자식 0의 NL까지 함께 받아들여지므로 반드시 골라서 올린다. 확인은 ERP 세션의 다음 실행에서 한다. 계획의 Note에 SQL plan baseline ... used for this statement가 뜨고 Buffers가 176K → 8,464가 되는지, DBA_SQL_PLAN_BASELINES에 받아들인(ACCEPTED YES) 계획이 하나뿐인지 본다. 서른 명이면 하루 약 529만 → 25만 블록이다. 주의할 점이 있다. 업체 패치로 SQL 글자가 바뀌면 서명이 달라져 베이스라인이 붙지 않으므로, 패치 때마다 다시 확인해야 한다. ERP 세션이 새 계획을 만들면 대기 줄(ACCEPTED NO, plan history)에 쌓이니 정기 점검에서 본다. 베이스라인 대장에는 "업체 지침의 FIRST_ROWS_10을 이 문장에서만 이긴다"는 이유와 그때의 규모를 적는다.

해설

다른 길과 그 대가: SQL 프로파일로도 ALL_ROWS를 붙일 수 있지만 튜닝 팩이 있어야 하고, 팩 설정이 바뀌면 에러 없이 사라진다(18화 §5). 로그온 트리거로 ERP 세션의 설정을 바꾸면 업체 지침을 어기고 ERP의 다른 화면까지 바뀐다.
함정: SQL Developer에서 바인드를 리터럴로 바꿔 돌리면 글자가 달라 다른 SQL_ID가 된다. 그 계획을 올리려면 sql_handle이나 sql_text를 따로 넘겨야 한다(OPT-042). 여기서는 같은 글자로 돌렸기 때문에 SQL_ID만으로 된다.
베이스라인은 통계를 고치지 않는다. E-Rows 2,673(실제 20,953)은 문자 날짜의 수직선 탓이고 그대로 남는다(10화 §12).
베이스라인이 막아 주는 것은 이 문장 하나다. ERP의 다른 추출도 같은 증상이면 문장마다 따로 올려야 하므로, Gets 상위 목록에서 ERP 세션의 FIRST_ROWS 자식을 찾아 대상 목록을 만든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황