1. 옵티마이져 목표 : SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리 경로를 결정
2. SQL과 Optimizer의 관계

• SQL은 결과에 대한 요구
• 옵티마이져는 없는 길을 생성해 주는 것이 아님. 이미 존재하는 길을 단지 찾아줄 뿐 ⇒사용자가 부여한 영향 요소에 따라 논리적으로 존재하는 최적이 달라짐

1. 숫자 목록 관계형 데이터베이스의 출현배경 : 데이터들 간에 물리적인 연결고리를 가지지 않아도 논리적인 연결고리만 있다면 원하는 데이터를 액세스 할 수 있다. ⇒논리적인 방법만으로 데이터를 찾을 수 있는 장치가 필요
2. SQL? 요구서에 불과함. 표준언어로 채택한 것
3. 옵티마이져 ? 사용자의 요구에 따라 그 때마다 최적의 경로를 찾아 주는 역할 책임자
4. 옵티마이져 실행계획에 영향을 주는 것들
5. 옵티마이져는 단지 이미 존재하는 길들 중에서 하나를 선택해 주는 역할 ⇒ 사용자의 역할이 중요

1. 규칙기반 옵티마이져 (RBO : Rule_based Optimizer)
2. 인덱스 구조나 사용 연산자에 부여된 순위로써 최적경로 결정

    ① ROWID로 1로우 액세스
    ② 클러스터 조인에 의한 1로우 액세스
    ③ Unigue HASH Cluster에 의한 1로우 액세스
    ④ Unique INDEX에 의한 1로우 액세스
    ⑤ CLUSTER 조인
    ⑥ Non Unique Hash Cluster Key
   ⑦ Non Unique Cluster Key
   ⑧ Non Unique 결합 인덱스
    ⑨ Non Unique 한 컬럼 인덱스
    ⑩ 인덱스에 의한 범위처리
    ⑪ 인덱스에 의한 전체범위 처리
    ⑫ Sort Merge 조인
    ⑬ 인덱스 컬럼의 MIN, MAX처리
    ⑭ 인덱스 컬럼의 ORDER BY
   ⑮ 전체 테이블 스캔

• 단점

EX> 1,000,000 로우를 가지는 TAB1테이블과 100로우를 가지는 TAB2테이블의 로우수를 알지 못하기 때문에 어느쪽이 전체 테이블 스캔 하는것이 유리한지 구별할 수 없다. EX> 2종류의 값만 가지고 있어 평균분포도가 50%인 컬럼 A로 구성된 인덱스와 10,000종류를 가지고 있어 0.01%의 평균 분포도를 가진 컬럼 B로 구성된 인덱스가 존재. A='10' AND B LIKE '123%'을 주었을때 무조건 ‘=’로 사용한 컬럼A를 사용하는 것이 더 유리하다고 판단.

· 통계정보를 전혀 가지지 않음        · 경우에 따라 비현실적인 처리경로 수립
   - 장점
· 수립될 처리경로 예측가능     	· 사용자가 원하는 처리경로로 유도하기가 용이        · 일반적인 보편타당성이 있음(신뢰성)
    

② 비용기준 옵티마이져(Cost_based Optimizer)

1. 관계형 데이터베이스가 추구하는 이상형
2. 처리방법들에 대한 비용을 미리 산정해보고 그 중 가장 적은 비용이 들어가는

처리방법 선택

1. 통계정보의 형태와 종류는 DBMS 제품이나 버전에 따라 차이

⇒통계정보는 테이블의 로우 수와 블록 수, 블록당 평균 로우수, 로우의 평균길이,

컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL값의 수, 클러스터링 팩터, 

① 현실을 감안한 최적화

1. 위의 예시에서 B인덱스 조건이 A조건보다 적은 범위를 처리하고 있다면 옵티마이져는 B인덱스를 처리주관 조건으로 선택
2. 분포도 : 통계정보에서 가장 주용한 정보

분포도가 좋다→ 컬럼값의 종류가 많다 →조건을 만족하는 처리범위가 좁다는 것을 의미하므로 일의 양을 결정하는 가장 중요한 요소

1. 컬럼의 모든 값들에 대해 분포도를 가지면 정확하겠지만 불가능

⇒컬럼값의 범위별로 분포도 보유하는 방법

1. 컬럼값에 저장할 분포도의 종류는 버켓(Bucker)의 개수에 따라 결정

