개발 DB에서는 함수 기반 인덱스를 타던 월별 화면이 운영에서는 FULL로 돈다

중급SELECT유통556 / 700

상황

작년 가을 공유 풀 경보가 울렸을 때 DBA가 유통 운영 DB 인스턴스에 CURSOR_SHARING = FORCE를 켰고, 지금도 그대로다. 개발 DB는 기본값(EXACT)이다. 이번 달 조민우가 「매장 월별 요일 분포」 화면을 만들면서 함수 기반 인덱스를 하나 만들었다. 개발 DB에서는 0.01초였는데, 운영에 올리자 화면을 열 때마다 4,583블록을 읽는다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) '20230601' · SHP_NO NUMBER(매장 6곳) · SLE_DOW 요일
매장 한 곳의 한 달 판매는 약 3,470건이다.
인덱스 CF10_PK (SLE_NO) · 이 화면 때문에 만든 함수 기반 인덱스 IX_CF10_SHP_YM (SHP_NO, SUBSTR(SLE_DT, 1, 6)) · 통계 최신
운영 CURSOR_SHARING = FORCE(인스턴스 전체) · 개발 EXACT

원본 SQL

sql

-- 조민우 작성 : 조회월(:ym, 'YYYYMM')과 매장(:shp)은 이미 바인드 변수다
SELECT SLE_DOW, COUNT(*) AS SLE_CNT
  FROM CF10
 WHERE SHP_NO = :shp
   AND SUBSTR(SLE_DT, 1, 6) = :ym
 GROUP BY SLE_DOW
 ORDER BY SLE_DOW;

CREATE INDEX IX_CF10_SHP_YM ON CF10 (SHP_NO, SUBSTR(SLE_DT, 1, 6));

실행계획 학습용 예시

xplan

[운영 V$SQL · SQL_TEXT]
  SELECT SLE_DOW, COUNT(*) AS SLE_CNT FROM CF10 WHERE SHP_NO = :shp AND SUBSTR(SLE_DT, :"SYS_B_0", :"SYS_B_1") = :ym GROUP BY SLE_DOW ORDER BY SLE_DOW

[운영 · FORCE]
Plan hash value: 2771390418

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      7 |00:00:00.09 |    4583 |
|   1 |  SORT GROUP BY     |      |      1 |      7 |      7 |00:00:00.09 |    4583 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |    833 |   3468 |00:00:00.08 |    4583 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SHP_NO"=:SHP AND SUBSTR("SLE_DT",:SYS_B_0,:SYS_B_1)=:YM))

[개발 · EXACT · 같은 SQL]
Plan hash value: 1046282913

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      7 |00:00:00.01 |     207 |
|   1 |  SORT GROUP BY                       |                |      1 |      7 |      7 |00:00:00.01 |     207 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   3472 |   3468 |00:00:00.01 |     207 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SHP_YM |      1 |   3472 |   3468 |00:00:00.01 |      14 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SHP_NO"=:SHP AND SUBSTR("SLE_DT",1,6)=:YM)

문제

같은 SQL이 운영에서만 FULL로 도는 이유를 운영 V$SQL의 텍스트와 Predicate Information으로 설명하시오.
FORCE를 그대로 둔 채 이 화면이 인덱스 범위 스캔을 하도록 SQL과 인덱스를 고치시오. 다른 방법을 하나 더 들고 그 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 컬럼을 가공하지 않는 범위 조건으로 · 경계 값은 화면에서 만든다 (SQL 안에 상수가 없으니 FORCE가 바꿀 것이 없다)
SELECT SLE_DOW, COUNT(*) AS SLE_CNT
  FROM CF10
 WHERE SHP_NO = :shp
   AND SLE_DT >= :ym_from          -- '20230601'
   AND SLE_DT <  :ym_to            -- '20230701'
 GROUP BY SLE_DOW
 ORDER BY SLE_DOW;

-- 함수 기반 인덱스 대신 일반 결합 인덱스 (다른 날짜 범위 조회도 함께 쓴다)
CREATE INDEX IX_CF10_SHP_DT ON CF10 (SHP_NO, SLE_DT);
DROP INDEX IX_CF10_SHP_YM;

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

