Oracle Toad 실행 계획 분석

Toad는 오라클 데이터베이스 관리와 SQL 최적화에 필수적인 도구입니다. 실행 계획 분석은 SQL쿼리의 성능 병목 현상을 파악하고 최적화하는데 매우 유용합니다. 이번 글에서는 Toad에서 실행 계획을 분석하는 방법과 이를 활용한 SQL 최적화의 상세한 과정을 알아보겠습니다.

---

1. 실행 계획이란 ?

실행 계획(Execution Plan) 은 SQL 쿼리가 데이터베이스에서 어떻게 처리되는지를 나타내는 로드맵입니다. 쿼리 실행 시 옵티마이저(Optimizer)가 선택한 접근 방식과 연산 순서를 보여줍니다. 실행 계획을 이해하면 성능 병목 지점을 파악하고 쿼리를 최적화하기 위한 정보를 얻을 수 있습니다.

 

2. Toad에서 실행 계획 확인 방법

1)  실행 계획 열기

1. 쿼리 작성 : Toad의 SQL 에디터 창에 최적화가 필요한 SQL 쿼리를 작성합니다.
2. Explain Plan 실행 : SQL 에디터에서 쿼리를 작성한 후, 툴바에서 "F5" 를 누르거나 "Explain Plan" 버튼을 클릭합니다.
3. 결과 확인 : 실행 계획 창이 열리며, 각 단계에 대한 정보가 표 형태로 표시됩니다.

2) 실행 계획 창의 주요 항목

• Operation : SQL 쿼리가 데이터에 접근하고 처리하는 방식을 나타냅니다. (Table Access Full, Index Range Scan)
• Object Name : 데이터가 검색되거나 조인되는 테이블 또는 인덱스의 이름을 표시합니다.
• Rows : 각 단계에서 처리되는 행의 예상 수를 나타냅니다.
• Cost : 옵티마이저가 계산한 상대적인 실행 비용을 나타냅니다. 값이 낮을수록 효율적인 쿼리입니다.
• Cardinality : 예상 결과 집합의 크기를 나타냅니다.
• Access Predicates : 각 단계에서 적용된 조건(필터 조건)을 보여줍니다.
• Filter Predicates : 특정 단계에서 필터링된 조건을 나타냅니다.

3. 실행 계획 분석의 핵심 포인트

1) Full Table Scan

1. 문제점 : Full Table Scan 테이블의 모든 행을 스캔하므로 성능 저하의 주요 원인 중 하나입니다.
2. 개선 방법
   • 자주 검색되는 컬럼에 인덱스를 추가합니다.
   • Where 절에 조건이 있는 경우 해당 조건에 인덱스를 활용합니다.

-- 예: 인덱스 추가
CREATE INDEX idx_department_id ON EMPLOYEES(DEPARTMENT_ID);

 

2) Index Scan

인덱스 스캔은 데이터 검색 시 효율적입니다. 그러나 인덱스를 잘못 사용하거나 과도하게 많은 행을 반환할 경우 성능 문제가 발생할 수 있습니다.

1. 개선 방법
   • 반환되는 행 수를 줄이도록 Where 조건을 구체화합니다.
   • 필요 없는 인덱스를 삭제하여 옵티마이저가 더 효율적인 실행 계획을 선택하도록 합니다.

3)  조인 연산

조인은 실행 계획에서 큰 영향을 미칩니다. Toad에서 조인 순서와 방식을 분석합니다.

1. 문제점 : 잘못된 조인 순서나 방식이 선택될 경우 성능이 저하됩니다.
2. 개선 방법
   • 옵티마이저 힌트를 사용하여 효율적인 조인 방식을 강제합니다.
   • 조인에 사용되는 컬럼에 적절한 인덱스를 추가합니다.

-- 힌트를 사용한 조인 최적화
SELECT /*+ USE_NL(A B) */ A.EMPLOYEE_NAME, B.DEPARTMENT_NAME
FROM EMPLOYEES A
JOIN DEPARTMENTS B ON A.DEPARTMENT_ID = B.DEPARTMENT_ID;

 

4) Sort 연산

Sort 연산은 대규모 데이터 정렬 시 성능 병목이 될 수 있습니다.

1. 개선 방법
   • Order by 절에서 사용되는 컬럼에 인덱스를 추가하여 정렬 비용을 줄입니다.
   • 불필요한 정렬을 제거합니다.

4. 실행 계획 최적화를 위한 추가 팁

1) 통계 정보 수집

옵티마이저는 최신 통계 정보를 기반으로 실행 계획을 생성합니다. 테이블 또는 인덱스의 통계 정보가 오래되었거나 부정확하면 잘못된 실행 계획이 선택될 수 있습니다.

-- 통계 정보 갱신
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

 

2) 바인드 변수 사용

쿼리에서 하드 코딩된 값 대신 바인드 변수를 사용하면 SQL 파싱 비용을 줄이고 성능을 향상 시킬 수 있습니다.

-- 바인드 변수 사용 예제
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = :dept_id;

 

3) 병렬 처리

병렬 처리는 대규모 데이터를 처리할 때 유용합니다. Toad에서 병렬 실행 계획이 제대로 활용되고 있는지 확인합니다.

SELECT /*+ PARALLEL(EMPLOYEES, 4) */ * FROM EMPLOYEES;

 

4) 힌트 활용

힌트를 사용하여 옵티마이저가 더 효율적인 실행 계획을 선택하도록 강제할 수 있습니다.

• Index : 특정 인덱스를 사용하도록 강제
• Full : 테이블 풀 스캔 강제
• Parallel : 병렬 처리를 활성화
• Leading : 조인 순서를 지정

SELECT /*+ INDEX(EMPLOYEES idx_department_id) */ *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 10;