실행계획의 유형

실행계획의 고정화
실행에 따라서 동적으로 최적화를 하는 것이 가장 이상적인가?
조건에 따라 유인하게 변화 / 튜닝된 실행계획을 고정

아우트라인 (outline) 이란?  제조법 (recipe)
실행계획의 요약본을 저장해 두었다가 참조해 실행계획을 수립하는 기능
완전한 실행계획이 아니라 동일하게 재현할수 있는 최소한의 참조정보

적용기준
잘정의된 옵티마이징 팩터와 적절항 SQL 을 기반으로 대부분은 옵티마이저에게 맡기고,
특별히 문제가 있는 경우만 아우트라인으로 통제

적용방법
범용적으로 관리하거나 , 개별적으로 관리할수도 있으며 필요에 따라 적용시키거나 금지 가능 , 필요시 강제로 편집도 가능 , 익스포트 해 두었다가 원할때 임포트 가능
그룹을 지정하여 선별적인 적용 가능

아우트라인의 생성과 조정
package : dbms_outln  , dbms_outln_edit
procedure:
create_outlin : 지정된 건을 공유커서에서 찾아 아우트라인 생성
clear_used : 지정한 아우트라인을 제거
drop_by_cat : 지정한 카테고리에 속한 아우트라인들을 제거
drop_unused : sql 파싱에 사용된 적이 없는 아우트라인을 제거
update_by_cat : 어떤 카테고리를 새로운 카테고리로 변경
generate_signature : 지정한 sql  문에 대한 식별자를 생성

생성: alter session set create_stored_outlines = category_name : 지정한 카테고리 생성
사용 :alter session set use_stored_outlines = category_name : 지정한 카테고리 사용

개별 아우트라인의 적용
한 세션에만 유효하고 다른세션에는 전혀 영향을 미치지 않음
공식적으로 적용하기에 부담이 있을때 사전검토를 위한 사용형태
기존 아우트라인 조정시 복제하여 수정한 후 대체시키기 위한 사용형태

1. 기존의 아우트라인에서 새로운 개별 아우트 라인으로 복제한다
create private outline prv_ol_1 from outln_1;
2. 아우트라인을 수정할 수 있는 룰이나 dbms_outln_edit 패키지에 있는 여러 프로시저를 이용해 조인의 순서를 조정하는 등의 작업을 한다.
3. 생성된 개별 아우트라인을 검증하기 위해 use_private_outline 을 true 로 지정하고 검증을 실시한다
4. 충분한 검증이 끝나면 공식적인 적용을 위해 다음의 작업을 수행한다
create or replace outline outln_1 from private prv_ol_1;
5. use_private_outlines 을 false 로 지정하여 개별 아우트라인의 수행을 종료한다.

아우트라인의 관찰 : 딕셔녀리 뷰를 통해 확인
user_outlines
user_outlines_hints

selet empno from emp e where e.emp_no = 7856

name : sys sys_outline_0522
category : lhss001
used : unused
sel_text :selet empno from emp e where e.emp_no = "SYS_B_0"
signature : sql_text 를 자동 인식하여 raw 타입으로 된 sql 식별자

동일한 sql을 다른 카테고리에 생성하면 signature 는 동일, name 은 달라짐

아우트라인의 저장형태 : 조인방법과 조인순서, 사용하는 인덱스 , 비용, 카디널리티 보유
테이블에 저장되어 있는 값이므로 직접 수정도 가능
기본값으로 지정된 SYSTEM 테이블 스페이스가 아닌 원하는 테이블스페이스에 생성 가능

업그레이드 시의 적용기준

1. 특정세션이나 전체 sql 에 대하여 다음과 같이 특정 카테고리에 아우트라인 생성 시작
alter session set create_stored_outlines = category_name;
2. 많은 sql 의 아우트라인이 생성되도록 오랜 동안 수집 , 월 단위 이상이나 특정 기간만 수행되는 것은 별도 처리
3. 아우트라인 생성을 종료시키려면 create_stored_outlines 파라미터를 false로 지정
4. dbms_stats 패키지를 이용하여 통계정보를 생성
5. 옵티마이저 모드를 rule 에서 choose 로 변경
6. use_stored_outlines 파라미터에 생성한 카테고리를 지정하여 아우트라인을 적용

비용 기준에서 전환 시
1. 이전버전에 대한 아우트라인을 생성할때 가능하다면 다양한 분류별로 카테고리를 지정
2. 많은 sql의 아우트라인이 생성되오록 오랜 동안 수집별도 처리
3. 아우트라인 생성을 종려시키려면 create_stored_outlines 파라미터를 false 로 지정
4. 업그레이드를 수행한 후 dbms_stat   패키지를 사용하여 통계정보를 생성
5. 애플리케이션을 수행하면서 테스트 실시
6. 비전 업그레이드로 인해 수행속도에 심각한 문제가 발생하엿다면 생성해 두었던 아우트라인을 적용

