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