[oracle] EXPLAIN PLAN을 활용한 쿼리 성능 최적화 기법

1. EXPLAIN PLAN 명령이란

---

EXPLAIN PLAN 명령은 데이터베이스에서 특정 쿼리의 실행 계획을 보여줍니다. 실행 계획은 쿼리가 실행될 때 데이터베이스가 수행하는 단계별 접근 방식을 설명하며, 이를 통해 쿼리 성능을 최적화할 수 있는 인사이트를 얻을 수 있습니다. 여기서는 Oracle 데이터베이스를 예로 들어 설명하겠습니다.

---

2. Oracle에서 EXPLAIN PLAN 사용

---

실행 계획 생성: EXPLAIN PLAN FOR 명령을 사용하여 실행 계획을 생성합니다.

EXPLAIN PLAN FOR
SELECT
    DECODE(dv, '1차', '1', '2차', '2', '3차', '3', '4차', '4') AS ORDER_NO,
    name,
    grade
FROM tbl_board
WHERE status = 'active'
ORDER BY ORDER_NO, name;

실행 계획 보기: DBMS_XPLAN.DISPLAY 함수를 사용하여 실행 계획을 확인합니다.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

이 두 단계를 통해 해당 쿼리의 실행 계획을 확인할 수 있습니다. 실행 계획에는 각 단계의 유형, 비용, 예상 행 수, 액세스 경로 등이 포함됩니다.

---

3. 실행 계획 분석

---

실행 계획을 분석할 때 주의해야 할 주요 요소는 다음과 같습니다:

1. Operation: 쿼리의 각 단계에서 수행되는 작업의 유형 (예: TABLE ACCESS, INDEX SCAN 등).
2. Options: 각 작업의 옵션 (예: FULL, RANGE SCAN 등).
3. Object Name: 해당 작업에서 액세스하는 객체 (예: 테이블 이름 또는 인덱스 이름).
4. Cost: 이 단계의 상대적 비용. 일반적으로 낮을수록 좋습니다.
5. Cardinality: 이 단계에서 예상되는 결과 행 수.
6. Bytes: 이 단계에서 예상되는 데이터 크기.

* Operation

Operation 항목은 SQL 쿼리가 실행되는 방법을 상세히 설명합니다. 각 Operation은 데이터베이스 엔진이 데이터를 어떻게 액세스하고 처리하는지를 나타내며, 이러한 항목은 실행 계획을 이해하고 성능을 최적화하는 데 중요한 정보를 제공합니다. 주요 Operation 항목에 대한 요약은 다음과 같습니다:

 

• SELECT STATEMENT: 최상위 노드로, SQL 문 전체를 나타냅니다.
• TABLE ACCESS:
  • FULL: 테이블의 모든 행을 스캔합니다.
  • BY ROWID: 특정 행을 ROWID를 사용하여 액세스합니다.
  • BY INDEX ROWID: 인덱스를 통해 얻은 ROWID를 사용하여 행을 액세스합니다.
  • SAMPLE: 테이블의 일부 샘플 데이터를 액세스합니다.
• INDEX:
  • UNIQUE SCAN: 인덱스를 사용하여 고유한 값을 찾습니다.
  • RANGE SCAN: 인덱스 범위를 스캔합니다.
  • FULL SCAN: 인덱스의 모든 값을 스캔합니다.
  • SKIP SCAN: 인덱스를 일부 스캔합니다.
  • FAST FULL SCAN: 인덱스의 모든 블록을 빠르게 스캔합니다.
• JOIN:
  • NESTED LOOPS: 외부 집합의 각 행에 대해 내부 집합을 반복합니다.
  • HASH JOIN: 해시 테이블을 사용하여 두 집합을 조인합니다.
  • MERGE JOIN: 두 정렬된 집합을 병합하여 조인합니다.
• SORT:
  • ORDER BY: 데이터를 정렬합니다.
  • AGGREGATE: 집계 함수를 적용하여 데이터를 정렬합니다.
  • GROUP BY: 그룹화를 위해 데이터를 정렬합니다.
• VIEW: 뷰를 사용하여 데이터를 액세스합니다.
• HASH: 해시 테이블을 생성합니다.
• BITMAP:
  • CONVERSION FROM ROWIDS: ROWID에서 비트맵으로 변환합니다.
  • CONVERSION TO ROWIDS: 비트맵에서 ROWID로 변환합니다.
  • INDEX SINGLE VALUE: 단일 값에 대한 비트맵 인덱스를 사용합니다.
  • INDEX RANGE SCAN: 비트맵 인덱스의 범위를 스캔합니다.
