[ Oracle ] "SQL의 튜닝 == 힌트 사용" 일까?

 

안녕하세요.

 

오늘은 Oracle에서 SQL 튜닝이라는 주제에 대하여 말해보려고 합니다.

 

아래 내용은 제 개인적인 생각이 포함된 내용입니다.

 

1. "SQL 튜닝 == 적절한 힌트 사용" 같은 말일까?

 

/*+ ordered use_nl(table)*/

SQL을 다루다보면 위와 비슷한 hint를 사용하는 경우를 흔히 볼 수 있다.

 

허나 실제로 SQL을 작성하여 실행하면 옵티마이저가 대부분 올바른 선택을 하기에 힌트에 대한 필요성은 크게 느끼지 못하게 되며, 오히려 hint를 사용하는게 옵티마이저의 올바른 실행계획을 방해하여 더욱 성능면에서 상대적으로 안좋아지는 경우도 잦게됩니다.

 

2. 그렇다면, 실제로 튜닝 작업을 하는 경우의 대부분이 힌트 추가 작업보다는 인덱스의 생성이나 변경의 작업일까? 

Oracle에서 쿼리 튜닝 작업을 할 때 힌트(Hint)를 사용하는 경우도 있지만, 대부분의 튜닝 작업은 인덱스 생성/변경, 통계 정보 관리, 쿼리 구조 개선과 같은 작업에 집중됩니다. 힌트는 주로 옵티마이저가 비효율적인 실행 계획을 선택할 때 임시로 개입하는 도구로 사용되며, 아래와 같은 이유로 힌트의 사용이 제한적일 수 있습니다:

(1) 옵티마이저의 역할

• Oracle 옵티마이저는 통계 정보를 기반으로 최적의 실행 계획을 자동으로 선택하려고 합니다. 따라서 정확한 통계 정보가 제공되고, 적절한 인덱스가 생성되어 있다면 대부분의 경우 수동 개입 없이도 효율적인 실행 계획이 생성됩니다.

(2) 힌트 사용의 한계

• 유지보수 문제: 힌트를 사용하면 코드에 특정 실행 계획을 강제하므로, 데이터가 변경되거나 통계가 달라졌을 때 힌트를 수정해야 할 수 있습니다.
• 예측 가능성 저하: 힌트는 특정 시점의 데이터 분포와 통계 정보에 의존하기 때문에, 시간이 지나면서 비효율적이 될 가능성이 있습니다.
• 옵티마이저 개선 방해: Oracle은 지속적으로 옵티마이저를 개선하고 있으므로, 힌트가 오히려 최신 기능을 활용하지 못하게 할 수도 있습니다.

(3) 실제 튜닝 작업에서의 주요 포인트

대부분의 튜닝 작업은 힌트보다 아래의 작업에 집중됩니다.

 

(3.1) 인덱스 생성 및 변경

• 적절한 인덱스: 자주 조회되는 컬럼에 대해 B-tree 또는 비트맵 인덱스를 생성합니다.
• 복합 인덱스: WHERE 절에 여러 컬럼이 결합되는 경우 복합 인덱스를 생성하여 성능을 개선합니다.
• 인덱스 유지보수: 불필요하거나 오래된 인덱스를 제거하거나 재구성합니다.

(3.2) 통계 정보 관리

• ANALYZE나 DBMS_STATS 사용: 옵티마이저가 올바른 결정을 내릴 수 있도록 최신 통계 정보를 제공합니다.
• 히스토그램: 데이터가 skew(편향)된 경우 히스토그램을 생성하여 옵티마이저가 더 나은 선택을 하도록 유도합니다.

(3.3) 쿼리 리팩토링

• 불필요한 조인 제거: 실제로 필요하지 않은 테이블을 조인에서 제외합니다.
• 서브쿼리 최적화: 서브쿼리를 뷰나 WITH절(CTE)로 대체하거나 조인으로 변환합니다.
• WHERE 조건 최적화: WHERE 조건에 인덱스를 잘 활용하도록 조건 순서를 조정합니다.

