JOIN-073

코드 정리 배포 다음 날부터 매장 「영수증 목록」 첫 화면이 0.01초에서 0.15초로 늘었다

기초SELECT유통223 / 700

상황

매장 관리 화면의 「영수증 목록」은 매장 하나와 기간(기본값 최근 3개월)을 받아 가장 최근 영수증부터 상품 줄을 보여 준다. 화면은 커서를 열어 20줄을 받아 그리고, 「다음」을 눌러야 이어서 받는다. 점장들과 본사 영업팀이 하루 3천 번쯤 누른다. 2009년에 만든 SQL은 별칭 없이 테이블 이름만 썼다. 지난주 코드 정리 배포에서 "모든 테이블에 별칭을 붙인다"는 새 코딩 규칙에 맞춰 SQL 수백 개의 FROM 절과 컬럼 이름을 도구로 일괄 고쳤다. 그다음 날부터 이 화면의 첫 화면이 0.15초씩 걸린다. 아래는 매장 1003의 2023-10-01 ~ 2023-12-31을 조회한 지금의 실행계획이다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 날짜순 적재 · 매장 1003의 2023년 4분기 영수증 10,476건
인덱스 IX_CF10_SHP_DT_HMS (SHP_NO, SLE_DT, SLE_HMS)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 영수증당 평균 2.19줄
통계 최신 · 옵티마이저 모드 ALL_ROWS(기본값) · SLE_DT가 VARCHAR2(8)이라 옵티마이저는 이 범위를 약 2,600건으로 본다(교재 03화)
배포 전 SQL은 FROM CF10, CF11이었고 힌트는 지금과 글자까지 같았다. 그때 첫 화면은 63블록 · 0.01초였다

원본 SQL

sql

SELECT /*+ LEADING(CF10 CF11) USE_NL(CF11) INDEX_DESC(CF10 IX_CF10_SHP_DT_HMS) */
       a.SLE_DT, a.SLE_HMS, a.SLE_NO, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SHP_NO = :shp
   AND a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_DT DESC, a.SLE_HMS DESC

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |      1 |        |     20 |00:00:00.15 |    9506 |          |
|   1 |  SORT ORDER BY                        |                    |      1 |   5694 |     20 |00:00:00.15 |    9506 | 1870K (0)|
|*  2 |   HASH JOIN                           |                    |      1 |   5694 |  22942 |00:00:00.12 |    9506 | 1330K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| CF10               |      1 |   2600 |  10476 |00:00:00.01 |    1302 |          |
|*  4 |     INDEX RANGE SCAN                  | IX_CF10_SHP_DT_HMS |      1 |   2600 |  10476 |00:00:00.01 |      42 |          |
|   5 |    TABLE ACCESS FULL                  | CF11               |      1 |  1096K |  1096K |00:00:00.08 |    8204 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   4 - access("A"."SHP_NO"=:SHP AND "A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (N - Unresolved (3))
---------------------------------------------------------------------------

   1 -  SEL$1
         N -  INDEX_DESC(CF10 IX_CF10_SHP_DT_HMS)
         N -  LEADING(CF10 CF11)
         N -  USE_NL(CF11)

문제

힌트가 그대로인데 계획이 바뀐 이유를 Hint Report와 실행계획으로 설명하시오. 힌트가 없을 때 옵티마이저가 왜 이 계획을 골랐는지도 쓰시오.
첫 화면이 다시 배포 전처럼 처리되도록 SQL을 고치시오. 같은 일이 되풀이되지 않게 하려면 무엇을 해야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT /*+ LEADING(a b) USE_NL(b) INDEX_DESC(a IX_CF10_SHP_DT_HMS) */
       a.SLE_DT, a.SLE_HMS, a.SLE_NO, b.ITM_NO, b.SLE_QTY, b.SLE_UPRC
  FROM CF10 a, CF11 b
 WHERE a.SHP_NO = :shp
   AND a.SLE_DT BETWEEN :d1 AND :d2
   AND b.SLE_NO = a.SLE_NO
 ORDER BY a.SLE_DT DESC, a.SLE_HMS DESC
-- 별칭을 붙인 SQL 의 힌트는 별칭으로 쓴다. 일괄 변경 도구에는 힌트 안의 이름도 바꾸는 규칙을 넣는다

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |      1 |        |     20 |00:00:00.01 |      63 |
|   1 |  NESTED LOOPS                   |                    |      1 |   5694 |     20 |00:00:00.01 |      63 |
|   2 |   NESTED LOOPS                  |                    |      1 |   5694 |     20 |00:00:00.01 |      43 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | CF10               |      1 |   2600 |     10 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING | IX_CF10_SHP_DT_HMS |      1 |   2600 |     10 |00:00:00.01 |       3 |
|*  5 |    INDEX RANGE SCAN             | CF11_PK            |     10 |      2 |     20 |00:00:00.01 |      30 |
|   6 |   TABLE ACCESS BY INDEX ROWID   | CF11               |     20 |      2 |     20 |00:00:00.01 |      20 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."SHP_NO"=:SHP AND "A"."SLE_DT">=:D1 AND "A"."SLE_DT"<=:D2)
   5 - access("B"."SLE_NO"="A"."SLE_NO")

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------

   1 -  SEL$1
         -  LEADING(a b)

   4 -  SEL$1 / "A"@"SEL$1"
         -  INDEX_DESC(a IX_CF10_SHP_DT_HMS)

   5 -  SEL$1 / "B"@"SEL$1"
         -  USE_NL(b)

