[SQLD] SQL 최적화의 원리 - 옵티마이저와 실행 계획, 옵티마이저 종류, 인덱스, 실행 계획, 옵티마이저 조인

< 옵티마이저와 실행 계획 >

1. 옵티마이저 (Optimizer)

- SQL 개발자가 SQL을 작성해 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 됨

(즉, 실행 계획을 수립하고 SQL을 실행)

- 옵티마이저는 SQL을 실행 계획을 수립하고, SQL을 실행하는 데이터베이스 관리 시스템의 소프트웨어

- 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라짐

- 옵티마이저의 실행 계획은 SQL 성능에 아주 중요한 역할을 함

 

2. 옵티마이저 특징

- 옵티마이저는 데이터 딕셔너리에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해서 예상되는 비용 산정

- 옵티마이저는 여러 개의 실행 계획 중에서 최저비용을 가지고 있는 계획을 선택해서 SQL을 실행

 

3. 옵티마이저의 필요성

- SQL 개발자가 작성한 SQL문을 어떻게 실행하느냐에 따라 성능이 달라짐

- 옵티마이저가 비효율적으로 실행 계획을 수립하면, SQL 개발자는 SQL을 개선해야함

- 옵티마이저에게 실행 계획을 변경하도록 요청할 수 있는데 이때 힌트를 사용함

 

4. 옵티마이저 실행 계획 확인

- 옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장

- SQL 개발자는 PLAN_TABLE을 조회해 실행 계획을 확인할 수 있음

- TOAD에서 Execution Plan Current SQL 메뉴를 클릭하는 것이 가장 편리

TABLE ACCESS FULL은 EMP2 테이블의 전체를 모두 읽었다는 의미

 

 

< 옵티마이저 종류 >

1. 옵티마이저의 실행 방법

- 개발자가 SQL을 실행하면 파싱을 실행해 SQL의 문법 검사 및 구분 분석을 수행

- 구문 분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립

- 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해 실행 계획을 수립

* 비용 기반 옵티마이저 : 통계정보를 활용해 최적의 실행 계획을 수립

- 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)함

 

개발자 SQL -> Parsing -> 옵티마이저 -> 실행 계획 -> SQL 실행

 

- 옵티마이저 엔진

옵티마이저	설명
Query Transformer	- SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환
Estimator	-  통계 정보를 사용해 SQL 실행비용을 계산 - 총 비용은 최적의 실행 계획을 수립하기 위함
Plan Generator	SQL을 실행할 실행 계획을 수립

 

2. 옵티마이저 엔진

- 규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선 순위를 기준으로 실행 계획을 수립

* 최신 Oracle 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 기본적으로 사용

 

- 옵티마이저 엔진

우선순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 기본키를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 기본키에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬 - 병합(Sort Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

SELECT /*+ RULE */ *
FROM EMP2
WHERE ROWID = 'AAAHYhAABAAALNJAAN';

 

힌트로 규칙 기반 옵티마이저를 실행하도록 알려줌

 

 

3. 비용 기반 옵티마이저

- 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산

- 총 비용이란 SQL문을 실행하기 위해 예상되는 소요시간, 혹은 자원의 사용량을 의미

- 총 비용이 적은 쪽으로 실행 계획을 수립

(단, 비용 기반 옵티마이저에서 통계 정보가 부적절한 경우 성능 저하가 발생할 수 있음)

 

 

< 인덱스 >

1. 인덱스

- 데이터를 빠르게 검색할 수 있는 방법을 제공

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

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

- 하나의 테이블에 여러개의 인덱스를 생성할 수 있고, 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있음

- 테이블을 생성할 때 기본키는 자동으로 인덱스가 만들어지고 인덱스의 이름은 SYSXXXX이다.

 

Root Block - Branch Block - Leaf Block으로 구성됨

- Root Block : 인덱스 트리에서 가장 상위에 있는 노드를 의미

- Branch Block : 다음 단계의 주소를 가지고 있는 포인터로 되어 있음

- Leaf Block : 인덱스 키와 ROWID로 구성됨. 인덱스 키는 정렬되어 저장됨 (Double Linked List 형태로 되어 있어 양방향 탐색이 가능)

Leaf Block에서 인덱스 키를 읽으면 ROWID를 사용해 EMP 테이블의 행을 직접 읽을 수 있음

 

 

2. 인덱스 생성

- 인덱스 생성은 'CREATE INDEX' 문을 사용해 생성 가능 

- 인덱스를 생성할 때는 한 개 이상의 칼럼을 사용해 생성 가능

- 인덱스 키는 기본적으로 오름차순으로 정렬 (DESC구를 포함하면 내림차순으로 정렬)

CREATE INDEX IND_EMP
ON EMP(ENAME ASC, SAL DESC);

CREATE INDEX 인덱스 명 ON 테이블명(컬럼명 ASC/DESC)

 

 

3.  인덱스 스캔

 

[ 인덱스 유일 스캔 ]

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

ex) EMPNO가 중복되지 않는 경우 특정 하나의 EMPNO를 조회한다.