• REMOTE: 원격 데이터베이스에서 데이터를 가져옵니다.
• SEQUENCE: 시퀀스를 사용하여 값을 생성합니다.
• FILTER: 조건을 만족하는 행을 필터링합니다.
• UNION, INTERSECTION, MINUS: 집합 연산을 수행합니다.
• TEMP TABLE TRANSFORMATION: 임시 테이블을 생성하고 액세스합니다.

 

---

4. 예제 실행 계획

---

실행 계획을 보면 다음과 같은 내용을 포함할 수 있습니다:

-------------------------------------------------------------------------------------------
| Id  | Operation           | Name                    | Rows  | Bytes | Cost (%CPU)| Time|
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                         |  1000 | 33000 |    10   (0)| 00:00:01 |
|   1 |  SORT ORDER BY      |                         |  1000 | 33000 |    10   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | TBL_BOARD               |  1000 | 33000 |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

이 예제에서:

• TABLE ACCESS FULL은 테이블 전체를 스캔하고 있음을 나타냅니다. 이는 인덱스가 사용되지 않았다는 것을 의미합니다.
• SORT ORDER BY는 정렬 작업을 수행합니다.

---

5. 인덱스 사용 유도

---

인덱스를 사용하도록 쿼리를 최적화하기 위해 힌트를 사용할 수 있습니다. 예를 들어, status와 name 컬럼에 대한 인덱스를 생성하고 힌트를 추가합니다:

CREATE INDEX idx_tbl_board_status ON tbl_board(status);
CREATE INDEX idx_tbl_board_name ON tbl_board(name);

-- 힌트 추가
EXPLAIN PLAN FOR
SELECT /*+ INDEX(tbl_board idx_tbl_board_status) INDEX(tbl_board idx_tbl_board_name) */
    DECODE(dv, '1차', '1', '2차', '2', '3차', '3', '4차', '4') AS ORDER_NO,
    name,
    grade
FROM tbl_board
WHERE status = 'active'
ORDER BY ORDER_NO, name;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

이렇게 하면 데이터베이스가 인덱스를 사용하도록 강제할 수 있습니다.

---

6. 결론

---

EXPLAIN PLAN을 사용하여 실행 계획을 확인하면 쿼리의 성능 병목 현상을 파악하고, 인덱스 사용 여부, 쿼리 구조 최적화 등을 통해 성능을 개선할 수 있습니다. 인덱스를 생성한 후에도 실행 계획을 통해 인덱스가 실제로 사용되고 있는지 확인하고, 필요시 인덱스 힌트를 사용하여 데이터베이스가 인덱스를 사용하도록 유도할 수 있습니다.

---

[번외] 인덱스 스킵 스캔(Skip Scan)이 발생하는 경우

---

서브쿼리를 포함한 예제 쿼리

CREATE INDEX idx_composite ON test_table (a, b, c); // 인덱스 생성

EXPLAIN PLAN FOR
SELECT * 
FROM test_table 
WHERE 1=1 
AND b = (SELECT year FROM b_table) 
AND c = 10 
ORDER BY b DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---

실행 계획 예상

복합 인덱스 (a, b, c)를 가지고 있는 상황에서 위 쿼리를 실행하면, 오라클은 서브쿼리 결과를 먼저 평가한 다음, 
이 값을 이용하여 test_table에서 조건을 만족하는 행을 찾습니다. 
인덱스의 리딩 컬럼인 a가 조건에 포함되지 않았기 때문에 인덱스 스킵 스캔(Skip Scan)이 발생할 수 있습니다.

---

최적화 방안

복합 인덱스를 활용하여 성능을 최적화할 수 있지만, a 컬럼이 조건에 포함되지 않은 경우 인덱스 스킵 스캔이 발생할 수 있습니다. 이를 해결하기 위해:

1. 인덱스 추가: b와 c 컬럼에 대해서만 별도의 인덱스를 추가하여 성능을 최적화할 수 있습니다. 하지만 이는 인덱스의 추가적인 저장 공간과 유지 보수 비용이 발생할 수 있습니다.
2. 실행 계획 확인 및 조정: 실행 계획을 주기적으로 확인하고, 필요에 따라 인덱스를 조정하거나 힌트(HINT)를 사용하여 옵티마이저가 적절한 인덱스를 선택하도록 유도할 수 있습니다.

최종적으로, 복합 인덱스를 적절히 사용하면서 쿼리의 성능을 최적화하기 위해 실행 계획을 잘 분석하고 필요한 조치를 취하는 것이 중요합니다.

