[Oracle] SQL 구문의 성능을 결정하는 실행 계획

1. 실행 계획 확인 방법

SQL 구문의 지연이 발생했을 때 제일 먼저 실행 계획을 살펴봐야합니다. 다음과 같은 3개의 기본적인 SQL 구문의 실행 계획을 살펴보겠습니다.

 

● 테이블 풀 스캔의 실행 계획

 

● 인덱스 스캔의 실행 계획

 

● 간단한 테이블 결합의 실행 계획

 

오라클의 경우 실행 계획을 확인하는 명령어는 다음과 같습니다.

SET AUTOTRACE TRACEONLY

실행 계획에 공통적으로 나타나는 부분은 조작 대상 객체, 객체에 대한 조작의 종류, 조작 대상이 되는 레코드 수 등이 나타납니다. 그만큼 중요한 부분이라는 것입니다. 조작 대상 객체를 살펴보면, 오라클은 Name 필드에 테이블 이름이 출력됩니다. 어떤 객체를 조작하는지 혼동하지 않게 주의가 필요합니다. 또한 이 부분은 테이블 이외에도 인덱스, 파티션, 시퀀스처럼 SQL 구문으로 조작할 수 있는 객체라면 무엇이라도 올 수 있습니다. 

 

2. 테이블 풀 스캔 실행 계획

객체에 대한 조작의 종류는 실행 계획에서 가장 중요한 부분입니다. Oracle의 경우 Operation 필드에 나타납니다. 오라'TABLE ACCESS FULL'은 테이블의 데이터를 전부 읽어들인다는 뜻입니다. 

 

객체 이름 또는 레코드 수라는 지표에 비해서, Oracle의 출력에 포함되어 있는 실행 비용이라는 지표는 평가하기 조금 어려운 항목입니다. 이름만 보면 작을수록 좋다고 생각할 수 있지만, 이를 절대 평가의 지표로 쓰는 것은 안됩니다. 또한 Oracle이 출력하는 TIME 필드도 어디까지나 추정되는 실행 시간이므로, 절대 지표로 사용할 수 없습니다. 이렇게 실행 계획에 출력되는 비용 또는 실행 시간, 처리 레코드 수는 추정값이므로 절대 지표로는 사용하면 안됩니다. 

 

조작 대상이 되는 레코드 수도 중요한 항목입니다. Row라는 항목에 출력이 되며 결합 또는 집약이 포함되면 1개의 SQL 구문을 실행해도 여러 개의 조작이 수행됩니다. 각 조작에서 얼마만큼의 레코드가 처리되는지가 SQL 구문 전체의 실행 비용을 파악하는 데 중요한 지표가 됩니다. 이 숫자는 카탈로그 매니저로부터 얻은 값입니다. 따라서 통계 정보에서 파악한 숫자이므로, 실제 SQL 구문을 실행한 시점의 테이블 레코드 수와 차이가 있을 수 있습니다. 

 

3. 인덱스 스캔 실행 계획

SQL 구문에 WHERE 조건을 추가하고 실행계획을 다시 살펴보겠습니다. PK를 이용해서 조회를 하는 경우, 조작 대상이 되는 레코드 수가 1개가 되고, 'TABLE ACCESS FULL'이 'TABLE ACCESS BY INDEX ROWID'로 바뀌었습니다. Operation에는 INDEX UNIQUE SCAN이라는 조작어가 나타납니다. 이는 인덱스를 사용해 스캔을 수행한다는 것입니다.  일반적으로 스캔하는 모집합 레코드 수에서 선택되는 레코드 수가 적다면 테이블 풀 스캔보다 빠르게 접근을 수행합니다. 인덱스를 사용할 때 활용되는 B-tree가 모집합의 데이터양에 따라 대수 함수적으로 처리 비용이 늘어나기 때문입니다. 특정 분기점을 기준으로 인덱스 스캔이 풀 스캔보다 효율적인 접근을 하게 된다는 것입니다. 시퀀셜 스캔의 경우 O(n), 인덱스 스캔의 경우 O(log n)입니다. 

 

4. 간단한 테이블 결합 실행 계획

SQL에서 지연이 일어나는 경우는 대부분 결합과 관련된 것입니다. 결합을 사용하면 실행 계획이 상당히 복잡해지므로, 옵티마이저도 최적의 실행 계획을 세우기 어렵습니다. 일반적으로 DBMS는 결합을 할 때 세 가지 종류의 알고리즘을 사용합니다. 

 

가장 간단한 결합 알고리즘은 Nested Loops입니다. 한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식입니다. 절차 지향형 언어로 구현하면 이중 반복으로 구현되므로 중첩 반복이라는 이름이 붙은 것입니다.

 

두 번째는 Sort Merge입니다. 결합 키로 레코드를 정렬하고, 순차적으로 두 개의 테이블을 결합하는 방법입니다. 결합 전에 전처리로 정렬을 수행해야 하는데요, 이때 작업용 메모리로 워킹 메모리를 사용합니다. 

 

세 번째는 Hash입니다. 이름 그대로 결합 키값을 해시값으로 맵핑하는 방법입니다. 해시 테이블을 만들어야 하므로, 마찬가지로 작업용 메모리 영역을 필요로 합니다. 실행 계획은 일반적으로 트리 구조입니다. 이때 중첩 단계가 깊을수록 먼저 실행됩니다. 이때 결합의 경우 어떤 테이블에 먼저 접근하는지가 굉장히 중요한 의미를 갖습니다. 같은 중첩 단계에서는 위에서 아래로 실행한다는 뜻입니다.