· 넓이균형 히스토그램(Width_balanced Histogram) :버켓에 저장할 분포도는 컬럼값의 최소치와 최대치에 대해 균등한 범위로 분할하여 각 범위별로 보유한 로우의 수를 보관 (컬럼값의 종류가 적거나 분포도의 편차가 심하지 않는 경우)

   · 높이균형 히스토그램(Height_balanced Histogram) : 총 로우 수를 버켓 		수만큼으로 나누어 각 버켓에 저장되는 값의 범위를 기록 (분포도나 컬럼값의 	편차가 심한 경우)
   => 두가지 방법은 사용자가 선택 할 수 없음. 단 버켓 수 지정 가능
 그러나 DBMS의 패키지 dbms_stats 등의 프로시져를 활용하여 자동 결정

② 통계정보의 관리를 통한 제어

1. 통계정보를 어떻게 관리할 것이냐을 고심
2. 테이블마다 통계정보를 관리할 수 없음 ⇒ 테이블을 모니터링하는 기능 이용

USER_TAB_MODIFICATION와 같은 딕셔너리 뷰를 통해 확인

1. 만약 통계테이블을 수동으로 관리 하고자 한다면 수집방법, 수집 주기 결정

⇒스크립트 작성, 작업스케줄링 할 수 있는 S/W활용,

 제공된 프로시져(GRTHER_DATABASE_STAT 등)

③ 최악의 상황이 발생할 확률의 감소

1. 적절하지 못한 인덱스 구성, 고품질의 SQL을 사용하지 못해도 대부분 최소한의 수행 속도 보장
2. 사용자의 쿼리를 재해석하여 새로운 쿼리로 변형시킴

인덱스의 길이, 최대 최소값, 리프 블록수, 가동 시스템의 I/O나 CPU 사용정보 등 - 장점

① 실행계획 예측의 곤란

1. 사용자가 예측불가능하므로 애플리케이션을 작성할 때 미리 적절한 대응하기 어렵고, 종합적인 전략을 수립하기 곤란

② 버전에 따른 변화 ③ 실행계획 제어가 곤란 -단점

- 옵티마이져의 발전 방향

 · 비용기준 옵티마이져로 발전해 나간다.
 · 단위SQL 보다는 테이블이나 인덱스 단위로 관리 

-통계정보 관리를 위한 제언

 · 통계정보의 수집과 관리를 위해 DBMS에서 제공하는 패키지들이 계속 보강되고 있음
 · 과거에는 I/O에 비중. 최근에는 CPU의 수행정도와 사용량을 추가함
 · 통계정보의 보다 간편한 수집이나 관리를 위해 DBMS_STATS 패키지 이용하면 좋음
   => 테이블이나 인덱스와 같은 데이터베이스 오브젝트에 대한 통계정보 생성을 	용이하게 해줌. 테이블이나 인덱스와 관련된 통계정보를 생성할 수 있으며, 견본 	데이터를 추출하여 통계정보를 생성하는것도 가능. 대용량 테이블이라면 모든 	데이터를 대상으로 통계정보를 수집할 수 없으므로 좋은 방법
  => 견본데이터는 5% 이하로 선정하는게 바람직
      지정 파라메터 : ESTIMATE_PERCENT 로 직접 			DBMS_STATS.AUTO_SAMPLE_SIZE 로 자동으로 판단
  => 통계정보 수집시간을 단축하기 위해 병렬로 처리 가능
     병렬등급(Degree)은 직접 지정/ 프로시져(Auto_dergree)를 이용하여 자동으로 지정

· 초기의 비용기준 옵티마이져는 특정 상황에 맞추어져 있기 때문에 문제    
 =>시스템 통계(System statistics) : 해당 시스템이 가지고 있는 성능특성을 실제로           측정함으로 보다 정확한 정보를 옵티마이져에게 제공. 시스템 통계를 생성할 때는 
    지정한 기간동안 시스템에서 실제로 일어나고 있는 활동들을 모니터링하고 분석을          해야 한다. =>관련 프로시져(DBMS_STATS.GATHER_SYSTEM.STATS)를 수행시켜        통계정보를 수집해 주어야 함

· 통계정보 교체하는 방법

 =>DBMS_STATS패지지의 ‘IMPORT_SYSTEM_STATS' 프로시져를 사용하면 간단.    이 작업은 ’DBMS_JOB'패키지를 이용하여 작업을 등록하고 적당한 시간에 자동으로     수행되도록 함

