Phase.1-4. 힌트(Hint) 적용 : 이전 후 실행 속도 개선

부제 : SQL 힌트(Hint) 적용을 통한 실행 계획 최적화

1. 개요

서버 이관 프로젝트에서 데이터베이스 성능을 최적화하는 것은 매우 중요한 과정이며, 특히 SQL 쿼리의 실행 계획을 적절히 유도하는 것이 핵심적인 요소 중 하나이다. 일반적으로 데이터베이스 옵티마이저(Optimizer)는 내부 알고리즘을 기반으로 최적의 실행 계획을 선택하지만, 경우에 따라 예상과 다른 부적절한 실행 계획을 선택하는 경우가 발생할 수 있다.

이러한 문제를 해결하기 위해 SQL 힌트(Hint) 를 활용하여 강제적으로 실행 계획을 조정하는 기법을 적용할 수 있다. SQL 힌트는 특정 테이블의 인덱스 활용을 강제하거나, 조인 순서를 제어하는 등 쿼리 실행 방식을 직접 제어하는 기능을 제공한다.

본 단계에서는 SPA(Static Performance Analyzer) 를 활용하여 힌트 적용 전후의 실행 계획 변화를 분석하고, AWR(Automatic Workload Repository) 리포트를 통해 성능 변화를 모니터링하여 최적의 성능을 유지할 수 있도록 한다.

---

2. SQL 힌트 적용의 필요성

SQL 힌트를 적용하는 주요 이유는 다음과 같다.

1. 비효율적인 실행 계획 방지
   • 옵티마이저가 적절하지 않은 조인 방식(Nested Loop, Hash Join 등)이나 테이블 접근 방식(Full Table Scan 등)을 선택하는 경우 이를 제어한다.
2. 강제적인 인덱스 활용
   • 옵티마이저가 인덱스를 무시하고 테이블을 풀스캔(Full Table Scan)하는 경우, 인덱스를 강제로 사용하도록 유도하여 성능을 최적화한다.
3. 조인 순서 제어
   • 조인 연산 시 옵티마이저가 예상과 다른 순서로 조인을 수행하는 경우, 이를 조정하여 최적의 순서로 실행되도록 한다.
4. 쿼리 실행 성능 개선
   • 실행 계획을 수동으로 최적화하여 CPU, 메모리 사용량을 줄이고, 빠른 응답 시간을 보장한다.

---

3. SQL 힌트 적용 방법 및 사례

1) 기본적인 힌트 적용 방법

SQL 힌트는 /*+ HINT_NAME */ 형식으로 SQL 문 내에 삽입되며, 테이블 수준, 조인 방식, 실행 우선순위 등을 조정하는 역할을 한다.

2) 주요 SQL 힌트 종류

힌트 유형설명예제

INDEX	특정 인덱스를 강제 적용	/*+ INDEX(emp emp_idx1) */
FULL	테이블 풀스캔을 강제	/*+ FULL(emp) */
USE_NL	Nested Loop Join 방식 강제	/*+ USE_NL(emp dept) */
USE_HASH	Hash Join 방식 강제	/*+ USE_HASH(emp dept) */
LEADING	조인 순서 강제	/*+ LEADING(emp dept) */

---

4. SQL 힌트 적용 예제 및 성능 비교

기존 SQL (힌트 적용 전)

SELECT e.emp_id, e.emp_name, d.dept_name      FROM employees e JOIN departments d ON e.dept_id = d.dept_id   WHERE e.salary > 5000;

SPA 실행 계획 분석 결과:

• 옵티마이저가 employees 테이블을 Full Table Scan 하여 성능 저하 발생.
• departments 테이블과 Hash Join 을 사용하면서 CPU 부하가 증가함.

SQL 힌트 적용 후 (인덱스 및 조인 방식 최적화)

SELECT /*+ INDEX(e emp_salary_idx) USE_NL(e d) */ e.emp_id, e.emp_name, d.dept_name     FROM employees e JOIN departments d ON e.dept_id = d.dept_id  WHERE e.salary > 5000;

 

적용된 최적화:

• employees 테이블의 emp_salary_idx 인덱스를 강제 사용하여 Index Scan 적용.
• 조인 방식으로 Nested Loop Join 강제 적용하여 메모리 사용량 절감.

---

5. SPA를 활용한 성능 비교

SQL 힌트 적용 전후의 실행 계획을 SPA(Static Performance Analyzer) 로 분석하여 성능 개선 효과를 확인할 수 있다.

항목힌트 적용 전힌트 적용 후

실행 시간(ms)	1200	300
CPU 사용량(%)	80	35
I/O 부하	높음	낮음

• 실행 시간이 약 75% 단축됨.
• CPU 및 I/O 부하가 크게 감소하여 데이터베이스 성능이 향상됨.

---

6. AWR을 활용한 성능 모니터링

SQL 힌트 적용 후, AWR(Automatic Workload Repository) 리포트를 활용하여 성능 변화를 모니터링한다.

1. 쿼리 실행 빈도 및 응답 시간 분석
   • 변경된 SQL이 운영 환경에서 어떻게 실행되는지 모니터링한다.
   • 평균 실행 시간 및 최대 응답 시간을 비교하여 개선 효과를 측정한다.
2. CPU 및 I/O 사용률 비교
   • 힌트 적용 전후 CPU 및 I/O 부하가 어떻게 변화했는지 분석한다.
   • 불필요한 자원 사용이 감소했는지 확인한다.
3. 인덱스 활용 여부 검증
   • AWR에서 SQL 실행 이력을 조회하여 예상한 인덱스가 실제로 적용되었는지 확인한다.

---

7. SQL 힌트 적용 후 기대 효과

• 실행 계획 최적화: 옵티마이저가 비효율적인 계획을 선택하는 것을 방지하고, 최적의 실행 경로를 유도할 수 있다.
• 데이터베이스 부하 감소: CPU 및 I/O 자원 사용량을 최적화하여, 전반적인 성능을 향상시킨다.
• 쿼리 응답 속도 향상: 최적의 실행 계획을 적용하여 빠른 데이터 검색이 가능해진다.
• 운영 환경 안정성 확보: AWR을 통해 지속적으로 성능을 모니터링하여, 예기치 않은 성능 저하를 방지할 수 있다.

---

8. 결론

SQL 힌트 적용은 서버 이관 후 데이터베이스 성능을 유지하고 최적화하는 데 중요한 역할을 한다. 옵티마이저가 예상과 다르게 부적절한 실행 계획을 선택하는 경우, 적절한 힌트를 적용하여 이를 교정할 수 있으며, 이를 통해 시스템의 안정성을 확보할 수 있다.

본 프로젝트에서는 SPA를 활용하여 실행 계획을 분석하고, AWR을 이용하여 성능 변화를 모니터링 함으로써, SQL 힌트가 적절히 적용되었는지 지속적으로 점검할 계획이다. 이러한 접근 방식을 통해 성능 저하를 방지하고, 새로운 서버 환경에서도 최적의 성능을 유지할 수 있도록 최선을 다할 것이다.