SQL 개발자 (SQLD) 자격증 따기 Part.3 섹션 1.

SQL 최적화의 원리

SQLD에서는 2~3문제 출제되는 비교적 비중이 낮은 문제(SQLP에서 더 중요하게 본다)

 

1. 옵티마이저

- SQL 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지 계획하게 된다. ~ SQL 실행 계획을 수립하고 SQL을 실행한다

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

- 동일한 결과가 나오는 SQL도 어떻게 수행하느냐에 따라 성능이 달라진다. 따라서 옵티마이저의 실행 계획은 SQL 성능에 아주 중요한 역할을 한다.

1) 특징

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

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

 

SQL 실행구조

~ AND 연산일때 작은 테이블을 먼저 읽게 하는 것이 더 나은 조회 속도를 낼 수 있다

2) 옵티마이저의 실행계획 확인

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

SQL Developer 프로그램에서는 F10으로 실행계획 확인가능

 

2. 옵티마이저 종류

1) 실행 방법

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

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

- 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해 실행 계획 수립. 비용 기반 옵티마이저는 통계 정보를 활용해 최적의 실행 계획을 수립

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

 

cf) 옵티마이저 엔진

쿼리 트랜스포머 : SQL문을 효율적으로 실행하기 위해 옵티마이저가 변환한다. SQL이 변환되어도 결과 동일

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

Pan Ganerator : SQL 실행 계획을 수립한다

 

- 규칙 기반 옵티마이저 vs 비용 기반 옵티마이저

- 최신 오라클 버전은 규칙 기반 옵티마이저보다 비용 기반 옵티마이저를 기본적으로 사용한다

cf) 우선순위 1 : ROWID를 사용한 1개 행/ 우선순위 15 : 전체 테이블 Full Scan

비용 기반 옵티마이저

- 비용 기반 옵티마이저는 오브젝트 통계 및 시스템 통계를 사용해 총 비용을 계산

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

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

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

3. 인덱스

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

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

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

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

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

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

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

- Leaf Block은 Double Linked LIst로 구성되어 양방향 탐색이 가능하다

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

1) 인덱스 스캔

(1) 인덱스 유일 스캔

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

(2) 인덱스 범위 스캔

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

- 데이터 양이 작은 경우 TABLE FULL SCAN가 발생할 수 있다

- INDEX RANGE SCAN은 인덱스의 LEAF BLOCK의 특정 범위를 스캔한 것이다(관계 연산자 사용)

(3) 인덱스 전체 스캔

- 인덱스 FULL SCAN은 인덱스에서 검색되는 인덱스 키가 많은 경우 LEAF BLOCK의 처음부터 끝까지 전체를 읽어 들인다

 INDEX RANGE SCAN은 인덱스의 LEAF BLOCK의 전체 범위를 스캔한 것이다

4. 실행계획 : 옵티마이저에 의해 실행계획이 정해지고 모두 똑같이 스캔하는 것이 아니다

5. 옵티마이저 조인

1) Nested Loop 조인

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

- 먼저 조회되는 테이블을 외부 테이블이라고 하고 그 외에는 내부 테이블이라고 한다

- 크기가 작은 외부 테이블을 찾는 것이 중요 ~ 데이터 스캔 범위를 줄일 수 있다

- Nested Loop 조인은 Random Access가 발생하게 되므로 발생하는 양을 줄이는 것이 관건이다

2) Sort Merge 조인

- 하나의 메모리 공간에 데이터를 모두 불러온 다음 정렬 수행 후 테이블을 병합, 데이터 양이 많으면 정렬로 인한 성능 저하 발생

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

3) Hash 조인

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

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

- 특히 해시 조인 시 선행 테이블이 메모리에 충분히 로딩될 수 있어야 한다