목차

3.1.2. 옵티마이져의 형태

가. 규칙기반 옵티마이져 (RBO : Rule_based Optimizer)

  1. 인덱스 구조나 사용 연산자에 부여된 순위로써 최적경로 결정 
    ① 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. 처리방법들에 대한 비용을 미리 산정해보고 그 중 가장 적은 비용이 들어가는 처리방법 선택
  3. 통계정보의 형태와 종류는 DBMS 제품이나 버전에 따라 차이

⇒통계정보는 테이블의 로우 수와 블록 수, 블록당 평균 로우수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL값의 수, 클러스터링 팩터, 인덱스의 길이, 최대 최소값, 리프 블록수, 가동 시스템의 I/O나 CPU 사용정보 등 

① 현실을 감안한 최적화
  - 위의 예시에서 B인덱스 조건이 A조건보다 적은 범위를 처리하고 있다면 옵티마이져는 B인덱스를 처리주관 
    조건으로 선택
  
  - 분포도 : 통계정보에서 가장 주용한 정보
    분포도가 좋다-> 컬럼값의 종류가 많다 ->조건을 만족하는 처리범위가 좁다는 것을 의미하므로 일의 양을 결정하는 
    가장 중요한 요소
  
  - 컬럼의 모든 값들에 대해 분포도를 가지면 정확하겠지만 불가능  
     =>컬럼값의 범위별로 분포도 보유하는 방법
  
  - 컬럼값에 저장할 분포도의 종류는 버켓(Bucket)의 개수에 따라 결정
    
   · 넓이균형 히스토그램(Width_balanced Histogram) :버켓에 저장할 분포도는 컬럼값의 최소치와 최대치에 대해 
     균등한 범위로 분할하여 각 범위별로 보유한 로우의 수를 보관(컬럼값의 종류가 적거나 분포도의 편차가 심하지 않는 경우)
   · 높이균형 히스토그램(Height_balanced Histogram) : 총 로우 수를 버켓 수만큼으로 나누어 각 버켓에 저장되는
      값의 범위를 기록 (분포도나 컬럼값의 편차가 심한 경우)
     
     => 두가지 방법은 사용자가 선택 할 수 없음. 단 버켓 수 지정 가능
	 그러나 DBMS의 패키지 dbms_stats 등의 프로시져를 활용하여 자동 결정DELETEME

② 통계정보의 관리를 통한 제어
  - 통계정보를 어떻게 관리할 것이냐을 고심

  - 테이블마다 통계정보를 관리할 수 없음 
       => 테이블을 모니터링하는 기능 이용
          USER_TAB_MODIFICATION와 같은 딕셔너리 뷰를 통해 확인

  - 만약 통계테이블을 수동으로 관리 하고자 한다면 수집방법, 수집 주기 결정
       =>스크립트 작성, 작업스케줄링 할 수 있는 S/W활용,
	 제공된 프로시져(GRTHER_DATABASE_STAT 등)


③ 최악의 상황이 발생할 확률의 감소
  - 적절하지 못한 인덱스 구성, 고품질의 SQL을 사용하지 못해도 대부분 최소한의 수행 속도 보장
  
  - 사용자의 쿼리를 재해석하여 새로운 쿼리로 변형시킴

3.1.2.3 옵티마이져 목표의 선택

  1. 옵티마이져가 수행하는 최적화 작업이란 일종의 시뮬레이션
    • 시뮬에이션이란 어떠한 현상이나 사건을 모형화하여 가상으로 수행시켜 봄으로써 실제 상황에서의 결과를 예측하는 것

가. 옵티마이져 모드의 종류

  1. 초기결정 최적화(First_rows) : 일부라도 먼저 통과하는것을 목표
  2. 전체결과 최적화(All_rows) : 전체가 모두 수행되는 것을 목표
  3. First_ROWS_n : 최적화의 목표는 커트라인으로 지정

나. 옵티마이져 모드의 결정 기준

OLTP [online transaction processing]
  1. 정의 : 온라인 업무의 처리 형태의 하나이다.
    터미널에서 받은 메시지를 따라 호스트가 처리를 하고, 그 결과를 다시 터미널에 되돌려주는 방법.
  2. 네트워크상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 조회하는 등의 단위 작업을 처리하는 방식을 말한다.
  3. 주로 신용카드 조회 업무나 자동 현금 지급 등 금융 전산 관련 부문에서 많이 발생하기 때문에 ‘온라인 거래처리’라고도 한다.
  4. 이 방식의 특징은 기존 컴퓨터 통신에서 이용해 온라인 방식과 달리 다수의 이용자가 거의 동시에 이용할 수 있도록
    송수신 자료를 트랜잭션(데이터 파일의 내용에 영향을 미치는 거래 ·입출고 ·저장 등의 단위 행위) 단위로 압축,
    비어 있는 공간을 다른 사용자들이 함께 쓸 수 있도록 한 점이다. </code>

다.옵티마이져 모드와 관련된 파라메터 지정

