점주 앱 장애의 재발을 막겠다며 DBA가 오늘 밤 옵티마이저 파라미터 하나를 시스템 전체에 반영하겠다고 한다

실전SELECT버거253 / 700

상황

가맹점주 앱 「최근 주문 20건」이 19c 업그레이드 뒤 스칼라 서브쿼리 Unnesting 때문에 느려진 일이 있었다(Buffers 104 → 60,310, SUBQ-011). DBA는 재발을 막겠다며 오늘 밤 ALTER SYSTEM SET "_optimizer_unnest_scalar_sq" = FALSE를 반영하겠다고 통보했다. 박팀장은 반영 전에 스칼라 서브쿼리를 쓰는 주요 SQL 셋을 보고 판단해 달라고 했다. 아래는 지금(기본값)과, 복사본 DB에서 파라미터를 끈 뒤 잰 숫자다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · 날짜순으로 쌓여 있다 · ORD_X01 (ORD_DT, ORD_HMS) · ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS) · 한 달 약 41,700건 · 매장마다 약 5,000건(한 달 약 208건)
ORD_ITEM: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS) · 한 달 약 103,000줄 · 하루 약 3,400줄
SHOP: 200행 · 5블록 · ITEM: 92행 · 5블록 · 둘 다 PK
Q1 하루 1만 번, Q2 하루 20번(본사 월 리포트), Q3 하루 200번 · 통계 최신

원본 SQL

sql

