의심 주문 열두 건의 상품을 보여 주는 점검 화면이 주문상품 5만 블록을 통째로 읽는다

중급SELECT버거684 / 700

상황

버거 본사 감사팀의 「환불 의심 주문 점검」 화면은 매장과 날짜를 고르면 의심 주문과 그 주문의 상품을 보여 준다. 의심 주문을 고르는 규칙(같은 계산대의 연속 취소, 마감 직전 대량 주문 등)은 PL/SQL 패키지에 있고, 파이프라인 함수 PKG_AUDIT.FN_SUSPECT_ORD가 주문 번호를 돌려준다. 의심 주문은 보통 열 건 안팎인데 화면은 2초쯤 걸린다. 감사팀은 하루 150번쯤 연다. 개발자는 "함수는 0.01초에 끝난다"라고 한다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) 높이 3 · 주문 한 건에 평균 2.5행 · 통계 최신
T_ORD_NO_TAB: SQL 컬렉션 타입(TABLE OF NUMBER) · FN_SUSPECT_ORD는 이 타입을 PIPELINED로 돌려준다
함수 안 SQL은 그 매장의 하루치 주문만 읽는다(호출 한 번에 44블록)
DB 블록 크기 8KB

원본 SQL

sql

SELECT s.COLUMN_VALUE AS ORD_NO, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM TABLE(PKG_AUDIT.FN_SUSPECT_ORD(:shop_no, :dt)) s,
       ORD_ITEM i
 WHERE i.ORD_NO = s.COLUMN_VALUE
 ORDER BY s.COLUMN_VALUE, i.ITEM_ID;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST) · :shop_no = 'SH0117', :dt = '20131224']
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |     31 |00:00:01.93 |   55265 |          |
|   1 |  SORT ORDER BY                      |                |      1 |  20197 |     31 |00:00:01.93 |   55265 |  2048  (0)|
|*  2 |   HASH JOIN                         |                |      1 |  20197 |     31 |00:00:01.93 |   55265 | 1236K (0)|
|   3 |    COLLECTION ITERATOR PICKLER FETCH| FN_SUSPECT_ORD |      1 |   8168 |     12 |00:00:00.01 |       0 |          |
|   4 |    TABLE ACCESS FULL                | ORD_ITEM       |      1 |  2472K |  2472K |00:00:00.61 |   55265 |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"=VALUE(KOKBF$))

[세션 통계: 화면 한 번]  session logical reads 55,309 (함수 안 SQL 44 포함) · 경과 1.94 초

문제

의심 주문이 12건인데 주문상품을 통째로 읽은 이유를 실행계획의 E-Rows로 설명하시오. 8,168이라는 숫자가 어디서 왔는지도 쓰시오.
의심 주문 규칙은 함수에 둔 채로 SQL을 고치시오. 고친 방법의 부작용과 다른 방법 하나를 견주어 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 함수가 돌려줄 건수를 옵티마이저에게 알려 준다 (의심 주문은 보통 10 ~ 20건)
SELECT /*+ CARDINALITY(s 20) */
       s.COLUMN_VALUE AS ORD_NO, i.ITEM_ID, i.ORD_ITEM_QTY
  FROM TABLE(PKG_AUDIT.FN_SUSPECT_ORD(:shop_no, :dt)) s,
       ORD_ITEM i
 WHERE i.ORD_NO = s.COLUMN_VALUE
 ORDER BY s.COLUMN_VALUE, i.ITEM_ID;

-- (다른 방법) 조인 순서와 방식을 직접 정한다
--   SELECT /*+ LEADING(s) USE_NL(i) INDEX(i ORD_ITEM_PK) */ ...

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

xplan