3.1.2.4 실행계획의 고정화

  1. 최적의 실행계획을 적용할려고 해도 옵티마이져가 고집을 부릴 때 제어필요⇒아우트라인
  2. 아우트라인 ? 과거에 수립되었던 실행계획의 요약본을 저장하고 있다가 이것을 참조하여 실행계획을 수립하는 기능
    완전한 실행계획이 아니라 동일하게 재현할 수 있는 최소한의 참조정보
  3. 아우트라인은 범용적으로 관리하거나 개별적으로 관리할 수 있고 적용 금지 가능
    • 선별적인 적용을 가능하게 하기 위해 카테고리(Category) 지정하여 별도 관리

가. 아우트라인의 생성과 조정

  1. DBMS_OUTLN과 DBMS_OUTLN_EDIT 패키지 이용
    • CREATE_OUTLNE : 지정된 건을 공유커서에서 찾아 아우트라인을 생성
    • CLEAR_USED : 지정한 아우트라인을 제거
    • DROP_BY_CAT : 지정한 카테고리에 속한 아우트라인들을 제거
    • DROP_UNUSED : SQL 파싱에 사용된 적이 없는 아우트라인 제거
    • UPDATE_BY_CAT : 어떤 카테고리를 새로운 카테고리로 변경
    • GENERATE_SIGNATURE : 지정한 SQL문에 대한 식별자 생성아우트라인은 모든 SQL or 특정한 것에 대해 생성 가능
  2. CREATE_STORED_OUTLINES를 지정하여 생성하고 종료
  3. TRUE/FALSE를 지정하거나 카테고리명을 직접 줄 수 있음
    • TRUE로 지정하면 ‘DEFAULT'이름의 카테고리로 아우트라인이 생성생성된 아우트라인을 사용하기 위해서는 USE_STORED_OUTLINES명령을 이용
    • 개별아우트라인을 사용하는 이유? 공식적으로 적용하기 미흡 or 조정필요시
      • 조정필요시 작업과정
      1. 기존의 아우트라인에서 새로운 개별 아우트라인으로 복제
        CREATE PRIVATE OUTLINE prv_01_1 FROM outln_1;
      2. 아우트라인을 수정할 수 있는 툴이나 DBMS_OUTLN_EDIT패키지에 있는 여러 프로시져를 갱신
        REFRESH_PRIVATE_OUTLINE 프로시져로 다시 한번 CREATE PRIVATE OUTLINE 명령으로 리플래쉬
      3. 생성된 개별 아우트라인을 검증하기 위해 USE_PRIVATE_OUTLINES을 TRUE로 지정하고 검증
      4. 충분한 검증을 끝내고 공식적인 적용
        CREATE OR REPLACE OUTLINE outln_1 FROM PRIVATE prv_01_1;
      5. 적용이 되었으면 USE_PRIVATE_OUTLINES을 FALSE로 지정하여 자신의 개별 아우트라인 수행 종료</code>

나. 아우트라인의 관찰

  1. 생성된 아우트라인은 딕셔너리 테이블에 저장
  2. 뷰를 통해 내용 확인 가능
EX> select empno, ename, job, loc 
    from emp e, dept d 
    where e.deptno = d.deptno and e.empno = 7856
    order by loc

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

  1. 규칙기반에서 전환하는 경우
    1. 현재의 규칙기준으로 수행되는 SQL에 대하여 특정 카테고리를 주고 아우트 라인 생성
      ALTER SESSION SET CREATE_STORED_OUTLINES =category_name;
      전체적으로 원한다면 ALTER SYSTEM 으로 한다.
    2. 수집기간을 적절하게 한다.
    3. 아우트라인 생성을 종료시키려면 CREATE_STORED_OUTLINES파라메터를 'FALSE'로 지정
    4. DBMS_STATS 패키지를 이용하여 통계정보 생성
    5. 옵티마이져 모드를 RULE에서 CHOOSE로 변경
    6. ALTER SESSION 명령어를 이용하여 USE_STORED_OUTLINES 파라메터에 앞서 생성해 두었던 카페고리를 지정하여 아우트라인 적용( ALTER SYSTEM)
  2. 옵티마이저 버전 업그레이드
    • 위 ①~③번 수행후 애플리케이션을 수행하면서 테스트 실시
    • 업그레이드 후 문제가 발생되면 아우트라인을 적용하여 과거 버전의 수행계획을 고수 →향상된 옵티마이져 혜택 받지 못함→이전 버전에 대한 아우트라인을 생성할 때 분류별로 카테고리 생성
  3. 아우트라인 적용방법
    1. 원하는 대상 SQL에 대해 원하는 실행계획을 아우트라인으로 생성 
      ALTER OUTLINES outline_name CHANGE CATEGORY TO out_category_name;
    2. ALTER SESSION SET USE_STORED_OUTLINES=our_category_name을 통해 아우트라인 적용

3.1.2.5.옵티마이져의 한계

  1. 현재의 정보로 미래를 예측
  2. 정확한 분포도 산정이 어려움
    • 컬럼의 사용 연산자별로 정확한 분포도
    • 컬럼의 결합에 따는 정확한 분포도
    • 인덱스 컬럼의 결합 순서 및 사용 유무에 따른 분포도
  3. 현실에서 사용되는 대부분의 조건은 변수 형태

다양한 사용 형태를 만족 할 수 있도록 종합적이고 전략적으로 데이터 구조와 인덱스 설계하고 수준 높은 SQL 구사 필요