오라클 인덱스 힌트 강제 사용 방법, 성능 최적화 가이드

DB 서버 사양은 16 core, 64gb, 데이터는 4GB, 30 TPS 부하에 CPU 사용률 100%라면..

 

혹시 실행되는 쿼리가 제대로 인덱스를 사용하지 않는 건 아닐까요? 또 분명히 인덱스 힌트를 적용했는데도 제대로 동작하지 않아 답답하셨던 경험이 있으신가요?

 

오늘은 저의 데이터베이스 튜닝 경험을 바탕으로, 인덱스 힌트를 확실하게 적용하는 방법을 알려드리겠습니다.

 

오라클 인덱스 힌트 강제 사용 방

『 '데이널'의 컨텐츠에 포함된 정보는? 』

 

인덱스 힌트가 안 먹히는 경우

인덱스 힌트가 제대로 동작하지 않는 주요 원인은 크게 세 가지입니다. 첫째, 힌트 구문에서 '+' 기호를 누락하는 경우입니다. 힌트는 반드시 /*+ */ 형식으로 작성해야 하며, + 기호가 없으면 일반 주석으로 처리됩니다.

 

둘째, 테이블 별칭 사용 시 발생하는 오류입니다. 쿼리에서 테이블 별칭을 사용할 경우, 힌트에도 반드시 동일한 별칭을 사용해야 합니다. 예를 들어, FROM emp e라고 작성했다면, 힌트에서도 e를 사용해야 합니다.

 

셋째, 잘못된 인덱스명을 지정하는 경우입니다. 실제 존재하지 않는 인덱스를 지정하면 힌트는 무시됩니다. 이런 경우가 아닌데도 힌트에 지정한 인덱스를 사용하지 않는다면 오라클 옵티마이져가 통계 정보를 이용해 더 효율적인 실행계획은 만든 것입니다.

 

하지만 오라클이 옵티마이저는 95점짜리 입니다. 5% 정도는 답이 틀릴 수도 있다는 이야기죠. 하지만 정확도는 점점 높아질 거라 생각합니다. 

 

인덱스 힌트의 종류와 사용법

인덱스 힌트는 크게 다음과 같은 종류가 있습니다.

1. INDEX 힌트

지정된 인덱스를 스캔하도록(INDEX SCAN) 유도합니다.

SELECT /*+ INDEX(e emp_idx) */ * FROM emp e WHERE depart_id = 10 ;

2. INDEX_DESC 힌트

지정된 인덱스를 역순으로 Scan하도록 유도합니다. 

SELECT /*+ INDEX_DESC(e emp_idx) */ * FROM emp e WHERE hire_date > '2023-01-01' ;

3. INDEX_FFS 힌트

인덱스를 빠르게 풀 스캔하는 INDEX FAST FULL SCAN 유도합니다. 

SELECT /*+ INDEX_FFS(e emp_idx) */ count(*) FROM emp e ;

 

복합 인덱스 및 인덱스 힌트 검증

인덱스 힌트 작성 시 가장 중요한 것은 정확한 문법 준수입니다. 다음은 실제 업무에서 자주 사용되는 예제입니다.

 

1. 복합 인덱스 사용

SELECT /*+ INDEX(e emp_dept_idx) INDEX(d dept_idx) */ e.emp_id, e.first_name, d.depart_name FROM emp e          , depart d WHERE e.depart_id = d.depart_id

 

주의 사항은 힌트는 항상 SELECT 문 바로 뒤에 작성하며, 복합 인덱스의 경우 선행 칼럼 순서를 고려해야 합니다

 

2. 인덱스 힌트 검증

인덱스 힌트가 제대로 적용되었는지 확인하는 방법은 실행 계획을 통해 가능합니다. 실행 계획 확인 방법은 아래 sql로 확인 가능합니다. 

EXPLAIN PLAN FOR SELECT /*+ INDEX(e emp_idx) */ * FROM empl e WHERE salary > 5000 ; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY) ;

 

마무리

인덱스 힌트는 강력한 성능 최적화 도구이지만, 신중하게 사용해야 합니다. 특히 아쉬운 점은 데이터베이스 버전이 업그레이드될 때마다 힌트의 동작이 달라질 수 있다는 것입니다.

 

이를 보완하기 위해서는 정기적인 실행 계획 검토와 성능 모니터링이 필요합니다. 또한 힌트 사용 전에 항상 테스트 환경에서 충분한 검증을 거치는 것이 좋습니다.

 

오라클 힌트(hint) 종류 및 사용법

오라클 힌트(hint) 종류 및 사용법