SQLP 튜닝 연습장

테마

튜닝 연습PL/SQL과 사용자 정의 함수
PLSQL-038

명동점 관리자의 자기 매장 판매 목록이 본사 직원의 다섯 매장 전체 목록만큼 느리다

실전SELECT유통688 / 700

상황 ​

유통 본사의 판매 화면들은 로그인한 사용자가 볼 수 있는 매장만 보여 준다. 권한 확인은 공통 함수 FN_CAN_SEE(사용자, 매장)가 하고, 화면 SQL마다 WHERE 절에 FN_CAN_SEE(:usr, h.SHP_NO) = 'Y'가 붙어 있다. 명동점 관리자가 한 달 판매 목록을 열면 자기 매장 4,176건이 나오는 데 0.6초가 걸린다. 다섯 매장 20,864건을 보는 본사 직원과 시간이 같다. 유통지원팀장은 함수가 붙은 SQL 세 개를 보고, 고칠 것과 필요한 인덱스를 정해 달라고 했다.

테이블과 인덱스 ​

  • CF10 판매: 500,000행 · 4,583블록(블록당 약 109행) · SLE_DT VARCHAR2(8) · 날짜순으로 쌓였다 · 2023년 12월 20,864건, 그중 명동점(SHP_NO 1003) 4,176건
  • CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증 한 장에 평균 2.2행
  • 인덱스: CF10_PK (SLE_NO) · 이 문제에서는 IX_CF10_SLE_DT (SLE_DT)도 있다 · 통계 최신
  • T_USR_SHP 사용자별 조회 가능 매장(이 문제에서 쓰는 테이블): 72행 · 4블록 · PK_T_USR_SHP (USR_ID, SHP_NO) · 매장 관리자 5명은 한 곳, 지역 담당 3명은 두세 곳, 본사 직원 12명은 다섯 곳
  • 함수 안 SQL은 호출마다 PK로 2블록을 읽는다 · 판매는 하루 약 700건 들어온다

원본 SQL ​

sql
CREATE OR REPLACE FUNCTION FN_CAN_SEE(p_usr_id VARCHAR2, p_shp_no NUMBER) RETURN VARCHAR2 IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
    FROM T_USR_SHP
   WHERE USR_ID = p_usr_id
     AND SHP_NO = p_shp_no;
  RETURN CASE WHEN v_cnt > 0 THEN 'Y' ELSE 'N' END;
END;
/

-- [Q1] 판매 목록 (매장 관리자 · 지역 담당 · 본사, 하루 2,400번 · 보통 한 달)
SELECT h.SLE_NO, h.SLE_DT, h.SLE_HMS, h.SHP_NO
  FROM CF10 h
 WHERE h.SLE_DT BETWEEN :fr AND :to
   AND FN_CAN_SEE(:usr, h.SHP_NO) = 'Y'
 ORDER BY h.SLE_DT, h.SLE_HMS;

-- [Q2] 매장 상품 판매 합계 (하루 60번 · 한 달)
SELECT d.ITM_NO, SUM(d.SLE_QTY) AS qty
  FROM CF10 h, CF11 d
 WHERE h.SLE_DT BETWEEN :fr AND :to
   AND FN_CAN_SEE(:usr, h.SHP_NO) = 'Y'
   AND d.SLE_NO = h.SLE_NO
 GROUP BY d.ITM_NO;

-- [Q3] 영수증 상세 (하루 9,000번 · 한 장)
SELECT h.SLE_NO, h.SLE_DT, h.SHP_NO, d.ITM_NO, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d
 WHERE h.SLE_NO = :sle_no
   AND FN_CAN_SEE(:usr, h.SHP_NO) = 'Y'
   AND d.SLE_NO = h.SLE_NO;

실행계획 학습용 예시 ​

xplan
[Q1: DISPLAY_CURSOR (ALLSTATS LAST) · 명동점 관리자 · 2023년 12월]
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |   4176 |00:00:00.61 |     295 |          |
|   1 |  SORT ORDER BY                       |                |      1 |    208 |   4176 |00:00:00.61 |     295 |  407K (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    208 |   4176 |00:00:00.60 |     295 |          |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  20833 |  20864 |00:00:00.01 |      98 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("FN_CAN_SEE"(:USR,"H"."SHP_NO")='Y')
   3 - access("H"."SLE_DT">=:FR AND "H"."SLE_DT"<=:TO)

[함수 안 SQL (dep=1) · SQL 한 번씩 · 명동점 관리자]
          함수 호출   함수 안 SQL 블록   SQL 전체 경과   논리 읽기(합)
  [Q1]       20,864            41,728         0.61 초          42,023
  [Q2]       20,864            41,728         0.68 초          58,741
  [Q3]            1                 2            1 ms               7
  (본사 직원이 열어도 [Q1]의 함수 호출은 20,864번으로 같다)

문제 ​

  1. 명동점 관리자와 본사 직원의 [Q1]이 같은 시간이 걸리는 이유를 실행계획과 함수 호출 수로 설명하시오.
  2. [Q1] ~ [Q3]을 각각 고칠지 정하고, 고칠 SQL은 결과가 바뀌지 않게 다시 쓰시오. 필요한 인덱스를 하나로 설계하고 그 인덱스가 [Q1]과 [Q2]에서 각각 어떻게 쓰이는지 쓰시오.
  3. 권한 규칙을 함수 하나에 두던 장점을 무엇으로 이어 갈지, 고친 안의 대가와 함께 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자