시험 DB에서 빨랐던 계획을 프로파일로 옮겨 넣었는데 운영의 기간별 상품 매출은 그대로다

중급PLSQL한빛유통630 / 700

상황

정산팀의 「기간별 상품 매출」은 처음에는 하루 범위로 쓰다가 올해부터 한 달 범위로 매일 50번쯤 돈다. 운영에서는 0.05초, 시험 DB(운영 복제)에서는 0.01초가 걸린다. 개발팀은 시험 DB의 계획 Outline으로 SQL 프로파일을 만들어 운영에 넣었다. 프로파일은 붙었는데 계획은 바뀌지 않았다. 작년 11월 업그레이드 주간에는 계획을 지키려고 OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES를 일주일 동안 켜 두었다. 시험 DB로는 데이터만 복제했고 SQL 관리 객체(베이스라인, 프로파일)는 옮기지 않았다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음) · 한 달 약 2,740건 · IX_T_ORD_DT (ORD_DT)(날짜순 적재라 한 달 치가 약 25블록에 모여 있다) · PK_T_ORD (ORD_SEQ)
T_ORD_DET: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) 높이 2 · 주문당 평균 2건
통계 최신 · Tuning Pack 계약 있음 · 운영에는 이 SQL의 베이스라인이 있다(아래)

원본 SQL

sql

-- 개발팀이 운영에 넣은 것 : 시험 DB 계획의 Outline 을 프로파일로
BEGIN
  DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
    sql_text    => :q_text,          -- 아래 SQL 의 글자 그대로
    profile     => SQLPROF_ATTR('BEGIN_OUTLINE_DATA',
                                'IGNORE_OPTIM_EMBEDDED_HINTS',
                                'ALL_ROWS',
                                'INDEX_RS_ASC(@"SEL$1" "O"@"SEL$1" ("T_ORD"."ORD_DT"))',
                                'FULL(@"SEL$1" "D"@"SEL$1")',
                                'LEADING(@"SEL$1" "O"@"SEL$1" "D"@"SEL$1")',
                                'USE_HASH(@"SEL$1" "D"@"SEL$1")',
                                'END_OUTLINE_DATA'),
    name        => 'PROF_ITM_PERIOD',
    force_match => TRUE);
END;
/

SELECT d.ITM_ID, SUM(d.ORD_QTY) AS qty, SUM(d.ORD_QTY * d.UNT_PRC) AS amt
  FROM T_ORD o, T_ORD_DET d
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to
   AND d.ORD_SEQ = o.ORD_SEQ
 GROUP BY d.ITM_ID

실행계획 학습용 예시

xplan

[운영 — 6월 한 달 · 프로파일을 넣은 뒤]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    100 |00:00:00.05 |    8273 |
|   1 |  HASH GROUP BY                        |              |      1 |    100 |    100 |00:00:00.05 |    8273 |
|*  2 |   FILTER                              |              |      1 |        |   5480 |00:00:00.05 |    8273 |
|   3 |    NESTED LOOPS                       |              |      1 |   5480 |   5480 |00:00:00.04 |    8273 |
|   4 |     NESTED LOOPS                      |              |      1 |   5480 |   5480 |00:00:00.02 |    5515 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD      |      1 |   2740 |   2740 |00:00:00.01 |      35 |
|*  6 |       INDEX RANGE SCAN                | IX_T_ORD_DT  |      1 |   2740 |   2740 |00:00:00.01 |      10 |
|*  7 |      INDEX RANGE SCAN                 | PK_T_ORD_DET |   2740 |      2 |   5480 |00:00:00.01 |    5480 |
|   8 |     TABLE ACCESS BY INDEX ROWID       | T_ORD_DET    |   5480 |      2 |   5480 |00:00:00.01 |    2758 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:V_FROM<:V_TO)
   6 - access("O"."ORD_DT">=:V_FROM AND "O"."ORD_DT"<:V_TO)
   7 - access("D"."ORD_SEQ"="O"."ORD_SEQ")

Note
-----
   - SQL profile PROF_ITM_PERIOD used for this statement
   - SQL plan baseline SQL_PLAN_5qh8mbn2xw3k1c9a4e07d used for this statement

[DBA_SQL_PLAN_BASELINES — 이 SQL]
 SQL_HANDLE            PLAN_NAME                        ORIGIN        ENABLED  ACCEPTED  FIXED  CREATED     계획
 SQL_5c2f0a4e6b1d93a7  SQL_PLAN_5qh8mbn2xw3k1c9a4e07d   AUTO-CAPTURE  YES      YES       NO     2023-11-08  NL (PK_T_ORD_DET)
 SQL_5c2f0a4e6b1d93a7  SQL_PLAN_5qh8mbn2xw3k17b61f2a0   AUTO-CAPTURE  YES      NO        NO     2024-02-19  HASH (T_ORD_DET FULL)

[시험 DB — 같은 SQL · 베이스라인 없음]  HASH JOIN · T_ORD_DET FULL · Buffers 931

문제

프로파일이 붙었는데도 계획이 NL인 이유를 Note의 두 줄과 베이스라인 목록으로 설명하시오. 베이스라인의 NL이 어떻게 받아들여진 계획이 되었는지도 쓰시오.
해시 계획이 운영에서 돌도록 바로잡고, 한 SQL에 장치를 둘 겹쳐 두지 않도록 정리하시오. 하루 범위로 부를 때는 어떻게 되는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 대기 줄의 HASH(2024-02-19)를 시험 운행하고, 통과하면 받아들인다
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
  n NUMBER;
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_5c2f0a4e6b1d93a7',
                                   plan_name  => 'SQL_PLAN_5qh8mbn2xw3k17b61f2a0');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
  DBMS_OUTPUT.PUT_LINE(DBMS_SPM.REPORT_EVOLVE_TASK(task_name => t));
  -- 리포트가 'passed the benefit criterion' 이면
  n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => t);
