Database

옵티마이저와 실행계획

Jinn 2024. 9. 24. 08:49
목차

1. 옵티마이저(Optimizer)
    1.1 옵티마이저
    1.2 규칙기반 옵티마이저(RBO, Rule Based Optimizer)
    1.3 비용기반 옵티마이저(CBO, Cost Based Optimizer)
2. 실행계획(Execution Plan)
    2.1 실행계획
    2.2 SQL 처리 흐름도(Access Flow Diagram)

 

1. 옵티마이저

1.1 옵티마이저

옵티마이저는 사용자가 질의한 SQL문에 대한 최적의 실행 방법을 결정한다. 이때 최적의 실행 방법을 실행계획이라고 한다. 즉 옵티마이저는 SQL문에 대한 실행계획을 결정한다.

 

데이터베이스의 동작 과정을 간단히 표현하면 다음과 같다.

사용자가 질의한 SQL문의 결과를 추출할 수 있는 실행 방법은 다양할 수 있다. 그중 최적의 방법을 결정하는 것이 옵티마이저의 역할이고, 옵티마이저가 결정한 실행 계획에 따라 SQL문의 수행 속도에 큰 차이가 있다.

 

최적의 실행 방법을 결정하는 것은 어떻게 처리해야 최소 일량으로 일을 처리할 수 있을지 결정하는 것이다.

옵티마이저는 최적의 실행 방법을 결정하는 방식에 따라 규칙기반 옵티마이저(RBO, Rule Based Optimizer)와 비용기반 옵티마이저(CBO, Cost Based Optimizer)로 구분할 수 있다.

 

1.2 규칙기반 옵티마이저

규칙기반 옵티마이저는 규칙을 가지고 실행계획을 생성한다. 여기서 규칙이란 우선 순위를 의미한다. 규칙기반 옵티마이저는 실행계획을 생성할 때 이용 가능한 인덱스 유무와 종류, SQL에 사용된 연산자의 종류, SQL에서 참조하는 객체의 종류 등을 참조한다. 이러한 정보에 따라 우선 순위가 정해져 있고, 이 우선 순위가 높은 규칙이 최적의 실행 방법이라고 생각하는 것이다.

 

아래 표는 오라클 규칙기반 옵티마이저의 규칙이다. 순위 숫자가 낮을수록 높은 우선 순위이다.

순위(규칙) 액세스 기법
1 Single row by rowid
2 Single row by cluster join
3 Single row by hash cluster key with unique or primary key
4 Single row by unique or primary key
5 Cluster join
6 Hash cluster key
7 indexed cluster key
8 Composite index
9 Single column index
10 Bounded range search on indexed columns
11 Unbounded range search on indexed columns
12 Sort merge join
13 MAX or MIN of indexed column
14 ORDER BY on indexed column
15 Full table scan

 

인덱스 없이 where절을 사용하는 경우는 전체 테이블을 접근하면서 주어진 조건을 만족하는 행을 결과로 추출하는 Full table scan으로 우선 순위가 가장 낮다.

 

우선 순위가 가장 높은 Single row by rowid는 rowid를 통해 테이블에서 하나의 행에 접근하는 방식이다. 행의 id로 접근하는 방법은 단일 행에 접근하는 가장 빠른 방법이다.

 

단일 컬럼 인덱스에 = 연산자를 사용하여 검색하는 경우는 우선 순위 9인 Single column index이다.

between이나 like 등을 사용하여 인덱스 컬럼의 양쪽에 범위를 한정하여 검색하는 방식은 우선 순위 10에 해당한다.

<, > 등을 사용하여 인덱스 컬럼 한쪽에만 범위를 한정하는 경우는 우선 순위 11에 해당한다.

 

규칙기반 옵티마이저는 인덱스를 활용한 접근 방식이 우선 순위가 높다. 따라서 이용 가능한 인덱스가 존재한다면 인덱스를 사용하는 방향으로 실행계획을 생성한다.

 

아래 SQL문에서 규칙기반 옵티마이저가 실행계획을 어떻게 생성할지 생각해보자.

SELECT name
FROM emp
WHERE job = 'SALESMAN'
AND sal BETWEEN 3000 AND 6000;

INDEX
--------------------------------
EMP_JOB : JOB
EMP_SAL : SAL
PK_EMP  : EMPNO (UNIQUE)

job 컬럼은 = 연산자를 사용하며 EMP_JOB이라는 단일 컬럼 인덱스도 존재한다. 따라서 규칙 9를 만족한다.

sal 컬럼은 between을 사용하여 양쪽 범위를 한정하며 역시 단일 컬럼 인덱스가 존재한다. 따라서 규칙 10을 만족한다.

규칙기반 옵티마이저는 규칙 9와 10중 9의 우선 순위가 더 높으므로 EMP_JOB 인덱스를 사용하여 조건을 만족하는 행에 접근할 것이다.

 

 

조인 순서를 결정할 때는 조인 컬럼의 인덱스 유무가 판단의 기준이 된다.

조인 컬럼의 인덱스가 양쪽 테이블에 모두 있다면 위 규칙에 따라 우선 순위가 높은 테이블이 선행 테이블이 된다.

한쪽 조인 컬럼에만 인덱스가 있으면 인덱스가 없는 테이블이 선행 테이블이 된다.

