오라클 데이터베이스 성능 튜닝 기초: 쿼리 최적화

목차

1. 쿼리 최적화 개요
2. 인덱스 사용
3. 실행 계획 분석
4. 조인 방식 최적화
5. 서브쿼리와 조인의 비교
6. SQL 힌트 사용
7. 예제

---

1. 쿼리 최적화 개요

데이터베이스 성능 튜닝에서 가장 중요한 부분 중 하나는 쿼리 최적화입니다. 쿼리 최적화는 SQL 문을 보다 효율적으로 작성하여 실행 시간을 줄이고 시스템 자원을 절약하는 것을 목표로 합니다. 이 글에서는 쿼리 최적화를 위한 다양한 기법과 그 활용 방법에 대해 설명합니다.

---

2. 인덱스 사용

인덱스는 데이터베이스 테이블의 검색 성능을 향상시키는 데 중요한 역할을 합니다. 인덱스를 적절히 사용하면 쿼리 성능을 크게 향상시킬 수 있습니다.

2.1. 인덱스 생성

CREATE INDEX idx_column_name ON table_name (column_name);

2.2. 예제

• EMPLOYEES 테이블의 last_name 컬럼에 인덱스 생성:

  CREATE INDEX idx_last_name ON employees (last_name);
  

인덱스를 생성하면 해당 컬럼을 기준으로 검색할 때 성능이 향상됩니다. 하지만 인덱스는 쓰기 작업(INSERT, UPDATE, DELETE) 성능에 영향을 미칠 수 있으므로 필요한 컬럼에만 생성하는 것이 중요합니다.

---

3. 실행 계획 분석

실행 계획(EXPLAIN PLAN)은 SQL 쿼리가 데이터베이스에서 어떻게 실행될지에 대한 정보를 제공합니다. 이를 통해 쿼리 성능을 분석하고 병목 지점을 찾아 최적화할 수 있습니다.

3.1. 실행 계획 보기

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Smith';

3.2. 실행 계획 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

실행 계획을 분석하여 쿼리의 비용(cost), 사용된 인덱스, 접근 방식 등을 확인할 수 있습니다. 이를 통해 쿼리 성능을 저하시키는 부분을 찾고 최적화할 수 있습니다.

---

4. 조인 방식 최적화

조인은 두 개 이상의 테이블을 결합하여 데이터를 조회하는 방법입니다. 조인의 종류와 방식에 따라 성능이 크게 달라질 수 있습니다.

4.1. 중첩 루프 조인(Nested Loop Join)

중첩 루프 조인은 작은 테이블과 큰 테이블을 결합할 때 주로 사용됩니다.

4.2. 해시 조인(Hash Join)

해시 조인은 대규모 데이터 세트 간의 조인에 적합하며, 해시 테이블을 생성하여 조인 성능을 향상시킵니다.

4.3. 병합 조인(Merge Join)

병합 조인은 정렬된 데이터 세트를 조인할 때 효율적입니다.

4.4. 예제

• 두 테이블을 조인하여 데이터를 조회:

  SELECT e.employee_id, e.first_name, d.department_name
  FROM employees e
  JOIN departments d ON e.department_id = d.department_id;
  

조인의 종류에 따라 실행 계획을 분석하여 최적의 방식을 선택합니다.

---

5. 서브쿼리와 조인의 비교

서브쿼리(Subquery)와 조인(Join)은 데이터를 결합하는 두 가지 방법입니다. 상황에 따라 더 나은 성능을 제공하는 방법을 선택해야 합니다.

5.1. 서브쿼리

서브쿼리는 쿼리 내에 포함된 또 다른 쿼리입니다.

SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

5.2. 조인

조인은 두 테이블을 결합하여 데이터를 조회합니다.

SELECT e.employee_id, e.first_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

서브쿼리는 읽기 쉬운 쿼리를 작성할 수 있지만, 조인이 더 나은 성능을 제공할 때가 많습니다. 실행 계획을 통해 두 방법의 성능을 비교하고 적절한 방식을 선택합니다.

---

6. SQL 힌트 사용

SQL 힌트(Hint)는 옵티마이저에게 특정 쿼리 실행 계획을 따르도록 지시하는 방법입니다. 힌트를 적절히 사용하면 쿼리 성능을 향상시킬 수 있습니다.

6.1. 힌트 구문

SELECT /*+ INDEX(e idx_last_name) */ employee_id, first_name
FROM employees e
WHERE last_name = 'Smith';

6.2. 예제

• 특정 인덱스를 사용하도록 힌트 제공:

  SELECT /*+ INDEX(e idx_last_name) */ employee_id, first_name
  FROM employees e
  WHERE last_name = 'Smith';
  

힌트를 사용할 때는 실행 계획을 분석하여 성능이 실제로 향상되는지 확인해야 합니다. 과도한 힌트 사용은 오히려 성능을 저하시킬 수 있습니다.

---

7. 예제

다음은 위에서 설명한 쿼리 최적화 기법을 종합적으로 사용하는 예제입니다.

예제 1: 인덱스 생성 및 사용

CREATE INDEX idx_department_id ON employees (department_id);

SELECT /*+ INDEX(e idx_department_id) */ e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

예제 2: 실행 계획 분석

EXPLAIN PLAN FOR
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());

예제 3: 서브쿼리와 조인 비교

서브쿼리:

SELECT employee_id, first_name
FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Sales');

조인:

SELECT e.employee_id, e.first_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Sales';

실행 계획을 통해 서브쿼리와 조인의 성능을 비교하고 최적의 방식을 선택합니다.

---

위의 예제들을 통해 데이터베이스 쿼리 최적화 기법을 이해할 수 있습니다. 쿼리 최적화는 데이터베이스 성능 향상에 중요한 역할을 하며, 다양한 기법을 활용하여 효율적인 SQL 문을 작성하는 것이 필요합니다. 실행 계획을 분석하고 적절한 인덱스와 조인 방식을 선택하여 최적의 성능을 달성하세요.