[Oracle] SQL 튜닝: 실행 계획 분석으로 쿼리 성능 개선하기

Oracle SQL 튜닝: 실행 계획을 분석하고 쿼리 성능을 개선하는 방법

SQL 쿼리 성능이 떨어지면 데이터베이스의 응답 시간이 길어지고, 시스템 전체 성능에 영향을 미칠 수 있습니다. 특히 Oracle Database에서는 쿼리 최적화가 중요한데, 그 핵심은 바로 **실행 계획(Execution Plan)**을 분석하고 이를 개선하는 것입니다. 

 

1. 실행 계획(Execution Plan) 이해하기

1.1. 실행 계획이란?

실행 계획은 Oracle Database가 특정 SQL 쿼리를 실행하기 위해 선택한 실행 경로를 보여주는 데이터입니다. 이 경로는 데이터베이스가 쿼리를 처리하는 방식(예: 테이블을 어떻게 조회할지, 인덱스를 사용할지 여부 등)을 나타냅니다. 실행 계획을 통해 쿼리 성능 문제의 원인을 파악하고, 최적화할 수 있는 부분을 찾아낼 수 있습니다.

1.2. 실행 계획을 확인하는 방법

실행 계획을 분석하려면, Oracle SQL에서 EXPLAIN PLAN 명령어를 사용합니다. EXPLAIN PLAN은 SQL 쿼리의 실행 계획을 출력하는데, 이를 통해 어떤 테이블을 조회하고 어떤 인덱스를 사용하는지, 그리고 각 단계에서의 비용을 확인할 수 있습니다.

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

이 명령어를 실행하면, 실행 계획이 저장된 PLAN_TABLE이라는 테이블에 결과가 저장됩니다. 이후 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 명령어를 사용하여 실행 계획을 볼 수 있습니다.

 

2. 실행 계획 분석하기

2.1. 실행 계획의 주요 항목

실행 계획을 분석할 때 중요한 항목들은 다음과 같습니다:

• ACCESS PATH: 데이터에 접근하는 방식입니다. 예를 들어, Full Table Scan, Index Scan, Range Scan 등이 있습니다.
• COST: 각 단계의 비용을 나타냅니다. COST가 낮을수록 성능이 좋습니다.
• ROWS: 각 단계에서 처리되는 데이터의 수를 나타냅니다. 이 값이 크면, 해당 단계에서 성능 병목이 발생할 수 있습니다.
• FILTER: 데이터를 필터링하는 조건입니다. 효율적인 조건이 필요합니다.

2.2. 실행 계획 예시 분석

예를 들어, 다음과 같은 실행 계획을 살펴보겠습니다:

| Id  | Operation           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|-----|---------------------|----------------|-------|-------|------------|----------|
| 0   | SELECT STATEMENT    |                | 1000  | 4000  | 5 (20)     | 00:00:01 |
| 1   | TABLE ACCESS FULL   | EMPLOYEES      | 1000  | 4000  | 5 (20)     | 00:00:01 |

 

위 실행 계획에서 TABLE ACCESS FULL은 전체 테이블을 스캔하는 방식입니다. 이 방식은 인덱스를 사용하지 않기 때문에 성능이 낮을 수 있습니다. 대신, Index Scan을 사용하면 성능이 개선될 수 있습니다.

 

 

3. 쿼리 성능 개선 방법

3.1. 인덱스 사용 최적화

인덱스를 적절히 활용하는 것은 SQL 튜닝에서 가장 중요한 요소 중 하나입니다. 쿼리에서 자주 사용하는 컬럼에 대해 인덱스를 추가하면, 테이블을 전체적으로 스캔하는 것보다 더 빠르게 데이터를 검색할 수 있습니다. 예를 들어, 위의 예시에서는 department_id 컬럼에 인덱스를 추가하면 Index Range Scan을 사용하게 되어 성능이 향상됩니다.

CREATE INDEX idx_dept_id ON employees(department_id);

