SQL 최적화의 기본 원리 - 제 1절 옵티마이저와 실행계획

1.옵티마이저

옵티마이저(Optimizer)

사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할 수행. 이러한 최적의 실행 방법을 실행계획(Execution Plan)이라 함 

 

- 최적의 실행 방법 결정 : 어떤 방법으로 처리하는 것이 최소 일량으로 동일한 일을 처리할 수 있을지 결정하는 것

 

옵티마이저가 최적의 실행 방법을 결정하는 방식

• 규칙기반 옵티마이저(RBO, Rule Based Optimizer) 
• 비용기반 옵티마이저(CBO, Cost Based Optimizer)

 가. 규칙기반 옵티마이저(RBO, rule based Optimizer)****정의암기!!

규칙기반 옵티마이저는 규칙(우선 순위)을 가지고 실행계획을 생성, 실행계획을 생성하는 규칙을 이해하면 누구나 실행계획을 비교적 쉽게 예측 가능

• 인덱스를 이용한 액세스 방식이 전체 테이블 액세스 방식보다 우선 순위가 높음
• 이용 가능한 인덱스가 존재한다면 전체 테이블 액세스 방식보다는 항상 인덱스를 사용하는 실행계획 생성
• 조인 순서를 결정 시 조인 칼럼 인덱스의 존재 유무가 중요한 판단의 기준
• 조인 칼럼에 대한 인덱스가 양쪽에 존재  >>  우선 순위가 높은 테이블이 선행 테이블(Driving Table)
• 한쪽에만 인덱스 존재  >>  인덱스가 없는 테이블이 선행 테이블 (NL Join 사용)
• 모두 인덱스가 존재하지 않을 경우  >>  FROM 절의 뒤에 나열된 테이블이 선행 테이블로 선택 (Sort Merge Join 사용)
• 우선 순위가 동일  :  FROM 절에 나열된 테이블의 역순으로 선행 테이블 선택

규칙기반 옵티마이저의 최적화 과정 예시

* EMP_JOB : JOB        * EMP_SAL : SAL * PK_EMP : EMPNO (UNIQUE) 

 

 

나. 비용기반 옵티마이저(CBO, Cost Based Optimizer)*****정의암기!!!

비용(예상되는 소요시간, 자원 사용량)이 가장 적은 실행계획을 선택하는 방식, 규칙기반 옵티마이저의 단점을 극복하기 위해서 출현

다양한 객체 통계정보와 시스템 통계정보 등 이용

통계정보  없을 경우  >>  정확한 비용 예측이 불가능해 비효율적인 실행계획 생성

(정확한 통계정보 유지하는 것은 비용기반 최적화에 중요한 요소)

그림 Ⅱ-3-3 비용기반 옵티마이저는 질의 변환기, 대안 계획 생성기, 비용 예측기 등의 모듈로 구성

• 질의 변환기   :   사용자가 작성한 SQL문을 처리하기에 보다 용이한 형태로 변환하는 모듈
• 대안 계획 생성기   :   동일한 결과를 생성하는 다양한 대안 계획을 생성하는 모듈

- 대안 계획은 연산의 적용 순서 변경, 연산 방법 변경, 조인 순서 변경 등을 통해 생성

- 대안 계획의 생성이 많아지면 최적화를 수행하는 시간이 그만큼 오래 걸린다. 

 

대부분의 상용 옵티마이저들은 대안 계획의 수를 제약하는 다양한 방법 사용

대안 계획들 중에서 최적의 대안 계획이 포함되지 않을 수도 있다. 

 

* 비용 예측기   :   생성된 대안 계획의 비용을 예측하는 모듈

 

- 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 한다. 

- 정확한 통계정보, 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확 

 

규칙기반 옵티마이저

- 항상 인덱스를 사용할 수 있다면 전체 테이블 스캔 보다는 인덱스를 사용하는 실행계획을 생성한다고 했다. 

 

비용기반 옵티마이저 

- 인덱스를 사용하는 비용이 전체 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있다. 

- 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL문도 서로 다른 실행계획이 생성될 수 있다. 

- 또한 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다.

 

 

2. 실행계획

- SQL에서 요구한 사항을 처리하기 위한 절차와 방법

- 다양한 처리 방법(실행계획)마다 실행 시간(성능)은 서로 다를 수 있다 (옵티마이저는 최적의 실행계획 생성)

 

- 실행계획 구성 요소*** (구성요소 암기!!!)

1) 조인 순서(Join Order)

2) 조인 기법(Join Method)

3) 액세스 기법(Access Method)

4) 최적화 정보(Optimization Information)

* Cost : 상대적인 비용 정보

* Card : Cardinality의 약자. 결과집합의 건수