양쪽 테이블 모두 조인 컬럼에 인덱스가 없으면 from절 뒤에 나열된 테이블이 선행 테이블이 된다. 만약 조인 테이블의 우선 수위가 같다면 from절에 나열된 역순으로 선행 테이블을 선택한다.

 

조인 기법은 양쪽 조인 컬럼에 모두 인덱스가 없으면 Sort Merge Join, 한쪽이라도 조인 컬럼에 인덱스가 있으면 Nested Loop Join을 사용한다.

 

현재는 대부분 비용기반 옵티마이저를 사용하며 하위 버전 호환성을 위해서만 규칙기반 옵티마이저를 남겨놓았다.

 

1.3 비용기반 옵티마이저

때로는 = 연산 보다 between 연산의 일량이 더 적을 수 있다. 또한 인덱스를 사용하는 비용이 Full table scan 보다 비용이 클수도 있다. 이처럼 모든 SQL문을 미리 정해진 몇가지 규칙만으로 정확히 예측할 수 없다. 비용기반 옵티마이저는 규칙기반 옵티마이저의 이러한 단점을 극복하기 위해 등장했다.

 

비용기반 옵티마이저는 SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택하는 방식이다. 여기서 비용이란 SQL문을 처리하기 위해 예상되는 소요시간 또는 자원 사용량을 의미한다. 정확한 비용 예측을 위해 비용기반 옵티마이저는 통계정보를 이용한다. 따라서 정확한 통계정보를 유지하는 것은 비용기반 최적화에서 중요한 요소이다.

 

비용기반 옵티마이저의 구성 요소는 다음과 같다.

질의 변환기는 사용자가 질의한 SQL문을 처리하기 용이한 형태로 변환하는 모듈이다.

 

대안 계획 생성기는 동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈이다. 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성된다. 동일한 결과를 생성하는 모든 대안 계획을 생성해야 최적의 대안을 선택할 수 있다. 그러나 모든 대안 계획을 생성하는 것은 그 자체로 시간이 오래 걸릴 수 있다. 그래서 대부분의 상용 옵티마이저는 대안 계획의 수를 제한하는데, 이렇게 생성된 대안 계획에는 최적의 대안 계획이 없을 수도 있다.

 

비용 예측기는 대안 계획의 비용을 예측하는 모듈이다. 보다 정확한 예측을 위해 옵티마이저는 통계정보를 이용한다.

 

비용기반 옵티마이저는 통계정보, DBMS의 버전과 설정 차이 등으로 동일한 SQL문도 다른 실행계획을 생성할 수 있다. 이처럼 비용기반 옵티마이저는 실행계획의 예측이 어렵다는 단점이 있다.

2. 실행계획

2.1 실행계획

실행계획이란 SQL을 처리하기 위한 철차와 방법을 의미한다. (어떤 순서로 어떻게 실행할지)

동일한 SQL도 다양한 실행계획이 존재하고, 각 실행계획마다 실행 시간(성능)은 서로 다르다. 여러 실행계획에서 가장 효율적인 방법을 옵티마이저가 생성한다.

 

다음은 오라클의 실행계획 예시이다.

Execution Plan
--------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=114)
  NESTED LOOPS (Cost=3 Card=2 Bytes=114)
    TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=74)
      INDEX (RANGE SCAN) OF 'EMP' (INDEX) (Cost=1 Card=2)
    TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=20)
      INDEX (UNIQUE SCAN) OF 'PK_DEPT' (INDEX (UNIQUE)) (Cost=0 Card=1)

조인 순서는 조인을 수행할 때 참조하는 테이블의 순서이다. 위 예시에서 조인 순서는 EMP → DEPT이다.

 

조인 기법은 테이블을 조인할 때 사용할 수 있는 방법이다. NL Join, Hash Join, Sort Merge Join 등이 있으며 위 예시에서는 NL Join을 사용했다.

 

액세스 기법은 하나의 테이블에 접근하는 방법이다. 인덱스를 활용해 접근하는 Index Scan과 테이블 전체를 읽는 Full Table Scan 등이 있으며 위 예시에서는 Index Scan을 사용한다.

 

최적화 정보는 실행계획의 각 단계마다 예상 비용을 표시한 것이다. 최적화 정보에는 Cost, Card, Bytes가 있다.

각 요소는 다음을 의미한다.

  • Cost: 상대적인 비용 정보
  • Card: Cardinality의 약자로, 주어진 조건 또는 조인 조건을 만족한 결과 집합의 건수
  • Bytes: 결과 집합이 차지하는 양

이러한 정보는 SQL을 실제로 실행하여 얻은 결과가 아니라 통계 정보를 바탕으로 옵티마이저가 계산한 예상치이다.

 

2.2 SQL 처리 흐름도

SQL 처리 흐름도란 SQL의 내부적 처리 절차를 표현한 도표로, 실행계획을 시각화한 것이기도 하다.

다음은 SQL 처리 흐름도의 예시이다.

SQL 처리 흐름도에는 조인 순서, 액세스 기법, 조인 기법 등을 표현할 수 있다.

 

위 예시에서 조인 순서는 TAB1 → TAB2이다. 먼저 참조되는 테이블을 Outer Table 또는 Driving Table이라고 하고, 이후에 참조되는 테이블을 Inner Table 또는 Lookup Table이라고 한다.

테이블 액세스는 TAB1은 Full Table Scan을 의미하고 TAB2는 I01_TAB2라는 인덱스를 이용한 Index Scan을 했다.

조인 방법은 NL Join을 수행했다.