[SQLD] .SQL 최적화 원리 (Optimizer)

옵티마이저(Optimizer)

• 개발자가 SQL을 작성하여 실행할 때, 옵티마이저는 SQL을 어떻게 실행할 것인지를 계획하게 된다.
• 실행계획을 수립하고 SQL을 실행한다.
• 데이터베이스 관리시스템의 소프트웨어
• 동일한 결과가 나오는 SQL도 어떻게 실행하느냐에 따라서 성능이 달라진다.

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

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

 

예를 들어

건수가 많은 테이블을 먼저 읽고 건수가 적은 테이블을 읽으면 불필요하게 비교 횟수가 증가하게 된다.

만약 옵티마이저가 비효율적으로 실행계획을 수립하면 SQL개발자는 SQL을 개선해야한다.

 

실행계획을 변경하도록 요청하는 것은 Hint를 사용해서 할 수 있다.

 

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

개발자는 PLAN_TABLE을 조회해서 실행 계획을 확인할 수 있다.

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

 

옵티마이저 실행 방법

1. 개발자가 SQL을 실행하면 파싱(Parsing)을 실행해서 SQL의 문법 검사 및 구문분석을 수행한다.
2. 구문분석이 완료되면 옵티마이저가 규칙 기반 혹은 비용 기반으로 실행 계획을 수립한다.
3. 옵티마이저는 기본적으로 비용 기반 옵티마이저를 사용해서 실행 계획을 수립한다.
   비용 기반 옵티마이저는 통계정보를 활용해서 최적의 실행 계획을 수립하는 것이다.
4. 실행 계획 수립이 완료되면 최종적으로 SQL을 실행하고 실행이 완료되면 데이터를 인출(Fetch)한다.

개발자SQL

Parsing

규칙/비용 기반 옵티마이저

실행 계획

SQL 실행

 

옵티마이저 엔진

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

 

규칙 기반 옵티마이저

실행 계획을 수립할 때 15개의 우선순위를 기준으로 실행 계획을 수립한다.

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

 

ROWID를 사용한 조회가 우선순위 1위이다.

SELECT /*+ RULE */ * FROM EMP WHERE ROWID = "AAAHYhAABAAALNJAAN";

옵티마이저에서 실행방법을 알려주는 것이 힌트(HINT)이다.

 

비용기반 옵티마이저

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

총비용 = 소요시간 혹은 자원의 사용량을 의미

---

인덱스(INDEX)

• 인덱스는 인덱스 키로 정렬되어있기 때문에 원하는 데이터를 빠르게 조회한다.
• 오름차순 내림차순 탐색가능
• 하나의 테이블에 여러 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성 될 수 있다.
• 테이블 생성시 기본키는 자동으로 인덱스가 만들어지고 인덱스의 이름은 "SYSXXXX"이다.

Root Block : 가장 상위 노드

Branch Block : 다음 단계의 주소를 가지고 있는 포인터(Pointer)

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

 

Leaf Block은 Double Linked List 형태로 되어 있어서 양방향 탐색이 가능하다.

 

인덱스 생성

CREATE INDEX INDEX_NAME ON                    EMP (ENAME ASC , SAL DESC ) # ENAME은 오름차순 ,SAL은 내림차순으로 인덱스 생성

 

인덱스 스캔

Unique Index SCAN ( 인덱스 유일 스캔 )

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

 

Index Range SCAN (인덱스 범위 스캔)

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

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

Table Full Scan 데이터를 모두 읽는 것 High Watermark 이하까지만 Table Full Scan을 한다. High Watermark는 테이블에 데이터가 저장된 블록에서 최상위 위치를 의미하고 데이터가 삭제되면 High Watermark가 변경된다.

Index Full SCAN ( 인덱스 전체 스캔 )

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

---

실행계획(Execution Plan)

SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO     AND EMP DEPTNO = 10;

4. NESTED LOOPS

     2. TABLE ACCESS BY INDEX ROWID TABLE LIMBEST.DEPT

            1. INDEX UNIQUE SCAN INDEX (UNIQUE) LIMBEST.SYS_C007959

     3. TABLE ACCESS FULL TABLE LIMBEST.EMP

 

• 1번은 DEPT 테이블의 SYS_C007959 인덱스를 유일하게 조회하였다.
• 2번은 INDEX에서 DEPT 테이블 ROWID를 사용해서 조회하였다.
• 3번은 EMP 테이블을 전체 스캔(FULL SCAN)하였다.
• 4번은 DEPT 테이블과 EMP 테이블을 Nested Loop 방식의 조인을 해서 최종 결과를 만들어 낸 것이다.

Nested Loop 방식의 조인은 DEPT 테이블에서 먼저 데이터를 찾고 그다음 EMP 테이블을 찾는 것을 의미한다.

이러한 것을 Random Access 라고 한다.

RANDOM ACCESS가 많이 발생하면 성능 지연이 발생한다.

 

INDEX 검색 - > ROWID를 사용해서 DEPT 테이블을 조회

 

이때 먼저 조회하는 테이블을 Outer Table 이라고 하고 그 다음에 조회되는 테이블을 Inner Table 이라고 한다.

 

Nested Loop 조인에서는 외부 테이블(선행)의 크기가 작은 것을 먼저 찾는 것이 중요하다.

그래야 스캔되는 범위를 줄일 수 있기 때문

SELECT /*+ ordered use_nl(b) */ * FROM EMP a, DEPT b WHERE a.DEPTNO = b.DEPTNO      AND a.DEPTNO = 10;

use_nl 힌트는 Nested Loop조인을 실행하게 한다.

ordered 힌트는 FROM 절에 나오는 순서대로 조인을 하게 하는 것이다.

---

Sort Merge 조인

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

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

정렬이 발생하기 때문에 데이터양이 많아지면 성능이 떨어지게 된다.

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

 

/*+ ordered use_merge(b) */ 힌트를 사용해서 SORT MERGE 조인

---

Hash 조인

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

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

선행 테이블이 충분히 메모리에 로딩되는 크기여야 한다.

 

/*+ ordered use_hash(b) */ 힌트를 사용해서 HASH 조인