옵티마이저의 한계
현재의 정보만으로 미래를 예측해야 함
정확한 분포도 산정의 어려움
단지 논리적으로 이미 존재하는 길을 선택할 뿐임
현실에서 사용되는 대부분의 조건은 변수 형태로 부여

결국 중요한 것은 다양한 사용 형태를 만족할 수 있도록 종합적이고 전략적인 차원에서 데이터 구조와 인덱스를 설계하고, 수준 높은 SQL 을 구사하는 것이 반드시 필요

옵티마이저의 최적화 절차
SQL -> Parser -> Query Transformer -> Estimator -> Plan Generator -> Row source generator

사용자가 실행한 sql은 데이터 딕셔너리를 참조하여 파싱을 수행
옵티마이저는 파싱 결과를 이용해 논리적으로 적용 가능한 실행 계획 형태를 선택하고
힌트를 감안하여 일치적으로 잠정적인 실행계획들을 생성

데이터 딕셔너리의 통계정보 (데이터의 분포도, 테이블 저장구조,인덱스 구조, 파티션 형태.

비교연산자 등을 감안하여 각 실행계획의 비용을 계산

실행계획들의 산출된 비용을 비교하여 가장 최소의 비용을 가진 실행계획을 선택

질의 변환기 Query transformer
보다 양호한 실행계획을 얻을 수 있도록 적절하게 sql 형태를 변환하는 것
-인라인 뷰나 뷰의 병합 (merging)
-조건절의 진입 (predicate pushing)
-서브쿼리의 비내포화(subquery unnesting)
-실제뷰(Materialized view) 의 질의 재생성(rewrite)
-OR 조건의 전개(expansion)

view merging
뷰정의시에 지정한 쿼리 (뷰쿼리)를 엑세스가 수행되는 쿼리 (엑세스쿼리)에 병합
predicate pushing
뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스 쿼리와 조건절을 진입시키는 질의 변환
subquery unnesting
서브쿼리는 경우에 따라서 내포관계를 해체하여 조인형식으로 대체함으로써 보다 양호한 수행속도를 얻을 수 있음
Query Rewrite
실제뷰는 테이블과 밀접한 논리적 관계를 가진 물리적 집합이므로 최적의 집합을 처리하도록 쿼리를 재생성
OR expansion
OR 조건이 처리주관 조건이 되면 여러개의 단위 쿼리로 분기하고 UNION ALL 로 연결하는 질의로 변환

비용산정기 (Estimator)
Selectivity : 처리할 집합에서 해당조건을 만족하는 로우가 차지하는 비율
Cardinality : 판정대상이 가진 결과 건수 혹은, 다음 단계로 들어가는 중간결과 건수
Cost : 실행계획의 각 연산들을 수행할 때 소요 시간비용을 상대적으로 계산한 예측치

선택도 : 대상 집합에서 해당 조건을 만족하는 로우가 차지하는 비율
판정단위는 ? 선택도 판정단위는 개별 컬럼이 아니라 해당 엑세스를 주관할수 있는 조건들
선택도는 0.0 에서 1.0 사이의 값을 갖도록 생성
선택도의 값이 낮다는 것은 전체에서 차지하는 비율이 낮다는 것 변별력이 좋다는 것
좋은 선택도를 가진 것을 처리주관으로 결정하면 보다 적은 처리범위를 엑세스

카디널리티 : 판정대상이 가진 결과건수 혹은 다음단계로 들어가는 중간결과 건수
산정방법: 선택도와 전체로우수 계산
select statement
table access (by index rowid) of 'emp' (cost=2 , card=1 . bytes =32)
index (full scam) of 'pk_emp' (unique) (cost=1 , card=15)

필요한 이유
선택도는 단지 비율일 뿐임 ,같은 대상 집합에 대해서는 비율만으로 충분하지만 만약 조인의 순서나 방향 등의 결정을 위해 먼저 수행될 집합을 선택하기 위해서

비용:실행계획 상의 각 연산들을 수행할때 소요되는 시간 비용을 상대적으로 계산한 예측치
산정방법: 통계정보에 CPU 와 메모리 상황, 디스크 I/O 비용도 고려하여 계산

동일한 평가결과의 우선순위 결정
규칙기준 : 로우캐시에 나타난 순서 / 비용기준 : 인덱스 명의 ASCII 값

실행계획 생성기
쿼리를 처리할 수 있는 적용 가능한 실행계획을 선별하고, 그들에 대한 비교검토를 거쳐 가장 최소의 비용을 가진것을 선택

특기사항 : 후보로 등장했던 여러개의 실행계획들은 다양한 처리단위 (query block) 조합으로 구성
최적화란 쿼리가 실행되기전에 아주 짧은 시간내에 수행해야만 하는 작업
이슈 : 최적화에 최대한의 시간을 투자한다면 조금 더 나은 실행계획을 얻을수 있을지 모르지만 이로인한 부하가 전체 수행시간에 너무 많은 비중을 차지한다면 켤고 적절 할수없다.

적용적 탐색 (Adaptive search) 과 경험적 (Heuristic) 기법을 적용하여 초기자를 선택( Cutoff) 하는 전략을 사용함

adaptive search:쿼리수행의 총예상수행시간에 대해 최적화를 하는 시간이 일정비율을 넘지 않도록 하는 탐색전략
Heuristic cutoff : 탐색도중이더라도 최적이라고 판단되는 실행계획을 발견하면 더이상 진행하지 않고 멈추는 것
힌트 ? 고수가 옆에서 훈수를 해주는 것과 유사 (보다 쉽게 최적을 찾을 수 있다)

질의의 변환
수식연산
sales_qty > 1200/12 --> sales_qty > 100
sales_qty*12 > 1200 --> 좌우를 이항해서 연산하지는 않음
조건연산
job like 'SALESMAN' --> job = 'SALESMAN' (가변길이 타입만)
job in('clerk' , 'manager') --> job = 'clerk' or job = 'manager'
sales_qty > any (:in_qty1, :in_qty2) --> sales_qty > :in_qty1 or sales_qty >:in_qty2
where 10000 > any (select sal from emp where job= 'clerk')
-->where exists(select sal from emp where job='clerk' and 10000>sal)
sales_qty > ALL(:in_qty1,:in_qty2) --> sales_qty >:in_qty1 and sales_qty >:in_qty2

where 1000> all (select sal from emp where job ='clerk'
--> where not (10000 <= any (select sal from emp where job='clerk'))
--> where not exists (select sal from emp where job='clerk' and 10000 <= sal)
sales_qty between 100 and 200 --> sales_qty >=100 and sales_qty<=200
not (sal<3000 OR comm in NULL) --> not sal < 3000 and comm is not null
--> sal >=3000 and comm is not null
not deptno= (select deptno from emp where empno = 7689)
--> deptno <> (select deptno from emp where empno = 1687)
목적 : 보다 양호한 실행계획을 얻을수 있도록 가능한 최대로 적절하게 SQL 형태를 변환하는것

이행성규칙 (transitivity principle)
where column1 comparison_operator constant and column1 = column2
--> column2 comparison_operator constant
comparison_operator : = .!= , ^= < , >, <=
constant : 연산 ,sql 함수 ,문자열 ,바인드 변수, 상관관계 변수를 포함하는 상수수식

진정한 의미는 ?
select * from emp e , dept d where e.deptno =20 and e.deptno = d.deptno
이행성 규칙에 의해 실행계획이 DEPT 테이블을 먼저 인덱스로 엑세스하는 실행계획이 가능

비교값이 상수수식이 아니면 추론이 안됨

OR 조건들의 UNION ALL 분기 --> UNION ALL 로 각각의 인덱스를 경유하는 실행계획을
 수립하고 이를 결합
select * from emp where job='clerk' or deptno=10;
--> select * from emp where deptno=10
union all
select * from emp where job='clerk' and deptno<>10;

뷰병합
뷰쿼리를 엑세스 쿼리에 병합해 넣는 방식
1.엑세스 쿼리에 있는 뷰를 원래 테이블로 변환
2.남아있는 조건절을 다시 엑세스 쿼리에 병합
3. 컬럼들도 대응되는 원래 테이블의 컬럼들로 병합

view merging 가 불가능한 경우 : 엑세스쿼리에 있는 조건들을 뷰쿼리에 진입
집합연산 (union, union all , intersect , minus)
connect by
rownum 을 사용한 경우
select-list 의 그룹함수 (avg , count, max, min , sum)

pushing predicate : 뷰병합을 할 수 없는 경우를 대상으로 뷰쿼리 내부에 엑세스 쿼리의 조건절을 진입시키는 방식

group by 뷰의 병합 (enable 조건)
complex_view_mergeing
optimizer_secure_view_merging

merge : 조건 처리가 완료된 후 group by
no merge : 뷰쿼리를 먼저 처리한 후 조인

IN  서브쿼리의 뷰병합
처리범위를 줄일 수 있는 조건들이 많아 인라인 뷰로 파고들어 가는 것이 일량을 줄일 수 있다면 뷰병합이 유리
파라메터는 기본값을 TRUE 로 하고 필요하다면 NO_MERGE 힌트를 적용하는 것이 바람직함

바인드 변수의 PEEKING
최초에 실질적인 파싱이 일어날때만 단 한번 변수의 값을 엿본다. 여기서 첫번째 파싱이란 공유 SQL  영역에 처음 등록될때를 의미
적용 파라미터 : optim_peek_user_binds = true