· 시스템 통계의 개선은 테이블이나 인덱스 통계정보와는 다르게 시작되는 SQL에 대해서만 새로운 통계정보로 파싱할 뿐 기존에 사용하던 SQL은 공유풀에 파싱되어 있던 것들을 그대로 사용한다. · 새로운 통계정보가 갱신되면 이전 버전은 자동으로 이력이 저장 ⇒ 원상회복에 유용

=> 딕셔너리뷰를 통해 확인 가능하며 ‘RESTORE'프로시져를 이용하여 원상 복귀

· ‘PURGE_STATS'프로시져 이용 : 통계정보의 이력을 자동으로 제거하지 않고 필요할 때 정리. 하지만 이력정보는 DBMS_STATS패키지를 사용하지 않고 ANALYZE명령을 사용하여 수집한 경우에는 관리 할 수 없음

★ 3.1.2.3 옵티마이져 목표의 선택 옵티마이져가 수행하는 최적화 작업이란 일종의 시뮬레이션이다. 시뮬에이션이란 어떠한 현상이나 사건을 모형화하여 가상으로 수행시켜 봄으로써 실제 상황에서의 결과를 예측하는 것.-옵티마이져 모드의 종류 ·초기결정 최적화(First_rows) : 일부라도 먼저 통과하는것을 목표 ·전체결과 최적화(All_rows) : 전체가 모두 수행되는 것을 목표 ·First_ROWS_n : 최적화의 목표는 커트라인으로 지정 ⇒ 완전한 비용기준 최적화 방법은 ALL_ROWS와 FIRST_ROWS_n이며, FIRST_ROWS는 비용기준과 경험적 방법을 혼합한 접근방법-옵티마이져 모드의 결정 기준 · OLTP형 시스템 중에서 ‘CHOOSE'모드가 있던 버전에 개발된 시스템으로서 기존의 실행계획을 가능한 유지하기를 원한다면 ’FIRST_ROWS' · 새로운 버전에서 개발된 시스템이라면 FIRTST_ROWS_n으로 지정 · OLTP형 업무처럼 배치처리 위주의 시스템이라면 ALL_ROWS를 지정하는 것이 좋다. · 옵티마이져 모드를 운영중에 바꾸는 것은 위험 ⇒ 옵티마이져 버전을 지정할 때 초기 설정 파라메터에 과거에 사용하던 버전을 지정 (OPTIMIZER_FEATURES_ENABLE 파라메터) / 실행계획 요약본을 생성 하여 이를 참조하게 하는 아우트라인(Outline)이용-옵티마이져 모드와 관련된 파라메터 지정· CURSOR_SHARING : SQL문장 내의 조건절에 있는 상수값들을 변수로 전환시켜 파싱· DB_FILE_MULTIBLOCK_READ_COUNT : 전체 테이블을 스캔하거나 인덱스 고속전 체스캔을 할 때 한번의 I/O에 읽어야 할 블록 수를 지정· OPTIMIZER_INDEX_CACHING : Nested Loops 조인이나 IN_List 탐침으로 수행되어 인덱스를 통해 반복해서 랜덤 액세스를 할 때 인덱스 블록들이 버퍼에 캐쉬되어 있을 확률을 나타냄·OPTIMIZER_INDEX_COST_ADJ : 비용계산을 할 eo 인덱스의 액세스 비중을 조정하는 역할을 담당하며 기본값은 100으로 되어 있음.· 옵티마이져가 규칙기준에서 비용기준으로 넘어가면서 ‘CHOOSE'모드에 대응되는 대책 필요(WHY? 통계정보를 가지고 있지 않으므로) ⇒ 동적표본화 (Dynamic Sampling) : 소량의 표본을 동적으로 추출하여 통계정보로 활용

★ ① 기존의 아우트라인에서 새로운 개별 아우트라인으로 복제

 CREATE PRIVATE OUTLINE prv_01_1 FROM outln_1;

② 아우트라인을 수정할 수 있는 툴이나 유_OUTLN_EDIT패키지에 있는 여러 프로시져를 갱신 REFRESH_PRIVATE_OUTLINE 프로시져로 다시 한번 CREATE PRIVATE OUTLINE 명령으로 리플래쉬 ③ 생성된 개별 아우트라인을 검증하기 위해 USE_PRIVATE_OUTLINES을 TRUE로 지정하고 검증④ 충분한 검증을 끝내고 공식적인 적용

  CREATE OR REPLACE OUTLINE outln_1 FROM PRIVATE prv_01_1;⑤ 적용이 되었으면 USE_PRIVATE_OUTLINES을 FALSE로 지정하여 자신의 개별      아우트라인 수행 종료