인덱스를 잘 활용하면 쿼리 성능을 크게 향상시킬 수 있습니다. 하지만, 너무 많은 인덱스를 추가하면 인덱스 유지 비용이 증가하므로, 인덱스를 선택적으로 사용해야 합니다.

3.2. 쿼리 리팩토링

SQL 쿼리가 비효율적인 방식으로 작성되어 있다면, 쿼리 자체를 리팩토링하는 것이 필요합니다. 예를 들어, JOIN을 사용하는 대신 서브쿼리를 사용하는 경우 성능이 떨어질 수 있습니다. 이런 경우, JOIN 방식으로 변경하거나, 불필요한 서브쿼리를 제거하는 것이 좋습니다.

-- 비효율적인 서브쿼리 예시
SELECT employee_id, (SELECT department_name FROM departments WHERE department_id = employees.department_id)
FROM employees;

-- JOIN을 사용한 최적화된 예시
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

이처럼 쿼리의 구조를 개선하여 불필요한 연산을 줄이면 성능이 크게 개선됩니다.

3.3. 파티셔닝(Partitioning) 사용

테이블이 매우 커질 경우, 파티셔닝 기법을 사용하여 데이터를 분할하면 쿼리 성능을 향상시킬 수 있습니다. 파티셔닝은 데이터를 여러 개의 작은 테이블처럼 나누어 저장하는 방식으로, 특정 범위의 데이터를 조회할 때 성능이 향상됩니다.

CREATE TABLE sales (
  sale_id INT,
  sale_date DATE,
  amount DECIMAL
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
  PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

위와 같이 파티셔닝을 사용하면, 특정 기간에 대한 데이터를 조회할 때 Full Table Scan 대신, 파티셔닝된 테이블에서 필요한 파티션만 조회하게 되어 성능이 향상됩니다.

3.4. 통계 정보 갱신

Oracle은 통계 정보를 기반으로 최적화된 실행 계획을 생성합니다. 만약 통계 정보가 오래되었거나 부정확하면, 비효율적인 실행 계획을 생성할 수 있습니다. DBMS_STATS 패키지를 사용하여 최신 통계 정보를 갱신하는 것이 중요합니다.

EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME');

정기적으로 통계 정보를 갱신하면, 쿼리 성능이 개선될 수 있습니다.

 

4. 쿼리 성능 모니터링과 조정

4.1. AWR 보고서 활용

Oracle에서는 AWR(Automatic Workload Repository) 보고서를 활용하여 데이터베이스 성능을 모니터링할 수 있습니다. AWR 보고서를 통해 쿼리 실행 시간을 포함한 여러 성능 지표를 확인하고, 병목 현상이 발생하는 부분을 식별할 수 있습니다. 이를 바탕으로 성능 튜닝을 진행하면 보다 효과적으로 쿼리 성능을 개선할 수 있습니다.

SELECT * FROM DBA_HIST_SQLSTAT WHERE SQL_ID = 'your_sql_id';

4.2. SQL Trace와 TKPROF 사용

SQL Trace를 활성화하고, TKPROF 도구를 사용하여 실행 계획을 분석하는 방법도 매우 유용합니다. TKPROF는 SQL 실행에 대한 상세한 정보를 제공하며, 성능 최적화에 유용한 데이터를 얻을 수 있습니다.

ALTER SESSION SET SQL_TRACE = TRUE;
-- SQL 쿼리 실행 후
TKPROF tracefile_name outputfile_name

 

5. 결론

Oracle SQL 튜닝에서 가장 중요한 것은 실행 계획 분석입니다. 쿼리의 성능을 개선하려면, 실행 계획을 철저히 분석하고, 인덱스 최적화, 쿼리 리팩토링, 파티셔닝 등의 방법을 통해 성능을 개선할 수 있습니다. 또한, 통계 정보 갱신과 AWR 보고서 등의 도구를 활용하여 지속적으로 성능을 모니터링하고 최적화하는 것이 중요합니다. 이러한 방법들을 통해 Oracle SQL의 성능을 극대화할 수 있습니다.