운영 상황판의 「이번 달 주문 현황」 타일 하나가 논리 읽기 1위에 올랐다

기초SELECT한빛유통293 / 700

상황

운영 상황판 첫 화면의 「이번 달 주문 현황」 타일은 이번 달 전체 주문 수, 완료 주문 수와 금액, 대기 주문 수, 취소 주문 수를 보여 주고, 업무 시간(09 ~ 21시)에는 1분마다 새로 고친다. 한 번에 0.5초라 아무도 신경 쓰지 않았는데, 지난주 AWR의 SQL ordered by Gets에서 이 SQL이 1위로 나왔다(하루 720번, 약 2,100만 블록). 아래는 2024년 6월을 조회했을 때의 실행계획이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 이 문제에서는 인덱스가 없다 · ORD_DT DATE(전부 자정) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%
2024년 6월: 27,390건(COMPLETE 24,651 · PENDING 2,191 · CANCEL 548) · 통계 최신
화면은 이번 달 1일과 다음 달 1일을 DATE 바인드(:v_from, :v_to)로 넘긴다

원본 SQL

sql

SELECT (SELECT COUNT(*) FROM T_ORD_BIG
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to)                         AS all_cnt,
       (SELECT COUNT(*) FROM T_ORD_BIG
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to AND ORD_ST = 'COMPLETE') AS cmpl_cnt,
       (SELECT SUM(ORD_AMT) FROM T_ORD_BIG
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to AND ORD_ST = 'COMPLETE') AS cmpl_amt,
       (SELECT COUNT(*) FROM T_ORD_BIG
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to AND ORD_ST = 'PENDING')  AS pend_cnt,
       (SELECT COUNT(*) FROM T_ORD_BIG
         WHERE ORD_DT >= :v_from AND ORD_DT < :v_to AND ORD_ST = 'CANCEL')   AS cncl_cnt
  FROM DUAL

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      1 | 00:00:00.52 |   29130 |
|   1 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.11 |    5826 |
|*  2 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.11 |    5826 |
|   3 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.10 |    5826 |
|*  4 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |  24539 |  24651 | 00:00:00.10 |    5826 |
|   5 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.10 |    5826 |
|*  6 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |  24539 |  24651 | 00:00:00.10 |    5826 |
|   7 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.10 |    5826 |
|*  8 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |   2181 |   2191 | 00:00:00.10 |    5826 |
|   9 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.10 |    5826 |
|* 10 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |    545 |    548 | 00:00:00.10 |    5826 |
|  11 |  FAST DUAL           |           |      1 |      1 |      1 | 00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))
   4 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))
   6 - filter(("ORD_ST"='COMPLETE' AND "ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))
   8 - filter(("ORD_ST"='PENDING' AND "ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))
  10 - filter(("ORD_ST"='CANCEL' AND "ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

문제

이 SQL 한 번이 몇 블록을 읽는지, 그 블록이 어디서 나오는지 실행계획의 줄 번호로 설명하시오.
결과 다섯 칸이 바뀌지 않게 T_ORD_BIG을 한 번만 읽도록 SQL을 고치시오. 건수를 셀 때 CASE의 ELSE를 어떻게 써야 하는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 테이블은 한 번만 읽고, 칸을 나누는 상태 조건은 집계 함수 안에서 행마다 따진다
SELECT COUNT(*)                                            AS all_cnt,
       COUNT(CASE WHEN ORD_ST = 'COMPLETE' THEN 1 END)     AS cmpl_cnt,   -- ELSE 를 쓰지 않는다
       SUM(CASE WHEN ORD_ST = 'COMPLETE' THEN ORD_AMT END) AS cmpl_amt,
       COUNT(CASE WHEN ORD_ST = 'PENDING'  THEN 1 END)     AS pend_cnt,
       COUNT(CASE WHEN ORD_ST = 'CANCEL'   THEN 1 END)     AS cncl_cnt
  FROM T_ORD_BIG
 WHERE ORD_DT >= :v_from
   AND ORD_DT <  :v_to

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

xplan

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |      1 |        |      1 | 00:00:00.11 |    5826 |
|   1 |  SORT AGGREGATE      |           |      1 |      1 |      1 | 00:00:00.11 |    5826 |
|*  2 |   TABLE ACCESS FULL  | T_ORD_BIG |      1 |  27266 |  27390 | 00:00:00.10 |    5826 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("ORD_DT">=:V_FROM AND "ORD_DT"<:V_TO))

모범 답안 서술

1번, 3번, 5번, 7번, 9번 줄의 SORT AGGREGATE가 SELECT 목록의 스칼라 서브쿼리 다섯 개이고, 그 아래 2번, 4번, 6번, 8번, 10번 줄이 모두 T_ORD_BIG을 FULL로 읽는다(Starts 1, Buffers 5,826씩). 다섯 칸이 같은 6월 27,390건을 보는데 칸마다 테이블을 처음부터 끝까지 다시 읽으므로 한 번에 5 × 5,826 = 29,130블록이다. 바깥이 DUAL 한 행이라 스칼라 서브쿼리 캐시가 덜어 줄 일도 없다. 하루 720번이면 약 2,100만 블록이다. 테이블은 한 번만 읽고, 칸을 나누는 상태 조건은 행마다 집계 함수 안의 CASE로 따진다. 기간 조건은 WHERE에 한 번 두어 6월 행만 남기고, 전체 건수는 COUNT(*), 상태별 건수는 COUNT(CASE WHEN ORD_ST = 'COMPLETE' THEN 1 END), 완료 금액은 SUM(CASE WHEN ORD_ST = 'COMPLETE' THEN ORD_AMT END)로 구한다. Buffers는 29,130 → 5,826, 하루로는 약 2,100만 → 420만 블록이 된다. 건수 칸의 CASE에는 ELSE를 쓰지 않는다. 조건에 맞지 않는 행은 NULL이 되고 COUNT는 NULL을 세지 않으므로 상태별 건수가 나온다. ELSE 0을 붙이면 0도 값이라 COUNT가 27,390건을 전부 세어 세 칸이 모두 전체 건수가 된다. ELSE 0을 쓰고 싶으면 SUM(CASE … THEN 1 ELSE 0 END)로 더한다. 결과는 원래 SQL과 같다. 완료 주문이 한 건도 없는 달이면 원래 서브쿼리의 SUM도, 고친 SQL의 SUM(CASE …)도 NULL을 돌려준다. 더 줄이려면 (ORD_DT, ORD_ST, ORD_AMT) 인덱스를 만들어 6월 범위의 인덱스만 읽게 할 수 있다(약 110블록). 대가는 100만 행 테이블에 인덱스가 하나 늘어 주문이 들어올 때마다 인덱스도 고쳐야 한다는 것이다. 먼저 SQL을 한 번 읽기로 고치고, 인덱스는 그다음에 하루 일량과 적재 비용을 비교해 정한다.

해설

함정: COUNT(CASE WHEN … THEN 1 ELSE 0 END)는 문법 오류도 없고 그럴듯한 숫자가 나와서 놓치기 쉽다. 고친 뒤에는 원래 SQL과 다섯 칸을 하나씩 대조한다.
다른 정답: DECODE(ORD_ST, 'COMPLETE', 1)도 같은 일을 한다. 상태가 세 가지뿐이라 GROUP BY ORD_ST로 세 줄을 받아 화면에서 합쳐도 한 번 읽기다. 다만 화면이 한 줄을 기대하고, 그달 취소가 0건이면 취소 줄이 아예 없어진다.
인덱스의 대가: 커버링 인덱스는 조회를 줄이지만 적재 비용을 늘린다(교재 14화). 1분마다 도는 타일이라면 SQL을 고친 뒤의 하루 일량(약 420만 블록)을 보고 인덱스를 만들지 정한다.
자주 도는 작은 SQL은 한 번의 일량이 작아도 하루로 곱하면 상위에 오른다. AWR의 SQL ordered by Gets는 실행 횟수와 한 번의 일량을 함께 본다(교재 16화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황