라이선스 점검으로 설정 하나를 바꾼 다음 날 고객 주문 내역 API만 다섯 배 느려졌다

기초SELECT이커머스634 / 700

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

상황

이커머스 운영팀은 라이선스 점검에서 Tuning Pack을 계약하지 않았다는 것을 확인하고, 어제 저녁 CONTROL_MANAGEMENT_PACK_ACCESS를 DIAGNOSTIC+TUNING에서 DIAGNOSTIC으로 바꿨다. 에러는 하나도 나지 않았다. 오늘 아침부터 「고객 주문 내역」 API(하루 약 2만 번)의 응답이 느려졌다. 다른 API는 그대로다. 소스에는 2년 전 외주 개발자가 넣은 힌트가 있고, 작년 7월 성능 점검 기록에는 "튜닝 어드바이저 권고 1건 수용"이라는 줄이 있다.

테이블과 인덱스

orders: 34,908행 · 193블록 · PK(order_id)만 · 고객당 평균 6.7건
order_items: 73,498행 · 290블록 · PK(order_item_id)만(order_id 인덱스 없음) · 주문당 평균 2.1줄
products: 280행 · 8블록 · PK(product_id)만
DBA_SQL_PROFILES: SYS_SQLPROF_0189c2f7a5d30001 · STATUS ENABLED · 작년 7월 생성

원본 SQL

sql

SELECT /*+ LEADING(o oi) USE_NL(oi) */
       o.order_id, o.order_date, o.status, oi.product_id, p.name, oi.quantity, oi.unit_price
  FROM orders o, order_items oi, products p
 WHERE o.customer_id  = :cid
   AND oi.order_id    = o.order_id
   AND p.product_id   = oi.product_id

실행계획 학습용 예시 · 교재 18화 §5의 실측 모양

xplan

[오늘 아침 — 주문 7건인 고객]
-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |     15 |00:00:00.02 |    2231 |
|*  1 |  HASH JOIN          |             |      1 |     14 |     15 |00:00:00.02 |    2231 |
|   2 |   NESTED LOOPS      |             |      1 |     14 |     15 |00:00:00.02 |    2223 |
|*  3 |    TABLE ACCESS FULL| ORDERS      |      1 |      7 |      7 |00:00:00.01 |     193 |
|*  4 |    TABLE ACCESS FULL| ORDER_ITEMS |      7 |      2 |     15 |00:00:00.02 |    2030 |
|   5 |   TABLE ACCESS FULL | PRODUCTS    |      1 |    280 |    280 |00:00:00.01 |       8 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("P"."PRODUCT_ID"="OI"."PRODUCT_ID")
   3 - filter("O"."CUSTOMER_ID"=:CID)
   4 - filter("OI"."ORDER_ID"="O"."ORDER_ID")

(Note 절 없음)

[어제 — DBA_HIST_SQL_PLAN 의 같은 SQL_ID]  HASH JOIN 둘 (ORDERS · ORDER_ITEMS · PRODUCTS 를 한 번씩) · 평균 491블록
  Note : SQL profile "SYS_SQLPROF_0189c2f7a5d30001" used for this statement
  프로파일 안의 힌트 : OPTIMIZER_FEATURES_ENABLE(default) · IGNORE_OPTIM_EMBEDDED_HINTS

문제

에러가 하나도 없었는데 이 API만 느려진 이유를 오늘과 어제의 계획, 프로파일 안의 힌트로 설명하시오.
팩 설정은 그대로 두고 SQL을 고치시오. 고친 뒤에 정리할 것도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 소스에서 힌트를 뗀다 (프로파일이 딕셔너리에서 하던 일을 코드에서)
SELECT o.order_id, o.order_date, o.status, oi.product_id, p.name, oi.quantity, oi.unit_price
  FROM orders o, order_items oi, products p
 WHERE o.customer_id  = :cid
   AND oi.order_id    = o.order_id
   AND p.product_id   = oi.product_id;

-- ② 배포한 뒤 : 팩 없이는 쓸 수 없는 프로파일을 지우고, 남은 프로파일이 있는지 본다
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'SYS_SQLPROF_0189c2f7a5d30001');
SELECT name, status, created FROM DBA_SQL_PROFILES;