3.1.2.4 실행계획의 고정화최적의 실행계획을 적용할려고 해도 옵티마이져가 고집을 부릴 때 제어필요⇒아우트라인 ? 과거에 수립되었던 실행계획의 요약본을 저장하고 있다가 이것을 참조하여 실행계획을 수립하는 기능아우트라인은 범용적으로 관리하거나 개별적으로 관리할 수 있고 적용 금지 가능 →선별적인 적용을 가능하게 하기 위해 카테고리(Category) 지정하여 별도 관리-아우트라인의 생성과 조정 : DBMS_OUTLN과 DBMS_OUTLN_EDIT 패키지 이용 · CREATE_OUTLNE : 지정된 건을 공유커서에서 찾아 아우트라인을 생성 · CLEAR_USED : 지정한 아우트라인을 제거 · DROP_BY_CAT : 지정한 카테고리에 속한 아우트라인들을 제거 · DROP_UNUSED : SQL 파싱에 사용된 적이 없는 아우트라인 제거 · UPDATE_BY_CAT : 어떤 카테고리를 새로운 카테고리로 변경 · GENERATE_SIGNATURE : 지정한 SQL문에 대한 식별자 생성아우트라인은 모든 SQL or 특정한 것에 대해 생성 가능⇒CREATE_STORED_OUTLINES를 지정하여 생성하고 종료할 수 있다. TRUE/FALSE를 지정하거나 카테고리명을 직접 줄 수 있음. TRUE로 지정하면 ‘DEFAULT'이름의 카테고리로 아우트라인이 생성생성된 아우트라인을 사용하기 위해서는 USE_STORED_OUTLINES명령을 이용개별아우트라인을 사용하는 이유? 공식적으로 적용하기 미흡 or 조정필요시

p143쪽 그림과 설명

1. 아우트라인의 관찰

1. 옵티마이져 업그레이드 시의 적용

ⓐ규칙기반에서 전환하는 경우

① 현재의 규칙기준으로 수행되는 SQL에 대하여 특정 카테고리를 주고 아우트 라인 생성 ALTER SESSION SET CREATE_STORED_OUTLINES =category_name;

전체적으로 원한다면 ALTER SYSTEM 으로 한다.

② 수집기간을 적절하게 한다. ③ 아우트라인 생성을 종료시키려면 CREATE_STORED_OUTLINES파라메터를 'FALSE'로 지정 ④ DBMS_STATS 패키지를 이용하여 통계정보 생성 ⑤ 옵티마이져 모드를 RULE에서 CHOOSE로 변경 ⑥ ALTER SESSION 명령어를 이용하여 USE_STORED_OUTLINES 파라메터에 앞서 생성해 두었던 카페고리를 지정하여 아우트라인 적용( ALTER SYSTEM)

ⓑ옵티마이저 버전 업그레이드 위 ①~③번 수행후 애플리케이션을 수행하면서 테스트 실시

업그레이드 후 문제가 발생되면 아우트라인을 적용하여 과거 버전의 수행계획을 고수 ->향상된 옵티마이져 혜택 받지 못함->이전 버전에 대한 아우트라인을 생성할 때 분류별로 카테고리 생성

· 아우트라인 적용방법

① 원하는 대상 SQL에 대해 원하는 실행계획을 아우트라인으로 생성

ALTER OUTLINES outline_name CHANGE CATEGORY TO out_category_name;

② ALTER SESSION SET USE_STORED_OUTLINES=our_category_name을 통해 아우트라인 적용

★ 3.1.2.5. 옵티마이져의 한계 ★ 3.1.3 옵티마이져의 최적화 절차-옵티마이져의 처리 과정 ⑴ 최조에 사용자가 실행한 SQL은 데이터베이스의 데이터딕셔너리를 참조하여 파싱. 옵티마이져는 파싱된결과를 이용하여 실행계획 선택. 힌트 감안하여 일차적 계획 생 성 ⑵ 각 실행계획의 비용을 계산 ⑶ 최소의 비용을 가진 실행계획을 선택