[SQL 개발자] 4) SQL 최적화 기본 원리 - 자격증 시험 요약

 

[SQL 개발자] 자격증에 대해 요약을 하고 있는데 이제 마지막 단원인 [SQL 최적화 기본 원리]에 대해 정리를 해보았습니다.  50 문항 중 20 문항을 틀리면 합격 하지만 데이터베이스는 전산에서 기본으로 해야 하므로 잘 배워 두세요.

 

아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약 하였습니다.

 

 

1. 옵티마이저(Optimizer)와 실행 계획

 

1.1 옵티마이저

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

어떤 옵티마이저 방법을 사용 하느냐에 따라 성능에 차이가 난다.

옵티마이저는 여러 실행 계획 중에서 최저비용의 계획을 이용해 SQL을 실행 한다.

옵티마이저에게 실행계획을 변경하도록 하려면 힌트(HINT)를 사용 한다.

 

1.2 옵티마이저 실행 계획

옵티마이저 실행 계획은 PLAN_TABLE에 저장되어 있어 조회가 가능 하다.

 

 

2. 옵티마이저의 종류

규칙기반 옵티마이저와 비용기반 옵티마이저가 있다.

 

2.1 옵티마이저 실행 방법(순서)

(1) 개발자가 SQL 실행

(2) 파싱(Parsing) 문법 검사 및 구문 분석

(3) 규칙 기반 또는 비용 기반 옵티마이저 선택(기본적으로 비용 기반 옵티마이저 실행)

(4) 실행 계획 수립

(5) SQL 실행

 

옵티마이저 엔진

옵티마이저	설명
Query Transformer	- SQL문을 효율적으로 실행하기 위해서 옵티마이저가 변환한다. - SQL이 변환되어도 그 결과는 동일하다.
Estimator	- 통계 정보를 사용해서 SQL 실행 비용을 계산한다 - 총 비용은 최적의 실행 계획을 수립하기 위해서이다.
Plan Generator	- SQL을 실행할 실행 계획을 수립한다.

 

2.2 규칙기반 옵티마이저

규칙 기반 옵티마이저(Rule base Optimizer)는 실행 계획을 수립할 때 15개의 우선 순위를 기준으로 실행 계획을 수립한다.

SQL문에 /*+ RULE */를 사용해서 옵티마이저에서 규칙 기반 옵티마이저로 실행하도록 알려준다

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

 

2.3 비용 기반 옵티마이저

- 비용 기반 옵티마이저(Cost base Optimizer)는 오브젝트 통계 및 시스템 통계를 사용해서 총 비용을 계산 한다.

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

- 통계정보가 부적절한 경우 성능 저하가 발생할 수 있다.

 

3. 인덱스 (Index)

 

3.1 인덱스 (Index)

- 인덱스는 데이터를 빠르게 검색할 수 있는 방법을 제공한다.

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

- 오름차순(Ascending), 내림차순(Descending) 탐색이 가능하다.

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

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

- 인덱스의 구조는 Root Block, Branch Block, Leaf Block으로 구성되고, Root Block은 인덱스의 트리에서 가장 상위에 있는 노드를 의미하며, Branch Block은 다음 단계의 주소를 가지고 있는 포인터로 되어 있다

- Leaf Block은 인덱스 키와 ROWID로 구성되고, 인덱스 키는 정렬되어 저장된다.

 

3.2 인덱스 생성

- CREATE INDEX문을 사용하여 생성이 가능하다.

- 한 개 이상의 column을 사용해서 생성할 수 있다.

- 기본적으로 사용된 column의 오름차순으로 정렬되고 DESC 구를 포함하면 내림차순으로 정렬.

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

 

3.3 인덱스 스캔(Index Scan)

1) 인덱스 유일 스캔(Index Unique Scan)

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

- 예를 들어 EMPNO(사원번호)가 중복되지 않는 경우 하나의 EMPNO를 조회 한다.

 

2) 인덱스 범위 스캔(Index Range Scan)

- SELECT문에서 특정 범위를 조회하는 WHERE문을 사용할 경우 발생한다.

- Like, Between이 대표적인 예이다. 단, 데이터 양이 적은 경우에는 인덱스 자체를 실행시키지 않고 TABLE FULL SCAN이 될 수 있다.

- Index Range Scan은 인덱스의 Leaf Block의 특정 범위를 스캔한 것이다.

 

3) 인덱스 전체 스캔(Index Full Scan)

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

 

 

4. 옵티마이저 조인(Optimizer Join)

 

힌트를 사용해서 /*+ ordered use_nl(TABLE NAME) */ 이런 방법으로 사용하며 종류에 따라 use_nl, use_merge, use_hash을 사용 한다.

 

4.1 Nested Loop Join (/*+ ordered use_nl(TABLE NAME) */)

- Nested Loop Join은 하나의 테이블에서 데이터를 먼저 찾고, 그 다음 테이블을 조인하는 형식으로 실행된다.

- Nested Loop Join에서 먼저 조회되는 테이블을 Outer Table, 그 다음에 조회되는 테이블을 Inner Table이라 칭한다.

- 외부 테이블(선행 테이블, Outer Table)의 크기가 작은 것을 먼저 찾는 것이 중요하다.

- Nested Loop Join에서는 RANDOM ACCESS가 발생하는데, RANDOM ACCESS가 많이 발생하면 성능 지연이 발생한다. 그러므로, RANDOM ACCESS의 양을 줄여야 성능이 향상된다.

- Nested Loop Join은 조인 칼럼의 인덱스가 존재해야 한다

 

 

2) Sort Merge Join (/*+ ordered use_merge(TABLE NAME) */)

- Sort Merge Join은 두 개의 테이블을 SORT_AREA라는 메모리 공간에 모두 로딩(Loading)하고 정렬(Sort)을 수행한다.

- 두 개의 테이블에서 Sort가 완료되면, 두 개의 테이블을 Merge한다.

- 정렬 데이터 양이 너무 많으면, 정렬은 임시 영역(디스크)에서 수행되기 때문에 성능이 급격하게 떨어진다.

 

3) Hash Join

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

- Hash 함수를 사용해서 주소를 계산하고, 해당 주소를 사용해서 테이블을 조인하기 때문에, CPU 연산을 많이 한다.

- 특히, Hash Join 시에는 선행 테이블이 충분히 메모리에 로딩되는 크기여야만 한다.

 

 

- copy coding -