* Bytes : 결과집합이 차지하는 메모리 양의 바이트

* 비용정보 : 실제 SQL 실행하고 얻은 결과가 아니라 통계 정보 바탕으로 계산한 예상치 

5) 연산(Operation): 여러 조작을 통해 원하는 결과를 얻어내는 일련의 작업

 

 

3. SQL 처리 흐름도***(처리 흐름도에서 무엇을 알수 있는지 알것!!!)

- SQL의 내부적인 처리 절차를 시각적으로 표현한 도표(실행계획을 시각화)

- 조인 순서, 액세스 기법과 조인 기법 등을 표현 가능

l  SELECT ...

    FROM TAB1 A, TAB2 B

    WHERE A.KEY = B.KEY

    AND A.COL1 = :condition1

    AND B.COL2 = :condition2 

    ;

* 액세스 건수 :  TAB1을 액세스한 건수

A.COL1 관련 인덱스가 없어서 전체 테이블 스캔 수행을 의미(액세스 건수 = TAB1테이블 총 건수)

* 조인 시도 건수 : TAB1에서 읽은 건에 대해 A.COL1 = :condition1 조건을 만족한 건수

* 테이블 액세스 건수 : B.KEY 로 구성된 인덱스인 I01_TAB2 에서 즉, 조인 시도한 건들 중 B.KEY = A.KEY 조건을

만족한 건수                                                         

* 성공 건수 : 결과 건수로 TAB2  에서 B.COL2 = :condition2 조건에 만족하는 건수

 

예상문제 

 

다음중 실행계획에 대한 설명으로 가장 부적절한 것은?

1) 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것이다.

2) 실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현된 것이다.

3) 동일 SQL 문에 대해 실행계획이 다르면 실행 결과도 달라질 수 있다. >>오답 (실행계획 즉, 실행방법이 달라진다고 해서 결과가 달라지지는 않는다.)

4) CBO(cost based optimizer)의 실행계획에는 단계별 예상 비용 및 건수 등이 표현된다.

 

SQL 처리 흐름도에 대한 설명으로 가장 절절한 것을 2개 고르시오

위의 SQL 처리 흐름도 그림 참조~

1) 인덱스 스캔, 테이블 전체 스캔 등과 같은 액세스 기법이 표현된다. SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

2) SQL의 내부적인 처리 절차를 시각적으로 표현해준다.

3) SQL 처리 흐름도는 실행 계획을 시각화하여 표현한 것이다 따라서 무관하지 않다.

4) SQL 처리 흐름도만을 보고 실행 시간을 알수없다.

 

다음중 옵티마이저와 실행계획에 대한 설명으로 부적절한 것을 2개 고르시오

1) SQL 처리 흐름도는 성능적인 측면의 표현은 고려하지 않는다. >> 성능적인 측면도 표현할 수 있다.

2) 규칙기반 옵티마이저에서 제일 높은 우선 순위는 행에 대한 고유 주소를 사용하는 방법이다.

>>규칙기반 옵티마이저에서 제일 낮은 우선순위는 전체 테이블 스캔이고, 제일 높은 우선순위는 rowid를 활용하여 테이블을 액세스하는 방법이다.

3) SQL 처리 흐름도는 인덱스 스켄 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있다.

>>SQL 처리 흐름도는 처리 절차를 시각적으로 표현한 것으로, 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현할 수 있으며, 성능적인 측면도 표현할 수 있다.

4) 인덱스 범위 스캔은 항상 여러 건의 결과가 반환된다. >>인덱스 범위 스캔은 결과 건수만큼 반환하지만, 결과가 없으면 한 건도 반환하지 않을 수 있다.

 

아래의 옵티마이저와 실행계획에 대한 설명 중에서 옳은 것을 모두 묶은 것은?

ㄱ) oracle의 규칙기반 옵티마이저에서 가장 우선 순위가 높은 규칙은 single row by rowid 액세스 기법이다.

ㄴ) 비용기반 옵티마이저는 테이블, 인덱스, 컬럼 등 객체의 통걔정보를 사용하여 실행계획을 수립하므로 통계정보가 변경되면 sql의 실행 계획이 달라질수 있다.

ㄷ) oracle의 실행계획에 나타나는 기본적인 join 기법으로는 nl join, hash join, sort merge join 등이 있다.

ㄹ) 다양한 join 기법중 nl join은 DW 등에서 데이터를 집계하는 업무에 많이 사용된다. >>nl join은 데이터를 집걔하는 업무보다는 OLTP의 목록 처리 업무에 많이 사용된다. DW 등의 데이터 집계 업무에서 많이 사용되는 join 기법은 hash join 또는 sort merge join 이다.

 

 

출처 : http://bysql.net/