테마
Skip to content
튜닝 연습PL/SQL과 사용자 정의 함수
PLSQL-039
매장 앱의 「이번 달 우리 매장」이 생긴 뒤로 매출 공통 함수 안의 SQL이 AWR 논리 읽기 1위에 올랐다
상황
버거 본사는 2022년에 매장별 일 주문 수를 구하는 공통 함수 PKG_SALES.FN_SHOP_DAY를 만들었다. 직원 식사 주문을 빼는 보정 규칙(FN_STAFF_MEAL, PL/SQL)이 들어 있어서, 매출 화면은 모두 이 파이프라인 함수를 TABLE()로 읽는다.
올해 매장 앱에 「이번 달 우리 매장」 화면이 생기자 함수 안 SQL이 AWR 논리 읽기 1위에 올랐다. 매장 200곳이 하루 약 30번씩 연다. 본사는 함수를 쓰는 SQL 세 개를 보고 무엇을 바꿀지 정해 달라고 했다.
테이블과 인덱스
ORD주문: 1,000,000행 · 22,422블록 ·ORD_DTVARCHAR2(8) ·ORD_HMSVARCHAR2(6) ·SHOP_NO'SH0001'(매장 200곳) · 2013년 12월 41,712건 · 매장 하나의 한 달 약 209건- 인덱스:
ORD_PK (ORD_NO)·ORD_X01 (ORD_DT, ORD_HMS)리프 3,774블록 · CF 22,223 · 통계 최신 SHOP매장: 200행 · 5블록 · PK(SHOP_NO) · 이름이 같은 매장이 있다(SH0070과SH0089는 둘 다 여의도점)- SQL 타입
T_SHOP_DAY_OBJ (SHOP_NO, ORD_DT, ORD_CNT, ADJ_CNT)와T_SHOP_DAY_TAB·FN_STAFF_MEAL은 SQL이 없는 패키지 함수다(매장별 직원 수는 패키지 배열) - 주문은 POS에서 하루 약 1,370건 INSERT된다
원본 SQL
sql
CREATE OR REPLACE PACKAGE BODY PKG_SALES IS
FUNCTION FN_SHOP_DAY(p_fr VARCHAR2, p_to VARCHAR2) RETURN T_SHOP_DAY_TAB PIPELINED IS
BEGIN
FOR r IN (SELECT SHOP_NO, ORD_DT, COUNT(*) AS ORD_CNT,
COUNT(CASE WHEN ORD_HMS >= '213000' THEN 1 END) AS LATE_CNT
FROM ORD
WHERE ORD_DT BETWEEN p_fr AND p_to
GROUP BY SHOP_NO, ORD_DT) LOOP
PIPE ROW (T_SHOP_DAY_OBJ(r.SHOP_NO, r.ORD_DT, r.ORD_CNT,
r.ORD_CNT - FN_STAFF_MEAL(r.SHOP_NO, r.ORD_DT, r.LATE_CNT)));
END LOOP;
RETURN;
END;
-- FN_STAFF_MEAL 생략
END;
/
-- [S1] 매장 앱 「이번 달 우리 매장」 (하루 약 6,000번)
SELECT ORD_DT, ADJ_CNT
FROM TABLE(PKG_SALES.FN_SHOP_DAY(:fr, :to))
WHERE SHOP_NO = :shop_no
ORDER BY ORD_DT;
-- [S2] 본사 「일별 전국 주문」 (하루 약 30번)
SELECT ORD_DT, SUM(ADJ_CNT) AS adj_cnt
FROM TABLE(PKG_SALES.FN_SHOP_DAY(:fr, :to))
GROUP BY ORD_DT
ORDER BY ORD_DT;
-- [S3] 본사 「매장 순위」 (하루 약 10번)
SELECT s.SHOP_NO, s.SHOP_NM, SUM(t.ADJ_CNT) AS adj_cnt
FROM TABLE(PKG_SALES.FN_SHOP_DAY(:fr, :to)) t, SHOP s
WHERE s.SHOP_NO = t.SHOP_NO
GROUP BY s.SHOP_NO, s.SHOP_NM
ORDER BY adj_cnt DESC
FETCH FIRST 10 ROWS ONLY;실행계획 학습용 예시
xplan
[S1: DISPLAY_CURSOR (ALLSTATS LAST) · SH0117 · 2013-12-01 ~ 2013-12-31]
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 31 |00:00:00.19 | 0 | |
| 1 | SORT ORDER BY | | 1 | 82 | 31 |00:00:00.19 | 0 | 2048 (0)|
|* 2 | COLLECTION ITERATOR PICKLER FETCH| FN_SHOP_DAY | 1 | 82 | 31 |00:00:00.19 | 0 | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)=:SHOP_NO)
[함수 안 SQL (dep=1) · S1 한 번]
SELECT SHOP_NO, ORD_DT, COUNT(*) … FROM ORD WHERE ORD_DT BETWEEN :B2 AND :B1 GROUP BY SHOP_NO, ORD_DT
ORD_X01 범위 41,712행 · Buffers 1,086 · 만든 줄(PIPE ROW) 6,200 · FN_STAFF_MEAL 6,200번
[AWR 하루] S1 실행 6,012번 · 함수 안 SQL Buffer Gets 6,529,032 (1위) · DB 시간 19.1분
[S2 · S3 한 번] 함수 안 SQL Buffers 1,086 · 경과 0.19 ~ 0.21 초 · S3의 함수 줄 E-Rows 8,168(실제 6,200)문제
- 매장 하나의 한 달 31줄을 보여 주는 [S1]이 매번 1,086블록을 읽고 6,200줄을 만드는 이유를 설명하시오.
- 보정 규칙(
FN_STAFF_MEAL)은 하나로 둔 채 [S1]이 자기 매장의 주문만 읽도록 고치시오. 필요한 DDL(뷰 또는 함수 변경, 인덱스)을 쓰고, [S2]와 [S3]이 어떻게 되는지도 쓰시오. - ① 함수에 매장 인자를 더하는 안, ② 파이프라인 함수를 뷰로 바꾸는 안, ③ 매장 일 집계 테이블을 밤마다 만드는 안을 견주고, 고른 안의 대가를 쓰시오.