실행 계획 보는 법 - SQL 튜닝

이번 글에서는 실행계획(explain plan)을 보는 법을 설명드리려고 합니다. 실행 계획은 한마디로 SQL이 데이터를 어떻게 처리할지 보여줍니다. 데이터베이스 엔진에서 실행할 예상 정보를 제공합니다.

 

Optimizer에 의한 실행계획 수립

우리가 실행 계획을 읽고 해석할 수 있다면 어떻게 될까요?  더 나은 성능을 위해 쿼리 튜닝을 할 수 있습니다. 실행 계획을 rdbms 종류에 상관없이 일반적인 개념은 비슷합니다. 그럼 이번 기회에 확실히 일아두죠. 
 

실행계획 해석

실행계획을 해석하기 위해서는 우선 실행계획의 구조를 알아야 합니다. Database 마다 조금씩 다르기도 하죠. 가장 많이 사용하는 오라클 기준으로 설명합니다.

실행계획을 해석하기 위해 읽는 순서가 중요합니다. 이 두 가지를 알면 실행계획을 간단히 해석할 수 있습니다.

1. 실행계획의 구조

• 각 Line = Operation 단위(실행 작업 단위입니다)
• 들여 쓰기 : Parent/Child 관계
• Parent는 왼쪽 들여 쓰기 된 상위 Line(더 상위 작업이라는 의미입니다)
• Child는 오른쪽 들여쓰기된 하위 Line(Parent의 아래 진행되는 작업이라는 의미입니다)

 

2. 실행계획 읽는 순서

• 제 1원칙: 위에서 아래로 읽는다
• 재 2원칙: 자식이 있다면 먼저 읽어야 한다(이 부분이 중요합니다. 우선 최 하위 Child를 찾아 내려가서 위에서 아래로 읽습니다. Child를 다 읽고나면 상위 Parent로 이동하여 읽습니다)

 

예를 들어, 아래는 부서(dept)와 사원(emp) 테이블일 이용해 해당 부서에 정보를 가져오는 SQL입니다. 실행계획을 보면 위에서 아래로 읽지만 자식이 있으면 맨 아래까지 내려가야 합니다.

그래서 Id=3부터 시작합니다. PK_TB_CUST 인덱스를 이용해 Index Unique Scan을 해서 TB_CUST에 rowid로 액세스 해서 테이블을 읽습니다. 그리고 TB_ORD 테이블은 full scan으로 읽어서 결과가 나온다고 해석할 수 있습니다.   
 

* rowid : 오라클에서 인덱스를 생성하기 위해 내부적으로 가지고 있는 유일한 컬럼 ID
 

참고로 rowid는 테이블의 각 행을 식별하기 위해 고유하게 생성되어야 합니다.  ROWID의 부여 규칙과 특성을 알고 싶다면 링크를 참고하기 바랍니다.  

 

SELECT /*+ USE_NL(E) */ *  
FROM   DEPT D
     , EMP E
WHERE E.DEPTNO = B.DEPTNO
;

 

--------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     3 |  1152 |     4   (0)|
|   1 |  NESTED LOOPS                |            |     3 |  1152 |     4   (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| TB_CUST    |     1 |    75 |     1   (0)|
|*  3 |    INDEX UNIQUE SCAN         | PK_TB_CUST |     1 |       |     0   (0)|
|*  4 |   TABLE ACCESS FULL          | TB_ORD     |     3 |   927 |     3   (0)|
--------------------------------------------------------------------------------
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."CUST_NO"='C0000000000001')
   4 - filter("O"."CUST_NO"='C0000000000001')

 

3. Operation

• 실행계획의 각 노드는 데이터베이스 엔진이 수행할 작업을 나타냅니다. 일반적인 작업에는 "테이블 스캔", "인덱스 스캔", "해시 조인", "정렬" 등이 포함됩니다.

 4. .Name

• Operation 하는 오브젝트(테이블, 인덱스 등)의 명칭을 나타냅니다.

5. Rows

• 각 단계에서 처리되는 예상 또는 실제 행(rows) 수를 말합니다. 만약 많은 rows 수를 기록한 지점을 확인하는 데 도움이 됩니다.

6. Bytes

• 각 단계에서 처리되는 rows에 해당되는 블럭의 bytes입니다. 

7. Cost

• 각 작업에는 예상되는 실행 시간이나 리소스 측면에서 계산된 비용입니다. 비용이 낮다는 것은 일반적으로 효율성이 더 높다는 것을 의미합니다.