---

[번외] 서브쿼리 활용으로 인한 TABLE ACCESS FULL 발생

---

Oracle의 DBMS_XPLAN.DISPLAY를 사용하여 실행 계획을 확인할 때 TABLE ACCESS FULL 항목이 나타나는 것은 데이터베이스가 테이블 전체를 스캔하고 있다는 것을 의미합니다. 이 경우, 인덱스를 사용하지 않고 전체 테이블을 스캔하는 것은 일반적으로 성능이 좋지 않으며, 쿼리 최적화를 위한 조치가 필요할 수 있습니다.

주어진 쿼리에서 sd_table이 TABLE ACCESS FULL 항목에 나타나는 상황을 살펴보겠습니다:

SELECT name, 
       age, 
       (SELECT sd_cd FROM sd_table) AS sd_cd 
FROM tbl_board 
WHERE 1=1 
ORDER BY ORDER_NO, name;

 

이 쿼리에서 sd_table의 TABLE ACCESS FULL이 발생하는 이유는 서브쿼리의 형태와 sd_table에 대한 접근 방식 때문일 수 있습니다.

---

문제 분석 및 해결 방법

1) 서브쿼리의 사용:

• 서브쿼리 (SELECT sd_cd FROM sd_table)가 tbl_board의 각 행에 대해 실행되기 때문에, sd_table의 전체 스캔이 필요할 수 있습니다.
• 이 서브쿼리가 단일 값만 반환한다고 가정해도, Oracle은 서브쿼리를 실행하기 위해 sd_table을 전체적으로 스캔할 수 있습니다.

2)인덱스 사용:

• sd_table의 검색 조건이 서브쿼리에서 sd_cd를 기준으로 한다면, 이 컬럼에 인덱스를 생성하는 것이 도움이 될 수 있습니다.

CREATE INDEX idx_sd_table_sd_cd ON sd_table(sd_cd);

 

3) 서브쿼리 최적화:

• 서브쿼리가 단일 값을 반환한다면, 이 서브쿼리를 메인 쿼리의 SELECT 절이 아닌 FROM 절에서 조인하는 방식으로 변경할 수 있습니다. 예를 들어, sd_table이 상수 값이 아니라면 조인 조건을 추가하는 방법입니다.

SELECT b.name, 
       b.age,
       s.sd_cd
FROM tbl_board b 
LEFT JOIN sd_table s 
ON s.some_column = b.some_column -- 실제 조인 조건으로 변경 
WHERE 1=1 
ORDER BY b.ORDER_NO, b.name;

 

4) 쿼리의 실행 계획 다시 확인:

• 인덱스 생성 후 쿼리의 실행 계획을 다시 확인하여 TABLE ACCESS FULL이 INDEX 접근으로 변경되었는지 확인합니다.

EXPLAIN PLAN FOR 
SELECT name, 
       age, 
       (SELECT sd_cd FROM sd_table) AS sd_cd 
FROM tbl_board 
WHERE 1=1 
ORDER BY ORDER_NO, name; 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---

추가 조치

1) 쿼리 리팩토링: 서브쿼리가 반환하는 데이터의 양이 많거나 조인 조건이 복잡한 경우, 쿼리를 리팩토링하여 성능을 개선할 수 있습니다.

 

2) 인덱스 힌트: 필요에 따라 인덱스 힌트를 사용하여 쿼리 옵티마이저가 인덱스를 강제로 사용할 수 있도록 할 수 있습니다.

SELECT /*+ INDEX(sd_table idx_sd_table_sd_cd) */ 
        name, 
        age,
        (SELECT sd_cd FROM sd_table) AS sd_cd 
FROM tbl_board 
WHERE 1=1 
ORDER BY ORDER_NO, name;

 

3) 통계 정보 갱신: 인덱스와 테이블의 최신 통계 정보를 유지하여 옵티마이저가 최적의 실행 계획을 선택할 수 있도록 합니다.

BEGIN 
	DBMS_STATS.GATHER_TABLE_STATS('schema_name','sd_table');
	DBMS_STATS.GATHER_TABLE_STATS('schema_name','tbl_board'); 
END;

---

결론

TABLE ACCESS FULL이 나타나는 이유는 주로 서브쿼리의 형태나 인덱스의 부재 때문일 수 있습니다. 서브쿼리를 조인 방식으로 변경하거나 인덱스를 생성하고, 실행 계획을 다시 확인하여 성능을 최적화할 수 있습니다.

---