-- Q1 가맹점주 앱 「최근 주문 20건」 (하루 1만 번)
SELECT *
  FROM (SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS,
               (SELECT SUM(i.ORD_ITEM_QTY) FROM ORD_ITEM i WHERE i.ORD_NO = o.ORD_NO) AS qty
          FROM ORD o
         WHERE o.SHOP_NO = :shop
         ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
 WHERE ROWNUM <= 20;

-- Q2 본사 「매장별 월 판매 수량」 (하루 20 번)
SELECT s.SHOP_NO, s.SHOP_NM,
       (SELECT SUM(i.ORD_ITEM_QTY)
          FROM ORD o, ORD_ITEM i
         WHERE o.SHOP_NO = s.SHOP_NO
           AND o.ORD_DT BETWEEN :d1 AND :d2
           AND i.ORD_NO = o.ORD_NO) AS qty
  FROM SHOP s
 ORDER BY s.SHOP_NO;

-- Q3 「일 상품 판매 순위」 (하루 200 번)
SELECT t.ITEM_ID,
       (SELECT m.ITEM_NM FROM ITEM m WHERE m.ITEM_ID = t.ITEM_ID) AS item_nm,
       t.qty
  FROM (SELECT ITEM_ID, SUM(ORD_ITEM_QTY) AS qty
          FROM ORD_ITEM
         WHERE ORD_DT = :d
         GROUP BY ITEM_ID) t
 ORDER BY t.qty DESC;

실행계획 학습용 예시 · 파라미터를 끈 숫자는 복사본 DB에서 잰 것으로 둔다

xplan

[세 SQL · 지금(기본값) ↔ 복사본에서 _optimizer_unnest_scalar_sq = FALSE]
            지금 Buffers    끈 뒤 Buffers    하루 실행    하루 블록 (지금 → 끈 뒤)
  Q1          60,310             104          10,000     약 6억 310만 → 104만
  Q2          56,341         209,165              20     약 113만 → 418만
  Q3             268             268             200     약 5만 4천 → 5만 4천 (상품명 서브쿼리 Starts 88 · 펼쳐지지 않는다)

[Q2 — 지금 (펼쳐짐)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |      1 |        |    200 |00:00:01.21 |   56341 |
|   1 |  SORT ORDER BY                         |             |      1 |    200 |    200 |00:00:01.21 |   56341 |
|*  2 |   HASH JOIN OUTER                      |             |      1 |    200 |    200 |00:00:01.21 |   56341 |
|   3 |    TABLE ACCESS FULL                   | SHOP        |      1 |    200 |    200 |00:00:00.01 |       5 |
|   4 |    VIEW                                | VW_SSQ_1    |      1 |    200 |    200 |00:00:01.20 |   56336 |
|   5 |     HASH GROUP BY                      |             |      1 |    200 |    200 |00:00:01.20 |   56336 |
|*  6 |      HASH JOIN                         |             |      1 |    104K|    103K|00:00:01.05 |   56336 |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| ORD       |      1 |  41700 |  41712 |00:00:00.02 |    1071 |
|*  8 |        INDEX RANGE SCAN                | ORD_X01     |      1 |  41700 |  41712 |00:00:00.01 |     139 |
|   9 |       TABLE ACCESS FULL                | ORD_ITEM    |      1 |   2472K|   2472K|00:00:00.52 |   55265 |
-------------------------------------------------------------------------------------------------------
   2 - access("ITEM_1"(+)="S"."SHOP_NO")
   6 - access("I"."ORD_NO"="O"."ORD_NO")
   8 - access("O"."ORD_DT">=:D1 AND "O"."ORD_DT"<=:D2)

[Q2 — 파라미터를 끈 뒤 (복사본)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |      1 |        |    200 |00:00:00.92 |     209K|
|   1 |  SORT AGGREGATE                        |             |    200 |      1 |    200 |00:00:00.91 |     209K|
|   2 |   NESTED LOOPS                         |             |    200 |    520 |    103K|00:00:00.88 |     209K|
|   3 |    NESTED LOOPS                        |             |    200 |    520 |    103K|00:00:00.47 |     167K|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |    200 |    208 |  41712 |00:00:00.12 |   42312 |
|*  5 |      INDEX RANGE SCAN                  | ORD_X02     |    200 |    208 |  41712 |00:00:00.01 |     600 |
|*  6 |     INDEX RANGE SCAN                   | ORD_ITEM_PK |  41712 |      2 |    103K|00:00:00.21 |     125K|
|   7 |    TABLE ACCESS BY INDEX ROWID         | ORD_ITEM    |    103K|      1 |    103K|00:00:00.30 |   41712 |
|   8 |  SORT ORDER BY                         |             |      1 |    200 |    200 |00:00:00.92 |     209K|
|   9 |   TABLE ACCESS FULL                    | SHOP        |      1 |    200 |    200 |00:00:00.01 |       5 |
-------------------------------------------------------------------------------------------------------
   5 - access("O"."SHOP_NO"=:B1 AND "O"."ORD_DT">=:D1 AND "O"."ORD_DT"<=:D2)
   6 - access("I"."ORD_NO"="O"."ORD_NO")

(DBMS_XPLAN 이 줄여 쓴 값 — 125K = 125,136 · 167K = 167,448 · 209K = 209,160 / 209,165)

문제

세 SQL이 파라미터를 끄면 각각 어떻게 되는지 숫자로 정리하고, 하루 실행 횟수를 곱해 시스템 전체에서 끄는 안을 평가하시오.
파라미터를 바꾸지 않고 Q1이 다시 느려지지 않도록 SQL을 고치시오. Q2도 변환에 기대지 않는 SQL로 고치시오.
SQL 수정, SQL 하나에만 거는 힌트나 베이스라인, 시스템 파라미터의 대가를 비교해 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 시스템 파라미터는 그대로 둔다. 고칠 범위를 SQL 하나씩으로 좁힌다.

-- Q1 : 20 건을 먼저 자르고, 수량은 바깥에서 20 번만 (서브쿼리 안 NO_UNNEST · 힌트 대장에 기록)
SELECT v.ORD_NO, v.ORD_DT, v.ORD_HMS,
       (SELECT /*+ NO_UNNEST */ SUM(i.ORD_ITEM_QTY)
          FROM ORD_ITEM i
         WHERE i.ORD_NO = v.ORD_NO) AS qty
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS
          FROM (SELECT o.ORD_NO, o.ORD_DT, o.ORD_HMS
                  FROM ORD o
                 WHERE o.SHOP_NO = :shop
                 ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC)
         WHERE ROWNUM <= 20) v
 ORDER BY v.ORD_DT DESC, v.ORD_HMS DESC;

-- Q2 : 펼쳐 주기를 기다리지 않고 조인 + GROUP BY 로 직접 쓴다 (주문이 없는 매장은 NULL · 원래와 같다)
SELECT s.SHOP_NO, s.SHOP_NM, q.qty
  FROM SHOP s,
       (SELECT o.SHOP_NO, SUM(i.ORD_ITEM_QTY) AS qty
          FROM ORD o, ORD_ITEM i
         WHERE o.ORD_DT BETWEEN :d1 AND :d2
           AND i.ORD_NO = o.ORD_NO
         GROUP BY o.SHOP_NO) q
 WHERE q.SHOP_NO(+) = s.SHOP_NO
 ORDER BY s.SHOP_NO;

-- Q3 : 그대로 둔다

개선 후 실행계획 학습용 예시 · 파라미터를 끈 숫자는 복사본 DB에서 잰 것으로 둔다

xplan

[Q1 — 고친 SQL]
--------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |      1 |        |     20 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE                        |             |     20 |      1 |     20 |00:00:00.01 |      80 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | ORD_ITEM    |     20 |      2 |     49 |00:00:00.01 |      80 |
|*  3 |    INDEX RANGE SCAN                    | ORD_ITEM_PK |     20 |      2 |     49 |00:00:00.01 |      60 |
|   4 |  SORT ORDER BY                         |             |      1 |     20 |     20 |00:00:00.01 |     104 |
|   5 |   VIEW                                 |             |      1 |     20 |     20 |00:00:00.01 |      24 |
|*  6 |    COUNT STOPKEY                       |             |      1 |        |     20 |00:00:00.01 |      24 |
|   7 |     VIEW                               |             |      1 |   5000 |     20 |00:00:00.01 |      24 |
|   8 |      TABLE ACCESS BY INDEX ROWID       | ORD         |      1 |   5000 |     20 |00:00:00.01 |      24 |
|*  9 |       INDEX RANGE SCAN DESCENDING      | ORD_X02     |      1 |   5000 |     20 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------
   3 - access("I"."ORD_NO"=:B1)
   6 - filter(ROWNUM<=20)
   9 - access("O"."SHOP_NO"=:SHOP)

[Q2 — 고친 SQL · 파라미터와 상관없이 같은 계획]  Buffers 56,341 (지금과 같다)

모범 답안 서술

세 SQL을 파라미터 기준으로 나누면 이렇다. Q1은 바깥 집합(매장 주문 5,000건)이 크다고 보고 펼쳤다가 20건에서 멈추지 못한 경우다(60,310, 끄면 104). Q2는 매장 200곳마다 한 달 주문과 주문상품을 찾는 집계라 펼쳐서 한 번에 묶는 지금 계획이 낫다(56,341, 끄면 매장마다 NL로 돌아 209,165블록, 약 3.7배). Q3의 상품명 서브쿼리는 집계가 아닌 단순 조회이고 상품별로 묶은 뒤 88번만 돌아서 파라미터와 상관없이 268블록 그대로다. 시스템 전체에서 끄면 Q1은 고쳐지지만 Q2는 하루 113만 → 418만 블록으로 나빠진다. 더 큰 문제는 이 셋 말고도 펼쳐서 빨라진 SQL이 모두 함께 바뀐다는 점이다. 밑줄 파라미터는 오라클 지원 없이 바꾸지 않는 것이 원칙이고, 무엇이 나빠질지 미리 다 볼 수 없다. 그래서 고칠 범위를 SQL 하나로 좁힌다. Q1은 SQL을 고친다. 20건을 먼저 자르고 수량 서브쿼리는 바깥에서 부르며, 서브쿼리 안에 NO_UNNEST를 넣는다(60,310 → 104, 하루 약 6억 310만 → 104만 블록). 앱 배포가 당장 어렵다면 Q1만 SQL Plan Baseline으로 11g 계획을 고정하거나, 그 SQL에만 OPT_PARAM 힌트로 파라미터를 끄는 방법이 있다. Q2는 변환에 기대지 않도록 조인 + GROUP BY로 직접 써 둔다. 주문이 없는 매장도 외부 조인으로 남기면 수량이 NULL로 나와 원래 스칼라 서브쿼리와 답이 같다. Q3는 그대로 둔다. 대안마다 대가가 있다. SQL 수정은 배포와 회귀 시험이 필요하다. 힌트와 베이스라인은 데이터가 바뀌면 낡으므로 힌트 대장에 넣은 이유와 다시 볼 조건(매장당 주문 수, 버전)을 적는다. 시스템 파라미터는 되돌리기 쉬워 보여도 영향 범위를 잴 수 없다.

해설

고칠 범위를 좁히는 순서: SQL 수정, SQL 하나에 거는 힌트나 베이스라인, 세션, 시스템 순으로 범위가 넓어진다. 넓을수록 빨리 걸 수 있지만 영향을 잴 수 없는 SQL이 늘어난다(교재 06화 힌트 유효기간 대장, 08화 §8).
Q2를 더 줄이려면: ORD_ITEM에도 주문일자가 있으므로 i.ORD_DT BETWEEN :d1 AND :d2를 더하면 주문상품 전체(55,265블록) 대신 한 달 치만 ORD_ITEM_X01로 읽어 약 3,700블록이 된다. 다만 주문과 주문상품의 일자가 늘 같은지(두 테이블을 조인해 일자가 다른 줄이 0건인지) 먼저 확인해야 한다.
OPT_PARAM 힌트: 받아들이는 파라미터는 버전마다 다르므로 넣은 뒤 계획의 Outline이나 Note로 실제로 먹었는지 확인한다.
원인을 파라미터 하나로 돌리면 다음 업그레이드 때 같은 일이 생긴다. Q1이 느려진 근본 원인은 화면이 20건만 쓰는데 SQL은 5,000건에 수량을 붙이도록 쓰였다는 점이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 파라미터를 끈 숫자는 복사본 DB에서 잰 것으로 둔다 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 파라미터를 끈 숫자는 복사본 DB에서 잰 것으로 둔다 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황