8. Predicate Information

• 각 단계마다 조건이나 필터가 적용됩니다. 이를 이해하면 쿼리가 필터링되거나 조인되는 방식을 확인하는 데 도움이 됩니다.

OPERATION	OPTIONS	DESC
AGGREGATE	GROUP BY	그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리
AND-EQUAL		인덱스 머지를 이용하는 경우 (10G 이후 사용불가)
CONNECT BY		CONNECT BY를 사용하여 트리 구조로 전개
CONCATENATION		단위 액세스에서 추출한 로우들의 합집합을 생성
COUNTING		테이블의 로우 수를 센다
FILTER		선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업
FIRST ROW		조회 로우 중에 첫번째 로우만 추출한다.
FOR UPDATE		선택된 로우에 LOCK을 지정한다.
INDEX	UINQUE RANGE SCAN RANGE SCAN DESCENDING	UNIQUE인덱스를 사용한다. (단 한개의 로우 추출) NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우) RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다.
INTERSECTION	OUTER	교집합의 로우를 추출한다.
MERGE JOIN		먼저 자신의 조건만으로 액세스한 후 각각을 SORT하여 MERGE해 가는 조인 위와 동일한 방법이지만  outer join을 사용한다.
MINUS		MINUS 함수를 사용한다.
NESTED LOOPS	OUTER	먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인 위와 동일하지만 outer join을 사용한다.
PROJECTION		내부적인 처리의 일종
REMOTE		다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해 DATABASE LINK를 사용하는 경우
SEQUENCE		시퀀스를 액세스 한다.
SORT	UNIQUE GROUP BY JOIN ORDER BY	같은 로우를 제거하기 위한 SORT 액세스 결과를 GROUP BY 하기 위한 SORT MERGE JOIN을 하기 위한 SORT ORDER BY를 위한 SORT
TABLE ACCESS	FULL CLUSTER HASH BY ROWID	전체 테이블을 스캔한다. CLUSTER를 액세스 한다. 키값에 대한 해쉬 알고리즘을 사용(버전 7에서만) ROWID를 이용하여 테이블을 추출한다.
UNION		두 집합의 합집합을 구한다.(중복없음) 항상 전체 범위 처리를 한다.
UNION ALL		두 집합의 합집합을 구한다.(중복가능) UNION과는 다르게 부분범위 처리를 한다.
VIEW		내부적인 처리의 일종
REMOTE		어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과)

 

실행계획 확인 사항

1. 인덱스 사용 확인

• 인덱스가 예상대로 사용되고 있는지 확인 - "인덱스 스캔" 또는 "인덱스 탐색" 작업 등
• 예상한 위치에서 인덱스가 사용되지 않는 경우 인덱스 전략 필요함

 

2. 조인 확인

• 테이블 조인 방식이 맞는지 확인
• 예를 들어, 중첩조인(Nested Loop), 해시조인(Hash Join), 소트머지 조인(Sort Merge Join) 등 성능 특성이 다름

 

3. 필터링 및 정렬

• 필터링 및 정렬이 발생하는 위치를 검토
• 쿼리에 대규모 결과 집합 정렬 또는 필터링이 포함되는 경우 성능에 영향을 미칠 수 있음

 

4. 서브 쿼리 및 중첩 루프

• 성능에 영향을 미칠 수 있는 서브 쿼리 및 중첩 루프 작업 확인
• 작업의 영향을 줄이기 위해 쿼리를 튜닝 고려

 

마무리 

과거에는 실행 계획을 이용하여 SQL을 튜닝하는 것이 DBA와 컨설턴트의 역할이었습니다. 하지만 요즘에는 rdbms의 옵티마이저의 지능이 발전하여 웬만한 SQL에 대해서는 누적된 통계정보를 통해 가장 좋은 실행계획을 만들어 줍니다. 하지만 아직도 5% 정도는 직접 SQL을 튜닝해야 하는 일이 생깁니다. 
 
우리는 SQL 실행 계획을 이해하고 해석함으로써 데이터베이스에서 쿼리가 처리되는 메커니즘을 알 수 있습니다. 이러한 해석을 통해 더 나은 성능을 위해 쿼리를 튜닝을 개별적으로 진행할 수 있습니다. 실행계획 변경을 위한 HINT에 대한 내용은 아래 글을 참고하시기 바랍니다. 
 
 

오라클 힌트(hint) 종류 및 사용법