개선 후 실행계획 학습용 예시 · 교재 18화 §5의 실측 모양

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |      1 |        |     15 |00:00:00.01 |     491 |          |
|*  1 |  HASH JOIN          |             |      1 |     14 |     15 |00:00:00.01 |     491 |  933K (0)|
|*  2 |   HASH JOIN         |             |      1 |     14 |     15 |00:00:00.01 |     483 | 1012K (0)|
|*  3 |    TABLE ACCESS FULL| ORDERS      |      1 |      7 |      7 |00:00:00.01 |     193 |          |
|   4 |    TABLE ACCESS FULL| ORDER_ITEMS |      1 |  73498 |  73498 |00:00:00.01 |     290 |          |
|   5 |   TABLE ACCESS FULL | PRODUCTS    |      1 |    280 |    280 |00:00:00.01 |       8 |          |
-----------------------------------------------------------------------------------------------------

모범 답안 서술

오늘 계획에는 Note가 없고, 어제 계획(DBA_HIST_SQL_PLAN)의 Note에는 SQL profile "SYS_SQLPROF_0189c2f7a5d30001" used가 있었다. SQL 프로파일은 튜닝 팩 기능이다. CONTROL_MANAGEMENT_PACK_ACCESS를 DIAGNOSTIC으로 내리자 프로파일은 에러 없이 무시되었다. DBA_SQL_PROFILES에는 여전히 ENABLED로 남아 있어 겉으로는 아무 일도 없어 보인다. 프로파일 안의 힌트는 IGNORE_OPTIM_EMBEDDED_HINTS였다. "소스에 박힌 힌트를 무시하라"는 뜻이다. 작년 7월부터 이 프로파일이 2년 전의 LEADING(o oi) USE_NL(oi)를 가려 해시 조인으로 돌았던 것이다(491블록). 프로파일이 사라지자 힌트가 다시 살아났다. order_items에는 order_id 인덱스가 없으므로, NL은 고객의 주문 7건마다 order_items를 통째로 읽는다(4번 줄 Starts 7 × 290 = 2,030블록, 합계 2,231블록). 하루 2만 번이면 약 982만 → 4,462만 블록으로 다섯 배에 가깝다. 팩 설정은 계약대로 두고, 프로파일이 딕셔너리에서 하던 일을 코드에서 한다. 소스의 LEADING(o oi) USE_NL(oi)를 떼면 옵티마이저가 세 테이블을 한 번씩 읽는 해시 조인을 골라 Buffers는 2,231 → 491이다. 배포한 뒤에는 쓸모가 없어진 프로파일을 DROP_SQL_PROFILE로 지우고, DBA_SQL_PROFILES에 남은 다른 프로파일이 있는지 찾아 같은 방식으로 정리한다. 팩 없이 남아 있는 프로파일은 "켜져 있지만 쓰이지 않는" 상태라 다음 사람을 속인다.

해설

함정: "에러가 없었으니 영향이 없다"는 판단이 틀렸다. 팩 설정을 내리면 튜닝 어드바이저 과제 만들기는 ORA-13717로 막히지만, 받아 둔 프로파일은 에러 없이 꺼진다(18화 §5 실측). 막히는 것과 조용히 사라지는 것을 나눠 봐야 한다.
코드를 바로 못 고치면: SQL Plan Management는 튜닝 팩 기능이 아니다. 힌트를 뗀 글자를 시험 세션에서 한 번 돌려 해시 계획을 만든 뒤, 그 계획을 이 SQL의 글자(sql_text)로 베이스라인에 올리면 배포 전까지 버틸 수 있다(OPT-042의 방법).
더 줄이려면: order_items (order_id) 인덱스가 있으면 고객의 주문 7건만 찾아가 수십 블록이 된다. 다만 이 DB의 인덱스와 제약 상태는 18화 졸업 과제와 이어지므로 따로 다룬다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 18화 §5의 실측 모양 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 18화 §5의 실측 모양 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황