어드바이저 권고 프로파일을 받아들였는데 이튿날 밤 정산 대사가 다시 느리다

기초PLSQL한빛유통628 / 700

상황

정산팀은 외부 업체의 정산 대사 솔루션을 쓴다. 솔루션은 결제일을 리터럴로 박은 SQL을 날짜마다 새로 만들어 보내고, SQL 맨 앞에는 업체가 넣은 힌트가 있다. 매일 밤 지난 30일을 하루씩 다시 대사하므로 같은 모양의 문장이 30개 돈다. 그제 DBA가 6월 15일 문장에 튜닝 어드바이저를 돌려 권고 프로파일을 받아들였다. 그 문장은 0.58초 → 0.01초가 됐다. 그런데 어젯밤 대사에서는 6월 15일 문장만 빨랐고 나머지 29개는 그대로였다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) 높이 2 · PAY_DT DATE(시분초 있음) · 결제일 하루 약 90건 · 하루치 결제는 90블록에 흩어져 있다
인덱스 IX_T_ORD_PAY_DT (PAY_DT)
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) 높이 2 · 주문당 평균 2건
Tuning Pack 계약 있음(CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING) · CURSOR_SHARING = EXACT

원본 SQL

sql

-- 솔루션이 날짜마다 만들어 보내는 문장 (6월 16일 판)
SELECT /*+ ORDERED USE_NL(o) */
       o.ORD_SEQ, o.PAY_DT, o.PAY_TP, o.PAY_AMT, d.ITM_ID, d.ORD_QTY, d.UNT_PRC
  FROM T_ORD_DET d, T_ORD o
 WHERE o.ORD_SEQ = d.ORD_SEQ
   AND o.PAY_DT >= TO_DATE('20240616', 'YYYYMMDD')
   AND o.PAY_DT <  TO_DATE('20240617', 'YYYYMMDD');

-- 그제 DBA 가 받아들인 권고
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'TASK_RECON_0615', name => 'PROF_RECON_0615');

실행계획 학습용 예시 · 교재 18화의 프로파일 모양

xplan

