인덱스와 DB통계

오라클에서 쿼리를 실행시키는데 이상하게 특정 쿼리가 무한로딩이 걸리는 일이 생겼다.

정확히는 특정 테이블에서만 발생했었다.

---

선결론 : 테이블의 통계정보 문제였다.

ANALYZE 명령어로 해결되었다.

---

 

다음과 같은 쿼리였다.

select COUNT(1) AS CNT
from (select * from T_TAA where F_STATUS in ('W', 'P')) T1
         join T_TBB T2 on T2.F_CD = T1.F_CD;

일단 실행계획부터 확인해봤다.

정상적으로 인덱스도 타고 cost 소모도 크지않았다.

 

두번째로 TEMP 사용량 체크

SELECT tablespace_name, used_blocks, free_blocks
FROM v$sort_segment;

용량 문제 없었다.

 

세번째로 PGA 메모리확인

SELECT * FROM v$pgastat WHERE name LIKE '%total PGA allocated%';

문제없었다.

 

원래 확인하는 부분은 실행계획과 인덱스부분만 확인했는데 하도 해결이 안되어서 

두번째, 세번째 부분을 DBA에게 부탁해서 확인해봤지만 문제없던 상황

 

이후 문제가 해결되었는데 ANALYZE 명령어로 오래된 통계정보를 갱신하였더니 해결되었다.

-- 전체 스키마 통계 수집 (권장)
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'PHS');

-- 특정 테이블만 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'PHS', tabname => 'T_TBB');

-- 전체 데이터베이스 통계 수집
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

 

원인은 T_TBB 의 통계정보가 깨져있었던것이였다.(여기에 추가로 잘못된 인덱스지정도 한 몫 한듯하다)

T_TAA 에도 의미없는 인덱스가 설정되어 있었고

T_TBB 테이블에 다량의 데이터가 지워지고 삽입되는게 반복되지만 통계갱신은 너무 오래된게 큰 이유였던것 같다.

그래서 오라클 옵티마이저가 잘못된 실행계획을 세워서 정상적으로 실행이 안된듯하다.

DML (INSERT/UPDATE/DELETE) 대량으로 한 후에는 통계 수집을 주기적으로 해주자

---

추가로 인덱스의 데이터분포도 문제가 있었다.

확인결과 T_TAA에 INDEX1 이 데이터 분포가 쓰레기였다고 한다. (인덱스는 고유 데이터일수록 좋다)

F_STATUS 라는 필드의 선택도가 0.005 였다고 하는데... 0.1 이하는 사용안하는게 낫다고 하더라...

다음은 지피티에게 물어본 좋은 데이터 분포에 대한 답변이다.

✅ 좋은 데이터 분포 (인덱스 효율적)

• 선택도(Selectivity)가 높은 경우
• 즉, 컬럼의 값들이 고르게 분포되어 있고, 중복이 적음
• 예: 주민번호, 이메일, 주문번호, 사용자ID 등

🔹 선택도 = 고유값 개수 / 전체 행 수

• 선택도 1에 가까울수록 인덱스 효율이 매우 좋음
• 0.1 (10%) 이하이면 인덱스 효율이 낮고, 풀스캔이 더 나을 수도 있음

---

❌ 나쁜 데이터 분포 (인덱스 비효율적)

• 컬럼에 중복값이 많음
• 예: 성별(M/F), 상태코드(0/1/2), 나라코드(대한민국만 있음)
• 이런 경우에는 인덱스를 써도 결국 많은 행이 걸리므로 인덱스를 잘 안 씀
  (오히려 정렬/디스크 IO만 늘어남)

---

📌 예시

총 행 수고유값 수선택도인덱스 효과

1,000,000	900,000	0.9	매우 좋음 ✅
1,000,000	100	0.0001	매우 나쁨 ❌
1,000,000	50,000	0.05	애매함 ⚠️

 

DB에 대해 한번 배우게 된 이슈였다.