SQLD(6)-SQL 최적화의 원리

• 옵티마이저와 실행계획

옵티마이저 - SQL실행계획을 수립, DBMS를 관리하는 소프트웨어

                 어떻게 실행을 하느냐에 따라 SQL성능이 달라짐

                 데이터 딕셔너리에 있는 오브젝트 통계등의 정보를 통해 예상되는 비용 산정 ( 최적의 계획을 선택 )

 

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

 

• 옵티마이저 종류

실행방법 - SQL 실행하면 Parsing실행해서 구문분석, 문법검사 수행

               규칙 혹은 비용(default)기반으로 실행계획 수립

               통계정보를 활용해서 최적의 실행 계획을 수립

               SQL실행후 데이터 인출

 

옵티마이저 엔진

옵티마이저	설명
Query Transformer	SQL문을 효율적으로 실행하도록 변환 결과는 동일
Estimator	실행계획 수립을 위해 통계정보를 사용해서 실행비용 계산
Plan Generator	실행 계획을 수립

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

p.215 표 참조

 

* 비용 기반 옵티마이저 - 오브젝트 통계, 시스템 통계를 사용해서 총비용(소요시간, 자원의사용량) 계산

                                 총비용이 적은 쪽으로 계획 수립, 통계정부 부적절한 경우 성능 저하

 

• 인덱스 - 데이터를 빠르게 검색할 수 있는 방법 제공, asc desc

테이블 하나에 여러 인덱스 생성가능, 여러개의 칼럼으로 구성 가능

기본키는 자동으로 인덱스 만들어짐

 

구조 - Root Block(상위노드), Branch Block(다음 단계 주소를 가지고 있는 포인터), Leaf Block(인덱스키(정렬) + ROWID)

Leaf Block은 double linked list로 이루어져 있음

 

• 인덱스 생성

CREATE INDEX [INDEX_NAME] ON TABLE_NAME(COLUMN ASC(Default)/DESC)

한개 이상의 칼럼을 사용해서 생성

• 인덱스 스캔

인덱스 유일 스캔 - 인덱스 키 값이 중복되지 않는 경우

인덱스 범위 스캔 - select문에서 Leaf Block의 특정 범위를 조회하는 where문 사용할 경우 ( Like, Between 대표적 )

인덱스 전체 스캔 - Leaf Block의 전체를 읽어드림

 

• 실행계획

SQL실행 흐름도에서 먼저 조회되는 테이블을 Outter Table, 그 다음에 조회되는 테이블을 Inner Table

 

• 옵티마이저 조인

• Nested Loop 조인

하나의 테이블(Outter Table - 크기 작은 것)에서 데이터 찾고 그 다음 테이블(Inner Table)을 조인하는 방식

Random Access발생(성능 저하의 원인)

 

* ordered use_nl

첫 테이블 full scan 다음 테이블 full scan

• Sort Merge 조인

두 개 테이블을 SORT_AREA 메모리 공간에 로딩후 Sort 후 병합

데이터 양 많아지면 성능 저하 ( 양 많을 시 임시 영역에서 수행되는데 임시 영역은 디스크에 있음 )

* ordered use_merge

 

• Hash 조인

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

CPU연산 많이 발생 ( 주소 계산, 주소를 사용해서 조인)

* ordered use_hash