SQL 성능 튜닝 기본 개념 | Explain Plan 사용법

데이터베이스 성능 최적화는 개발자와 DBA에게 중요한 작업 중 하나입니다. SQL 문이 비효율적으로 작성되면 시스템 자원이 불필요하게 소비되고, 응답 시간이 길어질 수 있습니다. 이러한 문제를 사전에 분석하고 개선하기 위해 EXPLAIN PLAN을 활용할 수 있습니다.

이 글에서는 EXPLAIN PLAN의 개념과 사용법, 그리고 실행 계획을 해석하는 방법에 대해 알아보겠습니다.

 

1. EXPLAIN PLAN이란?

EXPLAIN PLAN은 SQL 문이 실행될 때 옵티마이저(Optimizer)가 선택한 실행 계획을 보여주는 기능입니다. 이를 통해 쿼리가 어떤 방식으로 실행될지를 예측하고, 성능을 저하시키는 요인을 파악할 수 있습니다.

주요 확인 요소:

• 실행 순서 및 접근 방법 (Full Table Scan, Index Scan 등)
• 조인 방식 (Nested Loop, Hash Join 등)
• 예상 비용 (Cost) 및 실행 시간

 

2. EXPLAIN PLAN 실행 방법

2.1 실행 계획 생성하기

EXPLAIN PLAN을 사용하려면 먼저 다음과 같이 실행해야 합니다:

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

이렇게 실행하면 실행 계획이 PLAN_TABLE이라는 테이블에 저장됩니다.

2.2 실행 계획 조회하기

실행 계획을 확인하려면 다음 쿼리를 실행합니다:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

Explain plan을 쿼리문을 통해 보는 방법도 있지만 툴을 사용하고 있다면 자체적으로 단축키를 통해 실행 계획을 조회할 수 있어서 이를 활용하는 것이 편하다.

 

3. 실행 계획 해석 방법

3.1 주요 컬럼 설명

• ID: 실행되는 작업의 순서를 나타냅니다. 숫자가 높을수록 내부 단계입니다.
• Operation: 수행되는 작업을 나타냅니다 (예: TABLE ACCESS, INDEX SCAN, HASH JOIN 등).
• Object Name: 액세스되는 테이블 또는 인덱스의 이름입니다.
• Cost: 쿼리 실행 비용을 의미하며, 낮을수록 효율적인 실행 계획입니다.

3.2 Operation 유형

1. TABLE ACCESS FULL
   • 전체 테이블을 읽는 방식으로, 데이터 양이 많을 경우 성능 저하 가능성이 큽니다.
2. INDEX RANGE SCAN
   • 인덱스를 사용하여 특정 범위의 데이터를 검색하는 방식으로 효율적입니다.
3. INDEX UNIQUE SCAN
   • 유니크 인덱스를 활용하여 한 개의 결과만 가져오는 방식입니다.
4. NESTED LOOPS JOIN
   • 작은 데이터셋을 반복적으로 검색할 때 효과적인 조인 방식입니다.
5. HASH JOIN
   • 대용량 데이터 조인 시 사용되며, 임시 메모리 공간을 활용합니다.

 

4. 성능 튜닝을 위한 조치

EXPLAIN PLAN을 분석한 후, 성능을 향상시키기 위해 다음과 같은 조치를 고려할 수 있습니다:

4.1 인덱스 활용 여부 확인

• TABLE ACCESS FULL이 발생하면 인덱스를 추가하여 INDEX SCAN으로 변경할 수 있는지 확인합니다.

CREATE INDEX idx_emp_dept ON employees(department_id);

4.2 조인 방식 최적화

• NESTED LOOPS가 아닌 HASH JOIN이 필요할 경우, 옵티마이저 힌트를 사용하여 변경할 수 있습니다.

SELECT /*+ USE_HASH(emp) */ *
FROM employees emp JOIN departments dept
ON emp.department_id = dept.department_id;

4.3 불필요한 데이터 읽기 최소화

• 필요한 컬럼만 조회하도록 **SELECT *** 대신 특정 컬럼을 명시합니다.

SELECT employee_id, first_name, department_id FROM employees;

---

5. 마무리

EXPLAIN PLAN을 활용하면 SQL 쿼리의 실행 방식과 성능 문제를 사전에 파악하여 최적화할 수 있습니다. 이를 통해 데이터베이스 리소스를 효율적으로 사용하고, 빠른 응답 속도를 유지할 수 있습니다.

앞으로 SQL 최적화를 진행할 때는 항상 EXPLAIN PLAN을 활용하여 실행 계획을 분석하는 습관을 들이는 것이 중요합니다. 🚀