[어젯밤 — 6월 16일 문장]
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |    182 |00:00:00.58 |     601K|
|   1 |  NESTED LOOPS                |           |      1 |    180 |    182 |00:00:00.58 |     601K|
|   2 |   NESTED LOOPS               |           |      1 |    200K|    200K|00:00:00.36 |     401K|
|   3 |    TABLE ACCESS FULL         | T_ORD_DET |      1 |    200K|    200K|00:00:00.02 |     896 |
|*  4 |    INDEX UNIQUE SCAN         | PK_T_ORD  |    200K|      1 |    200K|00:00:00.21 |     400K|
|*  5 |   TABLE ACCESS BY INDEX ROWID| T_ORD     |    200K|      1 |    182 |00:00:00.19 |     200K|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."ORD_SEQ"="D"."ORD_SEQ")
   5 - filter(("O"."PAY_DT">=TO_DATE(' 2024-06-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."PAY_DT"<TO_DATE(' 2024-06-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[같은 밤 — 6월 15일 문장]  IX_T_ORD_PAY_DT → PK_T_ORD_DET 의 NL · Buffers 381
  Note
  -----
     - SQL profile PROF_RECON_0615 used for this statement

[DBA_SQL_PROFILES]
 NAME              CATEGORY  STATUS   FORCE_MATCHING  CREATED
 PROF_RECON_0615   DEFAULT   ENABLED  NO              06-17 09:12
 (프로파일 안의 힌트 : OPTIMIZER_FEATURES_ENABLE(default) · IGNORE_OPTIM_EMBEDDED_HINTS)

[V$SQL — 어젯밤 대사 문장 가운데 셋]
 결제일  SQL_ID         EXACT_MATCHING_SIGNATURE  FORCE_MATCHING_SIGNATURE  SQL_PROFILE       BUFFER_GETS
 06-15   4m1kq8z0v7c2a   1438207716409352211       8806217430295501273       PROF_RECON_0615           381
 06-16   9cz2w5r1h3n8d   6192774035518093160       8806217430295501273       (없음)                601,014
 06-17   2bx6t0y4p9m1f   3370951182604277725       8806217430295501273       (없음)                600,988

문제

프로파일이 6월 15일 문장에만 붙은 이유를 V$SQL의 두 서명 칸으로 설명하시오. 프로파일이 붙은 문장이 왜 빨라졌는지도 쓰시오.
30개 문장 모두에 프로파일이 붙도록 고치고, 이 방법의 한계와 근본 해결을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 날짜 하나에만 붙은 프로파일을 지우고
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROF_RECON_0615');

-- ② 같은 권고를 '리터럴만 다른 문장 전부'에 붙게 다시 받아들인다 (FORCE_MATCHING_SIGNATURE 로 맞춘다)
BEGIN
  DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name   => 'TASK_RECON_0615',
                                  name        => 'PROF_RECON',
                                  force_match => TRUE);
END;
/

-- ③ 확인 : 오늘 밤 30개 문장의 SQL_PROFILE 칸
SELECT sql_id, force_matching_signature, sql_profile, buffer_gets / executions AS gets_per_exec
  FROM V$SQL
 WHERE force_matching_signature = 8806217430295501273;

개선 후 실행계획 학습용 예시 · 교재 18화의 프로파일 모양

xplan

[6월 16일 문장 — FORCE_MATCH 프로파일 뒤]
--------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                 |      1 |        |    182 |00:00:00.01 |     383 |
|   1 |  NESTED LOOPS                         |                 |      1 |    180 |    182 |00:00:00.01 |     383 |
|   2 |   NESTED LOOPS                        |                 |      1 |    180 |    182 |00:00:00.01 |     202 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD           |      1 |     91 |     91 |00:00:00.01 |      93 |
|*  4 |     INDEX RANGE SCAN                  | IX_T_ORD_PAY_DT |      1 |     91 |     91 |00:00:00.01 |       2 |
|*  5 |    INDEX RANGE SCAN                   | PK_T_ORD_DET    |     91 |      2 |    182 |00:00:00.01 |     109 |
|   6 |   TABLE ACCESS BY INDEX ROWID         | T_ORD_DET       |    182 |      2 |    182 |00:00:00.01 |     181 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."PAY_DT">=TO_DATE(' 2024-06-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."PAY_DT"<TO_DATE(' 2024-06-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

Note
-----
   - SQL profile PROF_RECON used for this statement

모범 답안 서술

V$SQL에서 세 문장의 EXACT_MATCHING_SIGNATURE는 모두 다르고 FORCE_MATCHING_SIGNATURE는 모두 같다. 프로파일은 기본(force_match FALSE)으로는 리터럴까지 같은 문장, 곧 EXACT_MATCHING_SIGNATURE가 같은 문장에만 붙는다. 솔루션이 결제일 리터럴을 날마다 바꿔 박으므로 30개는 글자가 다른 30개의 문장이고, 그제 받아들인 프로파일은 6월 15일 문장 하나에만 붙었다(DBA_SQL_PROFILES의 FORCE_MATCHING NO). 프로파일이 붙은 문장이 빨라진 이유는 그 안의 IGNORE_OPTIM_EMBEDDED_HINTS 때문이다. 업체 힌트 ORDERED USE_NL(o)는 T_ORD_DET 20만 줄을 먼저 읽고 줄마다 PK_T_ORD와 T_ORD를 찾아가게 한다(4, 5번 줄 Starts 200K, Buffers 601K). 프로파일이 그 힌트를 무시하게 하자 옵티마이저는 결제일 인덱스로 하루치 91건만 찾고 상세를 붙이는 NL을 골랐다(381블록). FORCE_MATCHING_SIGNATURE는 리터럴을 바인드처럼 바꿔 셈한 서명이라 30개가 같다. 그래서 같은 권고를 force_match => TRUE로 다시 받아들이면 리터럴만 다른 문장 모두에 붙는다. 하룻밤 30문장이 약 1,803만 → 1만 1천 블록이 된다. 한계도 있다. SQL plan baseline은 리터럴만 다른 문장을 하나로 묶지 못하므로(정확히 같은 글자 단위) 여기서는 프로파일이 맞는 도구다. 문장에 리터럴과 바인드 변수가 섞여 있으면 force matching이 리터럴을 바꾸지 않아 붙지 않는다. 프로파일은 튜닝 팩 기능이라 라이선스와 팩 설정에 기댄다. 근본 해결은 업체에 힌트 제거와 바인드 변수 사용을 요청하는 것이고, 프로파일은 그때까지의 조치로 대장에 적는다.

해설

CURSOR_SHARING = FORCE로 푸는 안은 기각한다. 리터럴이 바인드로 바뀌어 프로파일과 베이스라인이 붙기는 하지만, 인스턴스나 세션의 모든 SQL이 함께 바뀐다(11화 §8의 함정들).
force matching의 범위: 리터럴만 다르면 다른 결제일뿐 아니라 업체가 다른 조건 값으로 만든 문장에도 붙는다. 여기서는 프로파일이 "힌트를 무시하라"는 것뿐이라 괜찮지만, OPT_ESTIMATE 같은 숫자 보정이 든 프로파일을 force matching으로 넓히면 값마다 맞지 않는 보정이 붙을 수 있다.
프로파일의 정체는 06화에서 사람이 한 일(힌트 떼기)을 코드 밖에서 한 것이다. 소스에는 USE_NL이 남는다. 그래서 대장에 정체와 팩 확인란, 업체가 고칠 날짜를 함께 적는다(18화 §4).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 18화의 프로파일 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 18화의 프로파일 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황