(3.4) 데이터베이스 구조 최적화

• 파티셔닝: 대량의 데이터를 처리하는 테이블에 대해 파티셔닝을 적용하여 특정 범위의 데이터만 읽도록 합니다.
• 테이블 설계 변경: 데이터 정규화를 줄이거나, 물리적으로 테이블 설계를 변경하여 I/O를 줄입니다.

(3.5) 실행 계획 분석

• EXPLAIN PLAN: 쿼리의 실행 계획을 분석하여 비효율적인 단계(Full Table Scan, Nested Loops 등)를 찾아냅니다.
• AUTOTRACE, SQL Monitor: 실제 실행된 쿼리의 자원 사용량과 실행 경로를 확인합니다.

(4) 힌트를 사용하는 경우

힌트는 대부분 다음과 같은 상황에서 사용됩니다:

• 옵티마이저가 잘못된 실행 계획을 선택할 때: 특정 인덱스를 강제로 사용하도록 강제하거나 병렬 처리를 유도합니다.
• 임시적인 성능 문제 해결: 코드 변경이 불가능하거나 통계 정보를 바로 갱신할 수 없을 때 사용합니다.
• 테스트와 실험: 특정 힌트를 적용했을 때 성능 차이를 비교하기 위해 사용합니다.

 

또한 위 (3)번에서 설명한 WHERE 조건 최적화의 경우도 대부분의 경우 옵티마이저가 순서를 자동으로 최적화하지만, 다음과 같은 상황에서는 직접적인 영향을 줄 수 있습니다.

1. 힌트(Hint)가 사용된 경우
   • 특정 조건 순서를 강제하거나 옵티마이저가 선택한 계획을 변경하려고 힌트를 사용하면, 조건 순서가 실행 계획에 영향을 미칠 수 있습니다.
2. 옵티마이저가 부정확한 통계를 가지고 있는 경우
   • 최신 통계 정보가 없으면 옵티마이저가 잘못된 실행 계획을 선택할 가능성이 있습니다. 이 경우, 조건 순서를 수동으로 조정하거나 통계를 갱신해야 할 수 있습니다.
3. 복잡한 조건 조합
   • 여러 테이블 간의 조인, 서브쿼리, 복합 조건 등이 얽혀 있는 경우 옵티마이저가 항상 최적의 순서를 보장하지 못할 수 있습니다. 이 경우, 실행 계획을 확인하여 필요하면 조정합니다.

 

3. WHERE 조건 작성 시 주의할 점

WHERE 절에서 조건 순서는 신경 쓰지 않아도 되는 경우가 많지만, 다음 사항을 고려하는 것이 중요합니다:

1. 인덱스를 활용 가능한 조건 작성
   • WHERE UPPER(column) = 'VALUE' → 비추천 (인덱스 사용 불가)
   • WHERE column = LOWER('VALUE') → 추천 (인덱스 사용 가능)
2. 불필요한 조건 제거
   • 중복되거나 항상 참인 조건은 제거하여 쿼리를 간결하게 만듭니다.
3. 옵티마이저의 실행 계획 확인
   • EXPLAIN PLAN 또는 AUTOTRACE를 사용하여 쿼리가 인덱스를 적절히 활용하는지 확인합니다.
4. 통계 정보 최신화
   • 옵티마이저가 올바른 실행 계획을 생성하려면 최신 통계 정보가 필수입니다. DBMS_STATS를 사용해 통계를 주기적으로 갱신하세요.

 

4. 최종 간략 정리

옵티마이저가 올바르게 동작한다면 힌트 사용은 최소화하는 것이 좋습니다.

대부분의 튜닝 작업은 적절한 인덱스 생성/변경, 통계 정보 최신화, 쿼리 구조 개선과 같은 근본적인 접근 방식에 의존합니다. 하지만, 옵티마이저가 예외적으로 비효율적인 실행 계획을 선택하는 경우에는 힌트를 활용할 수 있는 지식이 유용할 것입니다.

 

 

감사합니다.