[Oracle 튜닝] 오라클 실행계획 분석 및 최적화

오라클 데이터베이스에서 중요한 튜닝 기법인 실행 계획 분석 및 최적화 방법에 대해서 알아보겠습니다.

 

실행계획이란?

실행 계획은 오라클 옵티마이저가 SQL문을 실행하기 위해 선택한 단계별 작업 순서를 보여주는 로드맵입니다. 이는 쿼리 성능을 이해하고 개선하는 데 핵심적인 도구 입니다.

 

실행 계획 확인 방법

실행 계획을 확인하는 주요 방법은 다음과 같습니다.

1. EXPAIN PLAN 명령어 사용
2. SQL*Plus의 AUTOTRACE 기능
3. DBMS_XPLAN.DISPLAY_CURSOR 사용

가장 간단한 방법은 EXPLAIN PLAN을사용하는 것 입니다. 

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

실행 계획 읽는 방법

실행 계획은 트리 구조로 표시되며, 아래에서 위로, 안쪽에서 바깥쪽으로 읽습니다. 주요 구성 요소는 다음과 같습니다.

1. 작업(Operation) : 각 단계에서 수행되는 작업
2. 객체 이름 : 작업이 수행되는 테이블이나 인덱스
3. 비용(Cost) : 해당 작업의 상대적인 비용
4. 카디널리티(Cardinality) : 예상되는 결과 행 수

실행 계획 최적화 전략

1. 인덱스 활용 : 적절한 인덱스슷 생성하여 테이블 전체 스캔을 피합니다.
2. 조인 순서 최적화 : 작은 결과셋을 먼저 처리하도록 조인 순서를 조정 합니다.
3. 파티셔닝 : 대용량테이블을 파티션으로 나누어 접근 속도를 높입니다.
4. 통계 정보 최신화 : DBMS_STATS 패키지를 사용하여 정기적으로 통계를 갱신 합니다.
5. 힌트 사용 : 옵티마이저에게 특정 실행 계획을 제안할 수 있습니다. 단, 과도한 사용은 피해야 합니다.

실행 계획 분석 사례

다음은 4개의 테이블 조인 쿼리의 실행계획 분석 예시 입니다.

SELECT STATEMENT
 HASH JOIN
  HASH JOIN
   HASH JOIN
    TABLE ACCESS FULL BRICKS
    TABLE ACCESS FULL COLORS
   TABLE ACCESS FULL SHAPES
  TABLE ACCESS FULL SIZES

이 계획에서 BRICKS, COLORS, SHAPES, SIZES 테이블을 순차적으로 해시 조인하고 있습니다. 성능 개선을 위해 다음을 고려할 수 있습니다.

• 자주 사용되는 조인 컬럼에 인덱스 추가
• 테이블 접근 순서 최적화(작은 테이블부터 처리)
• 필요한 경우 병렬 처리 적용

적응형 쿼리 최적화

오라클 12c부터 도입된 적응형 쿼리 최적화는 실행 중에 계획을 조정할 수 있는 강력한 기능입니다. 이는 다음과 같은 이점을 제공합니다.

1. 런타임 통계를 기반으로 최적의 계획 선택
2. 부정확한 추정치로 인한 성능 저하 방지
3. 복잡한 쿼리에 대한 더 나은 실행 계획 생성

성능 튜닝 팁

1. 정기적인 실행 계획 검토 : EXPLAIN 명령어를 사용하여 주요 쿼리의 실행 계획을 정기적으로 분석 합니다.
2. 전체 테이블 스캔 최소화 : 인덱스 추가나 쿼리 재구성을 통해 전체 테이블 스캔을 줄입니다.
3. 비용과 카디널리티 모니터링 : 실행 계획에서 예상 비용과 카디널리티를 주의 깊게 관찰하여 비효율적인 부분을 식별 합니다.
4. 병렬 처리 활용 : 대용량 데이터 처리 시 적절한 병렬 처리를 통해 성능을 향상 시킵니다.
5. 메모리 관리 최적화 : SGA(System Global Area)와 PGA(Program Global Area)의 크기를 적절히 조정하여 메모리 사용을 최적화 합니다.

결론

실행 계획 분석과 최적화는 오라클 데이터 베이스 튜닝의 핵심 입니다. 이를 통해 쿼리 성능을 크게 개선할 수 있으며, 결과 적으로 전체 시스템의 응답 시관과 처리량을 향상 시킬 수 있습니다. 정기적인 모니터링과 분석, 그리고 적절한 최적화 기법의 적용을 통해 데이터 베이스 성능을 지속적으로 관리하세요.