우왕좌왕 좌충우돌 Slow query 개선 경험기

안녕하세요. 우왕좌왕 좌충우돌 Slow query 개선 경험기를 공유해 보려고 합니다.
혹시 잘못된 내용이 있다면 알려주세요. 피드백은 언제나 환영입니다 :)

 

 큰일이다 

어느 날 정산 모듈에 사용자가 몰리면서 전체 시스템에 영향을 준 일이 발생했습니다.
문제의 쿼리는 정산 모듈에서 특정 기간, 특정 사용자의 누적 사용 금액을 조회하는 쿼리였습니다.
정산 시 매번 조회되는 쿼리였기에 개선이 필요한 상황이었습니다.

예제를 위해 Slow query를 필요한 부분만 추출해 보면 아래와 같습니다.

SELECT SUM(B.AMOUNT) AS SUM_AMOUNT
FROM TB_MST A
   , TB_DTL B
WHERE A.ID = B.ID
AND A.DATES BETWEEN '20230101' AND '20230831'
AND B.USER_ID = 'ID_A'
;

 

TB_MST 테이블에 추가된 인덱스는 아래와 같습니다.

CREATE INDEX IDX_TB_MST_DATES ON TB_MST(DATES);

 

 

 실행계획 확인 

왜 느린지 실행계획을 확인해 보겠습니다.

1. /*+ GATHER_PLAN_STATISTICS */

Oracle 11g부터 도입된 /*+ GATHER_PLAN_STATISTICS */ 힌트를 사용하여 실행계획을 확인할 수 있습니다.

사용 방법은 아래와 같습니다.

1) 대상 쿼리에 /*+ GATHER_PLAN_STATISTICS */ 힌트를 넣고 조회한다.

SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(B.AMOUNT) AS SUM_AMOUNT
FROM TB_MST A
   , TB_DTL B
WHERE A.ID = B.ID
AND A.DATES BETWEEN '20230101' AND '20230831'
AND B.USER_ID = 'ID_A'
;

2) v&sql View에서 /*+ GATHER_PLAN_STATISTICS */ 텍스트를 포함한
     가장 최근에 실행된 SQL문을 찾고, SQL_ID를 얻는다.

SELECT *
FROM v$sql
WHERE SQL_TEXT LIKE '%GATHER_PLAN_STATISTICS%'
ORDER BY LAST_ACTIVE_TIME DESC
;

3) 실행계획을 얻는다.
      Format에 'ALLSTATS LAST' or 'ADVANCED ALLSTATS LAST'를 사용하면 상세 정보를 얻을 수 있다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(SQL_ID, 0, 'ADVANCED ALLSTATS LAST'))
;

 

2. 실행 계획 확인

1) 현재월 1일 ~ 현재월 말(상대적으로 좁은 범위) 조회 시 IDX_TB_MST_DATES를 탄다.

2) 2023-01-01 ~ 현재월 말(상대적으로 넓은 범위) 조회 시 IDX_TB_MST_DATES를 타지 하지 않는다.
     그래서 TB_DTL 테이블 풀 스캔이 일어남.

 

 인덱스야 너 왜 안타? 

통계 정보를 확인하였을 때 생성된 인덱스가 제대로 타지 않는 게 원인이었습니다.
물론, 옵티마이저 판단하에 인덱스 사용 유무가 결정됐겠지만,
"왜 사용하지 않았을까?"
'이런 이유에 의해 이럴 것이다' 예상해 보고 싶었습니다.
(Oracle 11g 기준, 힌트가 없다면 비용 기반 옵티마이저이고 대부분의 RDBMS의 경우 비용 기반 옵티마이저를 사용한다)

왜 범위 크기에 따라 인덱스 사용 유무가 결정됐을까?

먼저 테이블 풀 스캔이 일어난 경우를 알아야 합니다.
풀 스캔이 일어나는 경우는 대개 3가지입니다.

1. 크기가 작은 테이블에 액세스 하는 경우
   : TB_MST 테이블의 전체 레코드 수는 약 77만 건이다. 상대적인 개념이지만 결코 작은 테이블은 아니다.
2. 적용 가능한 인덱스가 없는 경우
   : IDX_TB_MST_DATES라는 적용 가능한 인덱스가 있다.
3. 인덱스 처리 범위가 넓은 경우
   : 넓은 범위의 DATES를 찾고 싶을 때 인덱스를 타지 않고 풀 스캔이 발생했다. 정확히 내 상황과 일치한다.

대부분의 인덱스는 B-트리 또는 B+트리와 같은 트리 구조로 구성되어 있는데,
이러한 트리구조는 특정 범위를 찾고 싶을 때 인덱스 트리를 여러 번 탐색해야 하는 경우가 있습니다.
결국 DATES 컬럼은 카디널리티가 낮으니 다중 탐색보다 풀 스캔이 유리하다고 판단을 한 것 같습니다.
(인덱스 생성 시 카디널리티가 높은 컬럼을 인덱스로 사용하는 게 유리하다)

💡 카디널리티가 낮다 = 유니크한 값이 적다 = 중복도가 높다 = 분포도가 높다 = 탐색 비용이 커진다

예를 들면 2023-05-01일에 사용한 금액이 여러 개 일수 있습니다.

DATES	AMT
...	...
2023-04-30	100
2023-05-01	200
2023-05-01	300
2023-05-01	400
2023-05-02	500
...	...

 

그림으로 보면 아래와 같습니다

카디널리티가 높다면 단일 탐색 이거나 다중 탐색 빈도가 적을 것입니다.
그렇지만 현재 DATES 컬럼의 경우 카디널리티가 낮기 때문에 여러 데이터 페이지를 탐색해야 합니다.
지금은 2023-05-01 일자 하루의 예시이지만 더 넓은 범위의 탐색일 경우 더 많은 다중 탐색이 발생했을 것입니다.

 

 개선해 보자 

넓은 범위 탐색을 할 경우 인덱스를 타지 않았을 때, TB_DTL의 풀 스캔이 발생하기 때문에
TB_DTL의 조회 성능을 끌어올려 줄 인덱스가 필요했습니다.

CREATE INDEX IDX_TB_DTL_USER_ID ON TB_MST(USER_ID);

TB_DTL에서 사용자 ID를 인덱스로 하는 논-클러스터링 인덱스를 만들어
TB_DTL 테이블을 액세스 하기 전에 인덱스를 먼저 태울 수 있도록 조정하였습니다.

Slow query 개선 후 실행계획을 다시 확인해 보면 아래와 같습니다.

1. 개선 후 DATES 컬럼의 좁은 범위 조회 시

0.73초에서 0.08초로 약 9배의 성능 향상

2. 개선 후 DATES 컬럼의 넓은 범위 조회 시

1.24초에서 0.02초로 약 62배의 성능 향상