----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     31 |00:00:00.01 |      57 |          |
|   1 |  SORT ORDER BY                       |                |      1 |     50 |     31 |00:00:00.01 |      57 |  2048  (0)|
|   2 |   NESTED LOOPS                       |                |      1 |     50 |     31 |00:00:00.01 |      57 |          |
|   3 |    NESTED LOOPS                      |                |      1 |     50 |     31 |00:00:00.01 |      39 |          |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| FN_SUSPECT_ORD |      1 |     20 |     12 |00:00:00.01 |       0 |          |
|*  5 |     INDEX RANGE SCAN                 | ORD_ITEM_PK    |     12 |      2 |     31 |00:00:00.01 |      39 |          |
|   6 |    TABLE ACCESS BY INDEX ROWID       | ORD_ITEM       |     31 |      2 |     31 |00:00:00.01 |      18 |          |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("I"."ORD_NO"=VALUE(KOKBF$))

[세션 통계: 화면 한 번]  session logical reads 101 (함수 안 SQL 44 포함) · 경과 0.01 초

모범 답안 서술

3번 줄 COLLECTION ITERATOR PICKLER FETCH의 E-Rows는 8,168인데 A-Rows는 12다. 옵티마이저는 테이블 함수가 몇 건을 돌려줄지 알 수 없다. 함수 안을 들여다보지 않고, 통계도 없으므로 블록 크기로 정해진 기본값(8KB 블록에서 8,168건)으로 잡는다. 주문 8,168건이면 주문상품은 2만 행쯤(E-Rows 20,197)이다. 그만큼을 NL 조인으로 ORD_ITEM_PK에서 하나씩 찾느니 주문상품을 FULL로 읽어 해시 조인하는 쪽이 싸다고 판단한 것이다. 그래서 의심 주문 12건을 보려고 주문상품 247만 행(55,265블록)을 읽었다. 함수가 0.01초에 끝나는 것은 맞지만, 문제는 함수가 아니라 함수의 건수를 모르는 옵티마이저였다. /*+ CARDINALITY(s 20) */로 함수가 20건 안팎을 돌려준다고 알려 주면 옵티마이저가 NL 조인을 고른다. 주문 번호 12개마다 ORD_ITEM_PK를 찾아가 31행을 읽고 테이블은 31번만 찾는다(5번 줄 Starts 12). Buffers는 55,265 → 57, 경과는 1.93 → 0.01초가 된다. 의심 주문 규칙은 함수에 그대로 남는다. 부작용은 20이 고정값이라는 것이다. 규칙이 바뀌어 의심 주문이 수천 건으로 많아지면 NL이 오히려 느려지는데, 힌트는 그 사실을 모른다. 또 CARDINALITY는 문서화되지 않은 힌트라서 버전이 바뀔 때 다시 확인해야 한다. 다른 방법으로 /*+ DYNAMIC_SAMPLING(s 2) */를 쓰면 하드 파싱 때 함수를 한 번 더 불러 실제 건수를 센다. 건수가 날마다 달라도 맞게 추정하지만, 파싱 때마다 함수가 한 번 더 돈다. LEADING(s) USE_NL(i)로 조인을 직접 정하는 방법은 건수와 상관없이 늘 NL이라 더 단단하지만, 역시 의심 주문이 많아지는 날에는 맞지 않는다.

해설

8,168은 어디서 오나: 통계가 없는 컬렉션에 옵티마이저가 쓰는 기본 건수이고, 블록 크기에 따라 정해진다(8KB 블록에서 8,168). 함수가 무엇을 하든 이 숫자로 시작한다. 함수 안 SQL이 빠른지는 이 문제와 상관이 없다.
힌트 유효기간: CARDINALITY(s 20)은 "의심 주문은 20건 안팎"이라는 오늘의 전제를 박아 둔 것이다. 힌트 대장에 그 전제를 적고, 규칙을 바꾸는 날 다시 잰다(교재 06화).
규칙을 SQL로 쓸 수 있다면 함수 대신 서브쿼리로 쓰는 것이 근본 해결이다. 옵티마이저가 ORD 통계로 건수를 추정한다. 여기서는 규칙이 PL/SQL에만 있어 함수를 둔다.
확장 옵티마이저(ASSOCIATE STATISTICS … USING 인터페이스 타입)로 함수가 자기 건수를 알려 주게 할 수도 있다. 가장 정확하지만 만들고 관리할 것이 많다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황