모범 답안 서술

Hint Report에 힌트 세 개가 모두 N(Unresolved)으로 찍혀 있다. FROM 절에서 CF10에 별칭 a를 붙이면 그 문장 안에서 이 테이블의 이름은 a가 된다. 힌트는 FROM 절에 적힌 이름(별칭이 있으면 별칭)으로만 대상을 찾으므로 LEADING(CF10 CF11), USE_NL(CF11), INDEX_DESC(CF10 ...)는 가리킬 테이블을 찾지 못했고, 오라클은 오류 없이 세 힌트를 모두 버렸다. 일괄 변경 도구가 FROM 절과 컬럼만 고치고 힌트 안의 이름은 그대로 두었기 때문이다. 힌트가 사라지자 옵티마이저는 ALL_ROWS 기준으로 결과 전체를 내는 비용을 계산했다. 영수증을 약 2,600건(4번 줄 E-Rows)으로 보고, 그만큼 CF11_PK를 찾아가는 NL보다 2번 줄처럼 CF11을 한 번 FULL로 읽는 해시 조인이 싸다고 본 것이다. 그 뒤 1번 줄에서 정렬해야 첫 줄이 나온다. 하지만 화면은 20줄만 받고 멈춘다. 옵티마이저는 그 사실을 모르므로, 20줄을 보여 주려고 실제 영수증 10,476건의 상품 22,942줄을 다 조인하고 정렬했다(Buffers 9,506, 0.15초). 힌트의 테이블 지정을 별칭으로 고친다. LEADING(a b) USE_NL(b) INDEX_DESC(a IX_CF10_SHP_DT_HMS)이면 인덱스를 거꾸로 읽어 최근 영수증부터 한 장씩 상품 줄을 붙이고, 인덱스 순서가 정렬 순서와 같아 정렬 없이 20줄에서 멈춘다. Buffers는 9,506 → 63, 시간은 0.15 → 0.01초로 돌아온다. 되풀이를 막으려면 일괄 변경 도구가 힌트 안의 테이블 이름도 별칭으로 바꾸게 하고, 배포 전후로 힌트가 있는 SQL의 Hint Report와 plan hash value를 비교하는 단계를 넣는다. N이나 U가 새로 생긴 SQL은 배포하지 않는다.

해설

힌트는 틀려도 조용하다: 대상을 찾지 못한 힌트는 오류 없이 버려진다. 19c부터 나오는 Hint Report가 그것을 드러내는 거의 유일한 증거다. DISPLAY_CURSOR로 계획을 볼 때 이 칸까지 읽는 습관을 들인다 (교재 06화 §7).
별칭을 떼는 답: FROM 절의 별칭을 다시 떼고 힌트를 그대로 두어도 계획은 돌아온다. 하지만 새 코딩 규칙과 어긋나고 다음 일괄 변경에서 또 깨진다. 힌트를 별칭에 맞추는 쪽이 맞다.
힌트 없이 푸는 길: FETCH FIRST 20 ROWS ONLY나 ROWNUM <= 20으로 20줄만 필요하다는 것을 SQL에 적으면, 옵티마이저가 앞의 20행 기준으로 비용을 계산해 같은 NL 계획을 스스로 고른다. 화면이 커서를 열어 두고 이어 받는 구조라면 키 기반 페이징으로 바꾸는 작업이 함께 필요하다 (교재 09화).
FIRST_ROWS(20)처럼 테이블을 가리키지 않는 힌트는 별칭 변경에 깨지지 않는다. 테이블을 가리키는 힌트(LEADING, USE_NL, INDEX 계열)만 별칭을 따라가야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황