END;
/

-- ② 같은 SQL 에 겹친 장치를 걷어 낸다 (계획을 지키는 일은 베이스라인 하나로)
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROF_ITM_PERIOD');

-- ③ NL 계획은 지우지 않는다 : 받아들인 계획 둘 가운데 비용이 싼 쪽을 고른다

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

xplan

[진화 리포트 FINDINGS]
  The plan was verified in 0.11000 seconds. It passed the benefit criterion
  because its verified performance was 8.88611 times better than that of the
  baseline plan.

[운영 — 6월 한 달 · 진화와 프로파일 정리 뒤]
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    100 |00:00:00.01 |     931 |          |
|   1 |  HASH GROUP BY                        |              |      1 |    100 |    100 |00:00:00.01 |     931 | 1328K (0)|
|*  2 |   FILTER                              |              |      1 |        |   5480 |00:00:00.01 |     931 |          |
|*  3 |    HASH JOIN                          |              |      1 |   5480 |   5480 |00:00:00.01 |     931 | 1561K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD       |      1 |   2740 |   2740 |00:00:00.01 |      35 |          |
|*  5 |      INDEX RANGE SCAN                 | IX_T_ORD_DT  |      1 |   2740 |   2740 |00:00:00.01 |      10 |          |
|   6 |     TABLE ACCESS FULL                 | T_ORD_DET    |      1 |    200K|    200K|00:00:00.01 |     896 |          |
--------------------------------------------------------------------------------------------------------------------

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

모범 답안 서술

Note에 두 줄이 함께 있다. 프로파일도 쓰였고 베이스라인도 쓰였다는 뜻이다. 옵티마이저는 먼저 프로파일의 힌트로 계획을 만들었고 결과는 해시였다. 그런데 이 SQL에는 베이스라인이 있고, 받아들인(ACCEPTED YES) 계획은 2023-11-08의 NL 하나뿐이다. SQL Plan Management는 새로 만든 계획이 받아들인 계획이 아니면 쓰지 않고, 받아들인 계획 가운데에서 고른다. 그래서 프로파일로 만든 해시는 쓰이지 못하고 NL이 돌았다. 베이스라인이 마지막 관문이다. NL은 작년 11월 업그레이드 주간에 켜 둔 자동 캡처가 잡은 계획이다. 그때 이 화면은 하루 범위로 쓰였고, 하루치 91건이라면 주문마다 PK_T_ORD_DET를 찾아가는 NL이 맞았다. 두 번째 실행에 자동으로 받아들여진 뒤, 한 달 범위로 부르기 시작한 2월에 옵티마이저가 만든 해시는 대기 줄(ACCEPTED NO)에 들어갔다. 지금 한 달 범위의 NL은 주문 2,740건마다 상세를 찾아가 8,273블록을 읽는다. 바로잡는 길은 프로파일이 아니라 베이스라인 안에 있다. 대기 줄의 해시를 진화 작업(CREATE_EVOLVE_TASK → EXECUTE_EVOLVE_TASK → IMPLEMENT_EVOLVE_TASK)으로 시험 운행하면 8.9배 낫다고 통과하고 받아들여진다. 그러면 한 달 범위의 Buffers는 8,273 → 931이다. 프로파일은 지운다. 한 SQL에 장치가 둘이면 다음 사람이 어느 쪽이 계획을 정하는지 알 수 없다. NL은 지우지 않는다. 받아들인 계획이 둘이면 옵티마이저는 그 가운데 비용이 싼 쪽을 고르므로, 하루 범위로 하드 파싱되면 NL을, 한 달 범위면 해시를 쓴다. 다만 바인드 피킹으로 한 번 만든 커서는 다시 쓰이므로 값에 따라 계획이 바로 갈리지는 않는다. 시험 DB가 빨랐던 이유도 여기 있다. 시험 DB에는 베이스라인이 없어 옵티마이저가 고른 해시가 그대로 돌았다. 운영과 조건이 다른 곳에서 튜닝한 셈이다. 튜닝을 시작하기 전에 운영의 DBA_SQL_PLAN_BASELINES, DBA_SQL_PROFILES와 계획의 Note부터 본다.

해설

반대의 경우: 프로파일을 ACCEPT_SQL_PROFILE로 받아들일 때 베이스라인이 이미 있으면, 그 계획을 베이스라인에 받아들인 계획으로 함께 넣는다고 문서에 적혀 있다. 이번처럼 IMPORT_SQL_PROFILE로 넣으면 그런 일이 없어 대기 줄에 머문다. 어느 쪽이든 Note와 베이스라인 목록으로 확인해야 한다.
진화의 몫: 진화는 두 계획을 실제로 돌려 비교한다. 업무 시간에 큰 SQL을 진화하면 그만큼 일을 더 한다. 이 SQL은 0.1초 남짓이라 바로 돌려도 된다(18화 §2).
자동 캡처를 오래 켜 두면 그 주에 돈 모든 반복 SQL의 그때 계획이 받아들인 계획이 된다. 그때의 바인드와 규모가 계획을 정했다는 사실을 베이스라인 대장에 적어 두어야 한다(OPT-004).
시험 DB로 SQL 관리 객체를 옮기려면 DBMS_SPM.PACK_STGTAB_BASELINE과 UNPACK_STGTAB_BASELINE을 쓴다. 운영과 같은 조건이어야 시험 결과를 믿을 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황