SELECT *
FROM EMP
WHERE EMPNO=1000;

EMPNO은 중복되지 않으므로 INDEX UNIQUE SCAN이 발생

 

 

[ 인덱스 범위 스캔 ]

- Index Randge SCAN은 SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생

ex) Like, Between

- 데이터 양이 적은 경우는 인덱스 자체를 실행하지 않고 TABLE FULL SCAN이 될 수 있음

- Index Range SCAN은 인덱스 Leaf Block의 특정 범위를 스캔한 것

SELECT EMPNO
FROM EMP
WHERE EMPNO >= 1000;

 

 

[ 인덱스 전체 스캔 ]

- Index Full SCAN은 인덱스에서 검색되는 인덱스 키가 많은 경우에 Leaf Block의 처음부터 끝까지 전체를 읽어 들임

SELECT ENAME, SAL
FROM EMP
WHERE ENAME LIKE '%' AND SAL > 0;

INDEX FULL SCAN은 인덱스 Leaf Block을 처음부터 끝까지 모두 스캔한 것

 

 

< 실행 계획 >

- SQL문의 실행 계획은 번호 순서대로 읽으면 됨

 

 

< 옵티마이저 조인 >

1.  Nested Loop 조인

- 하나의 테이블에서 데이터를 먼저 찾고 그다음 테이블을 조인하는 방식으로 실행

- Nested Loop 조인에서 먼저 조회되는 테이블을 외부 테이블(Outer Table)이라고 하고 그다음 조회되는 테이블을 내부 테이블(Inner Table)이라고 함

- 외부 테이블(선행 테이블)의 크기가 작은 것을 먼저 찾는 것이 중요함 (그래야 데이터가 스캔되는 범위를 줄일 수 있음)

- Nested Loop 조인은 RANDOM ACCESS가 발생함 RANDOM ACCESS가 많이 발생하면 성능 지연이 발생함

(RANDOM ACCESS의 양을 줄여야 성능이 향상됨)

 

 

1. dept의 인덱스를 먼저 검색

2. dept 인덱스의 ROWID를 사용해서 dept 테이블을 읽음

3. dept에서 emp 테이블의 인덱스를 찾음 (RANDOM ACCESS)

4. emp 인덱스를 사용해 다시 emp 테이블에서 데이터를 찾음

5. 인출(Fetch)를 실행해 전송

 

 

 

use_nl

use_nl() 힌트를 사용해 Nested Loop 조인을 실행하게 했다

- EMP2 테이블을 먼저 FULL SCAN하고 그다음 DEPT2 테이블을 FULL SCAN하여 Nested Loop 조인을 함

- ordered 힌트는 FROM절에 나오는 테이블 순서대로 조인을 하게 함

- ordered 힌트는 혼자 사용되지 않고 use_nl, use_merge, use_hash 힌트와 함께 사용

 

 

 

2.  Sort Merge 조인

- 두 개의 테이블을 SORT_AREA 라는 메모리 공간에 모두 로딩하고 SORT를 수행

- 두 개의 테이블에 대해서 SORT가 완료되면 두 개의 테이블을 병합(Merge)

- Sort Merge 조인은 정렬(SORT)이 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 됨

- 정렬 데이터 양이 너무 많으면 정렬은 임시 영역에서 수행됨 (임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어짐)

1. 양쪽 테이블의 처리 범위를 각자 Access

2. 각각 정렬

3. 정렬한 결과를 차례로 Scan하며 연결고리의 조건으로 Merge

 

use_merge

use_merge() 힌트를 사용해 Sort Merge 조인을 실행하게 했다

- SORT 단계와 Merge 단계로 실행됨

- ordered 힌트와 함께 사용해야함

- FROM 절 뒤에 나오는 테이블을 순서대로 조인하게 함

 

 

3. Hash 조인

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

- 해시 함수를 사용해 주소를 계산하고 해당 주소를 사용해 테이블을 조인하기 때문에 CPU연산을 많이함

- 특히 Hash 조인 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야함

 

 

use_hash

use_hash() 힌트를 사용해 Hash 조인을 실행하게 했다