AWR 실행 횟수 1위가 소스 어디에도 없는 상품명 조회다

실전PLSQL버거663 / 700

상황

버거 본사 DBA가 하루치 AWR을 보내왔다. 실행 횟수 1위는 SELECT ITEM_NM FROM ITEM WHERE ITEM_ID = :B1으로 하루 422만 번 실행되는데, 개발팀은 "그런 SQL은 소스에 없다"라고 한다. 개발팀장은 두 안을 놓고 고민 중이다. "상품명 함수에 RESULT_CACHE 한 단어를 붙이면 하루 만에 끝난다"는 안과, "함수를 부르는 프로그램 31개를 조인으로 고친다"는 안이다. 튜너에게 어느 쪽으로 갈지, 무엇을 잃는지 숫자로 정해 달라고 했다.

테이블과 인덱스

ITEM 상품: 92행 · 5블록 · PK(ITEM_ID) · 분기마다 메뉴를 개편할 때만 바뀐다
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 한 달에 약 103,000행
ORD 주문: 1,000,000행 · 22,422블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS)
함수 FN_ITEM_NM: USER_DEPENDENCIES로 보면 31개 프로그램이 부른다 · 결과 캐시 1,984칸 · result_cache_mode = MANUAL
아래 세 프로그램이 하루 호출의 99%를 차지한다(V$SQL에서 문장마다 실행 수와 행 수로 셈)

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_ITEM_NM(p_item_id NUMBER) RETURN VARCHAR2 IS
  v_nm ITEM.ITEM_NM%TYPE;
BEGIN
  SELECT ITEM_NM INTO v_nm FROM ITEM WHERE ITEM_ID = p_item_id;
  RETURN v_nm;
END;
/

-- [A] 매장 관리자 「영수증 다시 보기」: 주문 한 건의 상품 (실행 한 번에 평균 2.5행 · 하루 46,000번)
SELECT i.ITEM_ID, FN_ITEM_NM(i.ITEM_ID) AS item_nm, i.ORD_ITEM_QTY
  FROM ORD_ITEM i
 WHERE i.ORD_NO = :ord_no;

-- [B] 본사 「매장별 월간 판매 상품」 화면: 매장 하나, 한 달 (실행 한 번에 평균 515행 · 하루 2,300번)
SELECT o.ORD_NO, o.ORD_DT, i.ITEM_ID, FN_ITEM_NM(i.ITEM_ID) AS item_nm, i.ORD_ITEM_QTY
  FROM ORD o, ORD_ITEM i
 WHERE o.SHOP_NO = :shop_no
   AND o.ORD_DT BETWEEN :fr AND :to
   AND i.ORD_NO = o.ORD_NO;

-- [C] 본사 「상품 판매 내역 내려받기」: 한 달 치 주문상품 (실행 한 번에 평균 103,021행 · 하루 28번)
SELECT i.ORD_NO, i.ORD_DT, i.ORD_HMS, i.ITEM_ID, FN_ITEM_NM(i.ITEM_ID) AS item_nm, i.ORD_ITEM_QTY
  FROM ORD_ITEM i
 WHERE i.ORD_DT BETWEEN :fr AND :to;

실행계획 학습용 예시

xplan

[AWR 하루: SQL ordered by Executions (1위)]
  Executions   Rows Processed   Buffer Gets   CPU (s)   Elapsed (s)   SQL Text
   4,225,812        4,225,812     8,451,624      71.3          88.6   SELECT ITEM_NM FROM ITEM WHERE ITEM_ID = :B1
  (Parse Calls 63 · 이 문장의 PROGRAM_ID → DEV.FN_ITEM_NM)

[V$SQL: FN_ITEM_NM을 부르는 문장 · 하루 증가분]
  프로그램                  실행 수   행 수(= 함수 호출)    비중
  [C] 내려받기                   28          2,884,588     68%
  [B] 매장별 화면             2,300          1,184,500     28%
  [A] 영수증 다시 보기       46,000            115,000      3%
  그 밖 28개 프로그램             …             41,724      1%

