오라클 SQL 실행 계획 분석하기 EXPLAIN PLAN

 

---

1. EXPLAIN PLAN이란?

오라클 데이터베이스에서 EXPLAIN PLAN은 SQL 문장이 실행될 때 데이터베이스가 수행할 예상 작업의 세부 단계를 보여줍니다.
이 정보는 쿼리의 성능을 분석하고 개선할 수 있도록 도와주는 중요한 도구입니다.

• EXPLAIN PLAN은 실행 계획을 예측하며, 실제 실행 중 발생할 수 있는 실행 시간, 트랜잭션 상태 등은 반영되지 않습니다.
• 데이터 접근 방식(테이블 스캔, 인덱스 사용 여부 등)과 작업 순서를 확인할 수 있습니다.

---

2. EXPLAIN PLAN 기본 개념

실행 계획에서 확인할 수 있는 정보

1. 연산 작업: SELECT, JOIN, SORT와 같은 SQL 연산.
2. 데이터 접근 방식: FULL TABLE SCAN, INDEX SCAN 등.
3. 연산 순서: 작업이 수행되는 순서와 단계.

주요 용어

• FULL TABLE SCAN: 테이블의 모든 행을 읽는 방식.
• INDEX SCAN: 인덱스를 사용해 데이터를 검색.
• SORT: 데이터를 정렬.
• JOIN: 두 개 이상의 테이블을 결합.

---

3. EXPLAIN PLAN 사용법

실행 계획 생성

기본 문법

EXPLAIN PLAN FOR
SQL문;

실행 결과 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---

4. 예제: 실행 계획 생성 및 분석

예제 1: 단순 SELECT 문 실행 계획

1) 실행 계획 생성

EXPLAIN PLAN FOR
SELECT * 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = 10;

2) 실행 계획 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과:

ID OPERATION OBJECT_NAME OPTIONS

0	SELECT STATEMENT
1	TABLE ACCESS	EMPLOYEES	FULL

• 분석:
  • TABLE ACCESS FULL: 테이블 전체를 스캔합니다.
  • WHERE 조건을 만족하는 데이터를 찾기 위해 모든 행을 검사하므로 성능이 저하될 수 있습니다.

---

예제 2: 인덱스를 사용하는 SELECT 문

1) 인덱스 생성

CREATE INDEX idx_employees_dept ON EMPLOYEES(DEPARTMENT_ID);

2) 실행 계획 생성

EXPLAIN PLAN FOR
SELECT * 
FROM EMPLOYEES 
WHERE DEPARTMENT_ID = 10;

3) 실행 계획 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과:

ID OPERATION OBJECT_NAME OPTIONS

0	SELECT STATEMENT
1	INDEX RANGE SCAN	IDX_EMPLOYEES_DEPT

• 분석:
  • INDEX RANGE SCAN: 인덱스를 사용하여 DEPARTMENT_ID가 10인 행을 빠르게 검색합니다.
  • 성능이 FULL TABLE SCAN보다 크게 향상됩니다.

---

예제 3: JOIN 실행 계획

1) SQL 작성

EXPLAIN PLAN FOR
SELECT E.EMPLOYEE_ID, E.NAME, D.DEPARTMENT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

2) 실행 계획 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과:

ID OPERATION OBJECT_NAME OPTIONS

0	SELECT STATEMENT
1	NESTED LOOPS
2	TABLE ACCESS FULL	EMPLOYEES
3	TABLE ACCESS BY INDEX ROWID	DEPARTMENTS
4	INDEX UNIQUE SCAN	IDX_DEPT_ID

• 분석:
  • NESTED LOOPS: 두 테이블 간 조인을 수행.
  • TABLE ACCESS BY INDEX ROWID: DEPARTMENTS 테이블의 인덱스를 사용하여 데이터를 검색.
  • 성능은 조인 방식(NESTED LOOPS, HASH JOIN 등)에 따라 달라집니다.

---

5. 실행 계획에서 주의할 점

1. FULL TABLE SCAN
   테이블의 모든 데이터를 읽는 방식으로, 대량의 데이터를 다룰 경우 성능 문제가 발생할 수 있습니다.
   인덱스를 활용하거나 조건절을 최적화해 이를 줄여야 합니다.
2. 인덱스 사용 여부 확인
   실행 계획에 INDEX RANGE SCAN 또는 INDEX UNIQUE SCAN이 나타나야 인덱스가 사용되고 있음을 의미합니다.
3. 조인 방식
   JOIN이 사용된 경우 NESTED LOOPS, HASH JOIN, MERGE JOIN 등 조인 방식이 표시됩니다.
   데이터 양과 조인 조건에 따라 최적의 방식이 달라질 수 있습니다.
4. SORT
   ORDER BY 또는 GROUP BY가 포함된 쿼리에서는 SORT 연산이 발생할 수 있습니다.
   정렬을 최소화하려면 인덱스를 사용하거나 쿼리 구조를 변경하세요.

---

6. 실행 계획 최적화 예제

기존 쿼리

SELECT * 
FROM EMPLOYEES
WHERE UPPER(NAME) = '홍길동';

문제점: UPPER 함수가 적용되면 인덱스를 사용할 수 없습니다.
해결책: 함수 기반 인덱스를 생성합니다.

개선된 쿼리

CREATE INDEX idx_employees_upper_name 
ON EMPLOYEES(UPPER(NAME));

EXPLAIN PLAN FOR
SELECT * 
FROM EMPLOYEES
WHERE UPPER(NAME) = '홍길동';

결과 분석:

• 실행 계획에 INDEX RANGE SCAN이 표시되어 인덱스가 사용됩니다.

---

7. 마치며

EXPLAIN PLAN은 SQL 실행 계획을 분석하고 최적화 기회를 식별하는 데 유용한 도구입니다.
실행 계획에서 쿼리의 성능 문제를 발견하고, 인덱스 설계와 SQL 구조를 최적화함으로써 데이터베이스 성능을 개선할 수 있습니다.
이 도구를 효과적으로 활용해 더 나은 데이터베이스 관리와 성능 향상을 이루어보세요.