옵티마이저란??

옵티마이저란?

- 가장 빠르고 효율적인 SQL 실행 계획을 수립하고 SQL을 실행하는 DBMS의 핵심적인 소프트웨어다..

- 실행 계획은 SQL 성능에 핵심적인 역할을 하며 동일한 결과라도 계획에 따라 성능이 다르다.

 

옵티마이저 종류

1. 규칙 기반 옵티마이저

 미리 정해 놓은 규칙(액세스 경로별 우선순위)에 따라 엑세스 경로를 평가하고 실행계획을 선택한다. 

 

2. 비용 기반 옵티마이저

 예상되는 비용(쿼리 수행에 필요한 시간)을 기반으로 최적화를 수행한다. 미리 구한 테이블과 인덱스에 대한 통계정보를 기초로 각 오퍼레이션 단계별 예상 비용을 산정하고, 총비용이 가장 낮은 계획을 선택한다.(부적절한 통계정보의 경우 성능 정하)

 

옵티마이저 특징

- Data Dictionary에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용을 산정하고 최저비용 가지고 있는 계획을 선택해서 SQL을 실행한다.

- 옵티마이저는 자동으로 하다보면 비효율적으로 실행 계획을 구성할 수가 있는데 개발자는 힌트(HINT)를 줌으로써 실행 계획을 변경할 수 있다.

 

실행계획 확인 하는 방법

- 옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장하는데 개발자를 이를 조회 해서 확인할 수 있다.

- intellij의 datagrip을 활용해서 실행계획을 보자면 아래와 같다.

위 실행결과를 보면 풀스캔을 한 것을 볼 수 있다.

 

개발자가 SQL을 실행하면 파싱 과정(문법 검사, 구문 분석)을 거쳐 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립하는데 Oracle은 기본적으로 비용 기반 옵티마이저를 사용한다.

 

실행 계획 수립이 완료되면 SQL을 실행하고 데이터를 인출한다.

 

옵티마이저 엔진

1. Parser : SQL 문장의 각 요소를 파싱해서 파싱트리를 만듬(문법 검사, 구문 분석)

2. Query Transformer : SQL문을 효율적으로 실행하기 위해 더 일반적이고 표준적인 형태로 옵티마이저가 변환.

3. Estimator : 통계정보를 사용해서 SQL 실행비용을 계산한다. 총 비용은 최적의 실행 계획을 수립하기 위해서이다.

4. Plan Generator : SQL을 실행할 계획들을 수립한다.

5. Row-Source Generator : 옵티마이저가 생성한 계획을 SQL Engine이 실행 가능한 코드로 포맷한다.

6. SQL Engine : SQL 실행

 

인덱스

* 무조건 인덱스를 만든다고 좋은 것이 아니다. 인덱스 또한 자원이기 때문에 남발할 경우 데이터베이스에 부하를 준다.

- 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공하는 일종의 색인기술이다.

- 인덱스는 인덱스 키로 정렬되어 있기 때문에 원하는 데이터를 빠르게 조회한다.

- 인덱스는 오름차순 및 내림차순 탐색이 가능하다.

- 하나의 테이블에 여러 개의 인덱스를 생성 가능하며 하나의 인덱스는 여러 개의 칼럼으로 구성 가능하다.

- 기본키는 자동으로 인덱스가 만들어진다.

 

인덱스 구조

- 인덱스는 Root Block(가장 상위) - Branch Block(Leaf 주소를 보관하는 포인터) - Leaf Block(인덱스 키와 ROWID로 구성) 으로 구성된다.

 

인덱스 사용법

1. 생성

// IDX_EMP 라는 이름을 가진 인덱스를 생성
// ENAME은 오름차순, DEPTNO는 내림차순이다.
CREATE INDEX IDX_EMP 
ON EMP (ENAME, DEPTNO DESC)

2. 스캔

1) Index Unique SCAN

- Index Unique SCAN은 인덱스의 키 값이 중복되지 않는 경우, 해당 인덱스를 사용할 때 발생된다.

 

2) Index Range SCAN

- Index Range SCAN은 특정 범위를 조회할 경우 발생 한다.(ex. LIKE or BETWEEN or <= or >= 등)

 

3) Index Full SCAN

- 인덱스에서 키가 많은 경우 처음부터 끝0까지 Full로 스캔한다.

 

3. 예제

select * from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO
and emp.DEPTNO = 10;

 

옵티마이저 조인

1. Sort Merge 조인

- 조인하는 두 개의 테이블을 SORT_AREA 메모리 공간에 로딩하고 정렬을 수행한다.

- 정렬 후 병합을 하게 되는데 아무래도 정렬이 수행되기 때문에 테이터양이 많을 수록 성능이 떨어진다.

select /*+ ordered use_merge(dept)*/*
from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO
and emp.DEPTNO = 10;

use_merge 힌트는 Sort Merge 조인을 사용하기 위한 것이다.

 

2. Nested Loop 조인

- 하나의 테이블에서 데이터를 먼저 찾고 다른 테이블을 조인하는 방식이다. 먼저 조회하는 테이블을 외부 테이블이라고 하고 다음 테이블을 내부 테이블이라고 한다. 

- Nested Loop 조인은 스캔되는 범위를 줄이기 위해 외부 테이블의 크기가 작은 것을 먼저 찾아야 한다. Random Access가 발생하는데 많이 발생하면 성능이 하락한다. 

select /*+ ordered use_nl(dept)*/* 
from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO
and emp.DEPTNO = 10;

use_nl 힌트는 Nested Loop 조인을 사용하기 위한 것이고 order은 emp, dept 적은 순서대로 조인을 하게 하는 것이다.

 

3. Hash 조인

- 두 개의 테이블 중 작은 테이블을 Hash 메모리에 로딩하고 조인 키를 사용해서 Hash 테이블을 생성한다.

- Hash 함수를 사용해서 계산하기 때문에 CPU 자원 소모를 많이 한다. 

select /*+ ordered use_hash(dept)*/*
from emp, DEPT
where emp.DEPTNO = DEPT.DEPTNO
and emp.DEPTNO = 10;

use_hash는 Hash 조인을 사용하기 위한 것이다.