[복사본에서 잰 판: [C] 한 번 (103,021행)]
  판                          함수 본문    함수 호출   논리 읽기    경과
  그대로                        103,021      103,021     209,019   1.18 초
  스칼라 서브쿼리로 감싸기       10,163       10,163      23,303   0.16 초
  RESULT_CACHE (데운 뒤)              0      103,021       2,977   0.19 초
  ITEM과 조인                         0            0       2,982   0.07 초

[복사본에서 잰 판: [A] 한 번 (2.5행)]
  그대로 논리 읽기 10 · 스칼라 서브쿼리 10 (실행마다 캐시가 새로 시작) · RESULT_CACHE 5 · ITEM과 조인 10

문제

AWR 1위 문장이 소스에 없는 이유와, 그 문장을 누가 부르는지 찾는 방법을 설명하시오.
두 안(함수에 RESULT_CACHE, 프로그램 31개를 조인으로)을 숫자로 견주어 무엇을 언제 할지 정하고, 필요한 SQL을 쓰시오.
고른 안의 대가와, 결과 캐시를 붙여도 되는 근거를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 오늘: 상품명 함수에 결과 캐시 (답은 인자와 ITEM 에만 기댄다 · ITEM 은 분기 1회 · 답 92개)
CREATE OR REPLACE FUNCTION FN_ITEM_NM(p_item_id NUMBER) RETURN VARCHAR2 RESULT_CACHE IS
  v_nm ITEM.ITEM_NM%TYPE;
BEGIN
  SELECT ITEM_NM INTO v_nm FROM ITEM WHERE ITEM_ID = p_item_id;
  RETURN v_nm;
END;
/

-- ② 다음 배포: 행이 많은 [C] 내려받기와 [B] 매장별 화면은 ITEM 과 조인 (함수 호출 0)
--    함수는 못 찾으면 NULL 을 돌려주고 행은 남겼으므로 아우터 조인으로 같게 둔다
-- [C]
SELECT i.ORD_NO, i.ORD_DT, i.ORD_HMS, i.ITEM_ID, m.ITEM_NM, i.ORD_ITEM_QTY
  FROM ORD_ITEM i
  LEFT JOIN ITEM m ON m.ITEM_ID = i.ITEM_ID
 WHERE i.ORD_DT BETWEEN :fr AND :to;

-- [B]
SELECT o.ORD_NO, o.ORD_DT, i.ITEM_ID, m.ITEM_NM, i.ORD_ITEM_QTY
  FROM ORD o
  JOIN ORD_ITEM i  ON i.ORD_NO = o.ORD_NO
  LEFT JOIN ITEM m ON m.ITEM_ID = i.ITEM_ID
 WHERE o.SHOP_NO = :shop_no
   AND o.ORD_DT BETWEEN :fr AND :to;

-- ③ [A] 영수증 다시 보기 (실행 한 번에 2.5행): 그대로 둔다 · 결과 캐시로 함수 안 SQL 이 사라진다

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

xplan

[하루: RESULT_CACHE 반영 뒤, [C][B] 조인 배포 전]
  함수 안 SQL Executions 4,225,812 → 92 (상품마다 처음 한 번 · 메뉴 개편 날 다시 92)
  V$RESULT_CACHE_STATISTICS: Find Count 4,225,720 · Create Count Success 92 · Invalidation Count 0
  논리 읽기 하루 약 845만 블록 감소 · 함수 호출(SQL → PL/SQL) 4,225,812번은 그대로

[[C][B]를 조인으로 배포한 뒤]
  함수 호출 하루 약 15만 번 ([A]와 그 밖 프로그램) · [C] 한 번 1.18 초 → 0.07 초

모범 답안 서술