xplan

[운영 · FORCE · 고친 SQL (학습용 예시) · V$SQL 텍스트에 SYS_B_ 없음]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      7 |00:00:00.01 |     206 |
|   1 |  SORT GROUP BY                       |                |      1 |      7 |      7 |00:00:00.01 |     206 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |   3472 |   3468 |00:00:00.01 |     206 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SHP_DT |      1 |   3472 |   3468 |00:00:00.01 |      13 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SHP_NO"=:SHP AND "SLE_DT">=:YM_FROM AND "SLE_DT"<:YM_TO)

모범 답안 서술

운영 V$SQL의 텍스트를 보면 SUBSTR(SLE_DT, 1, 6)이 SUBSTR(SLE_DT, :"SYS_B_0", :"SYS_B_1")로 바뀌어 있다. FORCE는 문장 안의 상수를 모두 바인드 자리로 바꾸는데, 조회월과 매장은 이미 바인드 변수였으니 바뀐 것은 SUBSTR의 인자 1과 6이다. 함수 기반 인덱스는 SUBSTR("SLE_DT",1,6)이라는 식 그대로 만들어져 있어서, 옵티마이저는 인자가 바인드 자리인 식을 인덱스의 식과 같다고 보지 못한다. 그래서 2번 줄이 filter로 바뀌고 FULL로 4,583블록을 읽는다. 개발 DB는 EXACT라 상수가 그대로 남아 3번 줄에서 access로 쓰였다(207블록). FORCE를 그대로 두고 고치려면 SQL 안에 FORCE가 바꿀 상수를 남기지 않는다. 컬럼을 가공하지 않고 SLE_DT >= :ym_from AND SLE_DT < :ym_to 범위 조건으로 쓰고(경계 값은 화면에서 만든다), 함수 기반 인덱스 대신 일반 인덱스 (SHP_NO, SLE_DT)를 만든다. 운영에서도 인덱스 범위 스캔으로 4,583 → 206블록이 되고, 이 인덱스는 다른 날짜 범위 조회에도 쓸 수 있다. 다른 방법으로는 이 문장에 /*+ CURSOR_SHARING_EXACT */ 힌트를 붙여 상수를 지키는 방법이 있다. SQL 한 줄로 끝나지만, 앞으로 식이 들어간 인덱스를 쓰는 문장마다 힌트를 잊지 않고 붙여야 한다. 가상 컬럼(SLE_YM AS (SUBSTR(SLE_DT, 1, 6)))에 인덱스를 만들고 SLE_YM = :ym으로 조회해도 되지만 테이블에 DDL이 필요하다. 근본은 FORCE를 끄는 것이지만 인스턴스 전체 문장에 영향을 주므로 따로 점검해야 한다(PARSE-016). 어느 쪽이든 개발 DB의 CURSOR_SHARING을 운영과 같게 맞춰 두어야 이런 차이가 테스트에서 드러난다.

해설

FORCE가 바꾸는 상수에는 조건 값만이 아니라 함수 인자(SUBSTR의 자리와 길이, TO_CHAR의 형식 문자열)도 들어간다(교재 11화 §8: Q1 rev03의 'YYYYMMDD'도 바인드 자리가 됐다). 식이 들어간 인덱스는 그 상수가 그대로 있어야 쓰인다.
Buffers만 비교하면 원인을 놓친다. 운영과 개발의 차이를 찾을 때는 V$SQL의 SQL_TEXT부터 본다. SYS_B_가 보이면 FORCE가 손댄 문장이다.
이 동작은 버전과 설정에 따라 다를 수 있다. 반영하기 전에 운영과 같은 파라미터로 실행계획을 확인한다.
SUBSTR(SLE_DT, 1, 6) = :ym을 범위 조건으로 바꾸는 것은 컬럼을 가공하지 않는다는 인덱스 사용 원칙과도 같다(교재 02화, 03화). FORCE가 없어도 좋은 습관이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황