원래 테이블 수도 많아서 DBA 쪽에서 일괄적으로 해주는 작업이라고 들었는데, 기존에 시스템에서 이행된 쿼리라 작업이 되지 않아 쿼리 PLAN이 최적화되지 않았다고 들었다.
업무 중 3개의 테이블을 조인하여 조회쿼리를 만들어서 조회해보니 조회시간이 20s 이상 걸리는 상황이 생겼는데, 해당 쿼리를 5s 이하로 줄인 방법에 대해 적어보려고 한다.
사용 명령어
ANALYZE TABLE [테이블명] COMPUTE STATISTICS;
DB 성능 튜닝에서 ANALYZE TABLE [테이블] COMPUTE STATISTICS를 사용하면 쿼리 최적화에 필요한 최신 통계 정보를 수집하여 옵티마이저가 효율적인 실행 계획을 수립할 수 있게 도와준다고 한다. 여기서 통계 정보는 테이블의 데이터 분포, 인덱스 사용 빈도, 행의 수와 같은 정보를 포함하는데, 이 데이터는 쿼리 실행 시에 어느 인덱스를 사용해야 할지 결정하는 데 핵심적인 역할을 한다.
이게 무슨 내용이냐면,
쿼리가 최적화되지 않았다면 DB옵티마이저가 알아서 PLAN을 세우고 최적의 경로를 찾아준다. DB옵티마이저는 기본적으로 테이블/인덱스에 대해 통계 정보를 내는데, 내가 20s 이상 걸린 문제의 쿼리가 인덱스스캔을 돌면서 불필요한 경로를 한것이다. 오히려 /*+full */ 힌트를 넣어 풀스캔을 돌게 하여도 속도를 줄일 수 있었다.
그래서 저 ANALYZE TABLE [테이블] COMPUTE STATISTICS 를 사용하면 최신 통계를 수집해주고, 옵티마이저가 데이터에 어떻게 하면 더 효율적으로 접근할지 통계를 내준다고 보면 된다.
예시
1. 쿼리 최적화 전
SELECT a.col1, b.col2, c.col3
FROM table_a a
JOIN table_b b ON a.id = b.id
JOIN table_c c ON b.id = c.id
WHERE a.some_column = '조건';이 쿼리는 FROM 절에 세 개의 테이블을 조인하며, 옵티마이저가 오래된 통계 정보를 참고하여 최적이 아닌 인덱스를 선택하거나 전체 테이블 스캔을 수행할 수도 있다.
2. 쿼리 최적화 후
ANALYZE TABLE table_a COMPUTE STATISTICS;
ANALYZE TABLE table_b COMPUTE STATISTICS;
ANALYZE TABLE table_c COMPUTE STATISTICS;
/* 테이블에 대해 ANALYZE TABLE을 수행한 후 쿼리를 실행*/
SELECT a.col1, b.col2, c.col3
FROM table_a a
JOIN table_b b ON a.id = b.id
JOIN table_c c ON b.id = c.id
WHERE a.some_column = '조건';위 명령어를 통해 옵티마이저가 테이블, 인덱스의 최신 상태를 반영하게 되어 불필요한 인덱스 스캔과 테이블 스캔이 줄어들고, 결과적으로 조회 시간이 크게 개선된다.
통계 수집을 통해 효율적인 실행 계획을 세울 수 있기 때문에, 특히 데이터가 자주 변경되는 테이블이나 인덱스를 사용하는 쿼리의 경우 주기적으로 ANALYZE TABLE을 실행하는 것이 좋다고 한다.
