SQL ->
Parser -> (Parsed Query) ->
질의변환기:Query Transformer -> (Transformed Query) ->
비용산정기:Estimator -> (Query + Estimates) ->
실행계획 생성기:Plan Generator -> (Query Plan) ->
Row Source Generator
- 사용자가 실행한 SQL은 데이터 딕셔너리를 참조하여 파싱을 수행
- 옵티마이져는 파싱 결과를 이용해 논리적으로 적용 가능한 실행계획 형태를 선택하고, 힌트를 감안하여
일차적으로 잠정적인 실행계획들을 생성
- 데이터 딕셔너리의 통계정보(데이터의 분포도, 테이블 저장구조, 인덱스 구조, 파티션 형태, 비교연산자) 등을 감안하여
각 실행계획의 비용을 계산
- 실행계획들의 산출된 비용을 비교하여 가장 최소의 비용을 가진 실행계획을 선택(최저가 입찰 방식이므로 항상 최적의
결정이라고만 할 수는 없음)
1) 질의 변환기 (Query Tranformer)
: 보다 양호한 실행계획을 얻을 수 있도록 적절한 SQL 형태를 변환하는 것.
뷰병합 : View Merging
: 뷰 정의 시에 지정한 쿼리(뷰쿼리)를 엑세스가 수행행되는 쿼리(엑세스쿼리)에 병합(Merge)
- 수행시킨 기준으로 뷰에 지정한 부분만 보정하므로 뷰로 발생되는 불이익이 크게 감소
- 엑세스 쿼리의 조건들이 제 역할을 할 수 있고, 뷰 쿼리의 조건까지 추가
- 그러나 몇 가지 제한요소가 있어 이를 준수하지 않으면 뷰병합은 불가능
조건절 진입 : Predicate Pusing
: 뷰 병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스쿼리의 조건절을 진입시키는 질의 변환
- 엑세스쿼리를 뷰쿼리로 진입시키는 방법
- 이렇게 가능한 모든 방법을 동원해서 부여 조건을 뷰쿼리 내에 최대한 반영시켜 보다 양호한 실행계획을 유도
서브쿼리 비내포화 : Subquery Unnesting
: 서브쿼리는 경우에 따라서 내표관계를 해제하여 조인형식으로 대체함으로써 보다 양호한 수행속도를 얻을 수 있음.
- 실제로 대부분의 서브쿼리는 논리적으로 가능하다면 이러한 변환을 거침
- 서비쿼리 비내포화가 불가능하면 서브쿼리를 먼저 수행하거나 나중에 수행하는 실행계획을 수립
- 서브쿼리가 수행되는 순서에 따라서 수행속도에 매우 큰 차이가 나타날 수 있음
실체뷰의 쿼리 재생성 : Query Rewrite
: 실체뷰는 테이블과 밀접한 논리적 관계를 가진 물리적 집합이므로 최적의 집합을 처리하도록 쿼리를 재생성
- 일종의 확장된 뷰병합 기능
- 상황에 따라 어떤 물리적 집합을 엑세스할 것인지 고민할 필요가 없으므로 매우 유용한 기능
- 실체뷰의 쿼리 재생성은 비용 기준에 의해 결정 (테이블 엑세스가 적은 비용을 가지면 쿼리 재생성 하지 않음)
OR 조건의 전개 : OR expansion
: OR 조건이 처리주관 조건이 되면 여러 개의 단위 쿼리로 분기하고 UNION ALL로 연결하는 질의로 변환
- 단지 체크 조건으로만 사용되는 경우에 이와 같은 전개가 발생하면 오히려 큰 비효율이 발생
- 옵티마이져는 비용기준에 의해 전개여부를 결정
사용자 정의 바인드 변수 엿보기 : Peeking
: 쿼리 내에 사용자가 조건절에 지정한 바인드 변수가 있을 때 최초에 수행될 때 적용되었던 값을 이용해 실행계획을 수립하고,
다음 수행부터는 공유하는 방법이다.
2) 비용 산정기 (Estimator)
선택도 : Selectivity
: 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
- 선택도 판정 단위는 개별 컬럼이 아니라 해당 엑세스를 주관할 수 있는 조건들.
- 선택도는 0.0 ~ 1.0 사이의 값을 갖도록 생성
- 0.0 은 대상 집합에서 전혀 존재하지 않는다는 것을 의미
- 1.0 은 모든 대상 집합이 모두 해당된다는 것을 의미
- 선택도의 값이 낮다는 것은 전체에서 차지하는 비율이 낮다는 것, 이는 곧 변별력이 좋다(읽을 양이 적다)는 것을 의미
- 좋은 선택도를 가진 것을 처리주관으로 결정하면 보다 적은 처리 범위를 엑세스
예>
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND JOB = 'CLERK'
AND ENAME LIKE 'SMITH%'
EMP Table 의 인덱스 : 선택도
- EMPNO : 해당사항 없슴.
- ENAME : 0.02 (2%)
- DEPTNO + JOB : 0.1 (10%)
- FULL SCAN : 1.0 (100%)
카디널리티 : Cardinality
: 판정 대상이 가진 결과 건수 혹은, 다음 단계로 들어가는 중간결과 건수
- 산정방법 : 선택도(Selectivity)와 전체 로우수(Num_rows)로 계산
카디널리티가 필요한 이유
- 선택도는 단지 비율일 뿐임. 백만건의 1%와 백 건의 1%는 비율은 같지만 절대량은 같지 않다.
- 같은 대상 집합에 대해서는 비율만으로도 충분하다.
- 그러나 만약 조인의 순서나 방향 등의 결정을 위해 먼저 수행될 집합을 선택해야 한다면 비율만으로는 결정 할 수 없다.
비용 : Cost
: 실행계획 상의 각 연산들을 수행할 때 소요 시간비용을 상대적으로 계산한 예측치
- 산정방법 : 통계정보에 CPU와 메모리 상황, 디스트 I/O 비용도 고려하여 계산
- 동일한 평가결과의 우선순위 결정은 규칙기준의 경우 로우 캐시(Cache)에 나타난 순서로 비용기준은 인덱스명의 ASCII 값으로 한다.
신뢰의 한계성
- 비용산정 과정에서 수많은 가정들을 세우고 다양한 계산식을 적용
- 그러나 가정들이 완벽할 수 없는 불완전한 가정이므로 때로는 잘못된 비용을 산정하게 되는 한계가 발생
- 이런 문제의 보완을 위해서 다양한 힌트들과 다양한 초기화 파라미터들이 계속 추가되고 있다. (옵티마이져의 진화)
3) 실행계획 생성기 (Plan Generator)
: 쿼리를 처리할 수 있는 적용 가능한 실행계획을 선별하고, 그들에 대한 비교검토를 거쳐 가장 최소의 비용을 가진 것을 선택
- 최적화에 최대한의 사간을 투자 한다면 조금 더 나은 실행계획을 얻을 수 있을지는 모르지만 이로 인한 부하가 전체 수행시간에 너무 많은 비중을 차지한다면 결코 적절하다고 할 수 없다.
- 모든 경우를 다 검토할 수는 없다 따라서 적응적 탐색(Adaptive Search)과 경험적(Heuristic) 기법을 적용하여 초기치를 선택(Cutoff)하는 전략을 사용한다.
적응적 탐색 : Adaptive Search
- 쿼리수행의 총 예상수행시간에 대해 최적화를 하는 시간이 일정비율을 넘지 않도록 하는 탐색 전략
- 마치 대국시간이 짧은 속기바둑의 제한시간과 대국 시간이 1-2일인 세계대회의 제한시간이 다르게 부여되는 것과 매우 유사함.
경험적 선택 : Heuristic Cutoff
- 탐색도중이더라도 최적이라고 판단되는 실행계획을 발견하면 더 이상 진행하지 않고 멈추는 것)
- 최적이거나 최소한 아주 좋은 실행계획일 것이라고 판단되는 실행계획들의 일정량을 선별하고 정렬한후에 그 중에서 가장 양호한 것을 선택
출처 : 새로쓴 대용량 데이터베이스 솔루션 vol.1
