SQL 과 실행 계획

SQL 과 옵티마이져와의 상관관계를 이해하고 데이터베이스를 좀더 고급으로 이용할수 있다.

SQL -> SQL 해석 (DATA Dictionary 참조)  -> 실행 계획 작성 -> 실행 (Table 추출) -> 결과
사용자는 요구만 하고 OPTIMIZER 가 실행계획을 수립
수립된 실행계획에 따라 엄청난 수행속도 차이 발생
실행계획 제어가 어렵다
OPTIMIZER 가 좋은 실행계획을 수립할수 있도록 전략적인 FACTOR 을 부여

SQL 로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리 경로를 결정

SQL 은 처리절차를 기술한 것이 아니라 결과에 대한 요구
처리절차는 OPTIMIZER 가 생성, 즉 진정한 프로그래머는 옵티마이저
없는 길을 생성해주는 것이 아니라 이미 존재하는 길을 단지 찾아줄 뿐임
사용자가 부여한 영향요소에 따라 최적은 달라짐 (책임은 사용자)
최적이란 주어진 상황에 따라 달라지는 것임
동일한 결과를 얻을수 있는 경로는 많으나 효율성의 차이는 큼
옵티마이저는 결코 전지전능하지 않다.

옵티마이져 영향 요소
인덱스,테이블 구조, SQL 형태, 사용컬럼,연산자 형태, 힌트 사용, 시스템 및 네트워크 상태, DBMS, 버전, 옵티마이저 모드, 통계 정보

옵티마이저의 형태
SQL을 수행해 보면 쉽게 알 수가 있다.
그렇다고 해서 SQL 작성 시마다 통계정보를 확인하고 비용계산을 해볼 수는 없다!
SQL 만으로 최적의 처리경로를 예측할 수 있는 안목을 가지고  작성하는 것과 무조건 결과만 얻겠다는 접근 방법에는 커다란 차이가 있다.

규칙기준 옵티마이저 (Rule_based optimizer)
1) ROWID로 1 로우 엑세스
2) 클러스터 조인에 의한 1 로우 엑세스
3) Unique Hash Cluster 에 의한 1 로우 엑세스
4) Unique Index 에 의한 1 로우 엑세스
5) Cluster 조인
6) Non Unique Hash Cluster key
7) Non Unique 결합 인덱스
8) Non Unique 한 컬럼 인덱스
9) 인덱스에 의한 범위처리
10) 인덱스에 의한 전체범위처리
11) Sort Merge 조인
12) 인덱스 컬럼의 Min , Max 처리
13) 인덱스 컬럼의 Order by
14) 전체 테이블 스캔
15) Non Unique Cluster key

전략적인 인덱스를 구성하면 확률이 크게 증가

비용기준 옵티마이저 (Cost_Based Optimizer)

통계정보로 실제비용을 계산하여 최소비용을 선택
데이터의 상태에 따른 현실적인 처리 경로 수립
생각만큼 완벽한 처리경로는 얻을 수는 없음

테이블 로우 수와 블록수
블록당 평균 로우수
로우의 평균 길이
컬럼별 상수값의 종류
분포도
컬럼내의 NULL 값의 수
클러스터링 팩터
인덱스의 깊이 (Depth ,Level)
컬럼의 최대,최소값
리프(Leaf) 블록수
가동 시스템의 I/O , CPU 정보

옵티마이저 모드의 종류
First Rows : Mix of cost and heuristics , fast delivery of the first few rows
First Rows n : cost-based approach
All Rows : 비용기준의 Default 모드 , 전체에 대한 Best Throughout

옵티마이저 모드의 선택기준
OLTP 형  -> First rows , First rows n
OLAP 형  -> All rows
옵티마이저 모드를 운영중에 함부로 바꾸는 것은 매우 위험
모드에 따라 항상 실행계획이 변경되는 것은 아니다.

옵티마이저 관련 파라메터
Cursor sharing  force, similar , exact (default)
sql 조건절에 있는 상수값들을 변수로 전환시켜 파싱
Exact 는 대.소문자, 공백, 비교 상수값이 조금만 달라고 공유 못함

DB File MultiBlock read count
full table scan, index fast full scan 을 할때 한번 I/O 에 읽을 블록 수를 지정
운송단가와 같은것, 옵티마이저가 비용을 계산할때 중요하게 사용

Optimizer index caching
nested loops 조인이나 in list 탐침 등의 인덱스를 반복 엑세스에서 인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 의미
즉 랜덤의 비용감소를 의미하므로 옵티마이저가 인덱스 랜덤 엑세스를 선택하는 경향이 증가함

Optimizer index cost adj
비용계산을 할때 인덱스를 엑세스의 비중을 조정하는 역활을 담당
100 은 계산된 비용을 그대로 적용한다는 의미, 10을 주었다면 1/10로 계산하겠다는 의미
인덱스 엑세스가 전체테이블 스캔으로 자주 나타나며 이값을 조정

동적 표본화 (Dynamic sampling)
소량표본을 동적으로 추출하여 통계정보로 활용
통계정보를 가지고 있지 않거나 여러등의 문제로 사용할수 없거나, 너무 오래되어 신뢰할수 없을 때 적용

SQL 파싱마다 표본을 추출하므로 적은 양의 데이터 처리나 빈번하게 수행되는 경우는 적용하지 말것
동적 표본만으로도 충분히 좋은 수행속도를 낼수 있다거나, 전체 수행시간에 비해 표본 추출시간이 적다거나 매우 오래 수행되는 배치처리인 경우에 적용
실행계획을 위한 최소한의 표본만 사용
표본량에 따라 적중률이 비례하는 것이 아니므로 너무 높은 레벨을 지정할 필요없음
초기값은 기본값을 그대로 적용하고, 필요시 특정 세션에만 지정
레벨을 증가할수록 표본으로 추출하는 블록 수는 증가

독립접으로 존재하는 집합들의 데이터를 사용자 요구와 주어진 다양한 영향요소를 고려하여 최적화된 처리방법을 스스로 결정
옵티마이저 : 구체적 처리과정을 생성  /  사용자 : 요구사항 지시

규칙기준 옵티마이져 : 사전에 정의된 규칙을 기준으로 처리경로 설정
비용기준 옵티마이져: 결과 산출까지의 비용을 기준으로 처리경로 결정

옵티마이져는 현재 존재하는 다양한 영향요소 만을 고려하여 최적경로 수립
옵티마이져가 하는 판단의 정확도를 높이기 위해서는 매우 다양한 것들이 고려

실행계획 고정화 방안
상황에 따른 최적 경로수립이 필요하나 때론 특정 처리경로로 고정이 필요
개념 및 활용방법과 적용 기준을 제시

옵티마이져 내부처리 단계
옵티마이져는 크게 3단계의 내부처리 단계를 가짐
질의 변환기 Query transformer/ 비용산정기 Estimator / 실행계획 생성기 plan generator

SQL 쿼리 재구성
이행성 규칙 : 연산자 유형에 따라 SQL을 재구성
뷰병합 : 뷰쿼리를 엑세스 쿼리로 병합, 엑세스 쿼리를 뷰쿼리로 병합

바인드 함수 PEEKING
최적화는 상수 조건이나 변수 조건이냐에 따라 크게 영향 : 논리적 한계
초기 상수값 참조를 위한 일종의 커닝