AWR 1위 문장은 애플리케이션 소스가 아니라 함수 FN_ITEM_NM 안의 SELECT다. SQL에서 부른 함수 안의 SQL은 바깥 문장과 따로 파싱되고 실행되는 재귀 SQL(dep=1)이고, 바인드 이름도 :B1으로 바뀌어 있어 소스 검색에 걸리지 않는다. V$SQL의 PROGRAM_ID가 이 함수를 가리키고, USER_DEPENDENCIES로 함수를 부르는 프로그램 31개를 찾는다. 하루 4,225,812번은 그 프로그램들이 행마다 함수를 부른 횟수의 합이다. 문장마다 실행 수와 행 수로 세어 보면 [C] 내려받기가 68%, [B] 매장별 화면이 28%, [A] 영수증이 3%다. 한 번에 2블록이라 하루 845만 블록이다. 결과 캐시는 이 함수에 맞는다. 답이 인자(ITEM_ID)와 ITEM 테이블에만 기대고, 시계, 세션 설정, NLS, 패키지 변수 같은 숨은 입력이 없다. ITEM은 분기마다 메뉴를 개편할 때만 바뀌므로 무효화도 그날뿐이다. 답은 92개라 1,984칸에 넉넉히 들어가고, 모든 세션이 함께 쓴다. 그래서 오늘 할 일은 함수에 RESULT_CACHE 한 단어를 붙이는 것이다. 31개 프로그램을 건드리지 않고 함수 안 SQL이 하루 4,225,812번 → 92번(상품마다 처음 한 번)으로 줄고, 논리 읽기도 하루 약 845만 블록 준다. 그러나 결과 캐시는 함수 안의 SQL을 없앨 뿐 함수 호출은 없애지 못한다. SQL에서 PL/SQL로 건너가 결과 캐시를 찾는 함수 호출 4,225,812번은 그대로 남는다. [C] 한 번을 재 보면 결과 캐시 판이 0.19초, ITEM과 조인한 판이 0.07초다. 호출의 96%가 [C]와 [B]에서 나오므로 다음 배포에서 이 둘을 ITEM 조인으로 바꾼다. 함수는 상품을 못 찾으면 NULL을 돌려주고 행은 남겼으므로 아우터 조인으로 쓴다. [A]는 실행 한 번에 2.5행이라 조인해도 일량이 같으니 그대로 둔다. 스칼라 서브쿼리로 감싸는 안은 이 상황에 맞지 않는다. 캐시가 실행 하나 동안만 살아서, 실행 한 번에 2.5행인 [A]에서는 실행마다 새로 시작한다. [C]에서도 92종 중 칸을 얻지 못한 상품은 나올 때마다 호출되어 10,163번이 남는다. 결과 캐시의 대가도 적어 둔다. 찾을 때마다 래치를 잡으므로 여러 세션이 같은 답을 동시에 찾으면 경합이 생긴다(교재 15b화: 두 세션이면 각자 1.6 ~ 2.1배). 실패한 답(없는 상품 번호의 NO_DATA_FOUND)은 저장되지 않아 매번 본문을 돈다. 메뉴 개편 날 ITEM을 고쳐 커밋하면 92개 답이 전부 지워지고 다시 계산된다(맞는 동작이다). 결과 캐시는 다른 결과와 칸을 함께 쓰므로 DBA와 result_cache_max_size를 확인하고, 붙인 뒤에는 V$RESULT_CACHE_STATISTICS의 찾기와 만들기를 지켜본다.

해설

결과 캐시는 오늘의 답, 조인은 다음 배포의 답이다. 한 단어로 31개 프로그램의 함수 안 SQL을 없애는 효과는 크고 되돌리기도 쉽다(한 단어를 떼면 된다). 그러나 행이 많은 프로그램에 남는 함수 호출은 조인만 없앤다. 두 안은 경쟁하는 안이 아니라 순서다.
붙인 뒤에는 V$RESULT_CACHE_STATISTICS의 Find와 Create, Invalidation을 매일 본다. 만들기가 92 근처에서 멈추고 찾기가 호출 수만큼 늘어야 정상이다. 만들기가 계속 늘면 숨은 입력이나 잦은 무효화를 의심한다(교재 15b화).
[B]와 [C]를 이너 조인으로 바꾸면 마스터에 없는 상품의 행이 사라진다. 함수는 그런 행에 NULL을 돌려주고 행을 남겼으므로 아우터 조인이어야 결과가 같다.
프로그램 31곳을 다 고친다는 안은 일이 크고 위험도 크다. 호출의 96%가 두 프로그램에서 나오므로 숫자가 고칠 곳을 좁혀 준다. 나머지 29곳은 결과 캐시에 맡긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황