adminschool.net wiki

- 실행계획의 중요성? 액세스 효율에 가장 결정적인 영향을 미침

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

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

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

가. 규칙기반 옵티마이져 (RBO : Rule_based Optimizer) 
  -  인덱스 구조나 사용 연산자에 부여된 순위로써 최적경로 결정
    ① 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
   ⑮ 전체 테이블 스캔

• 단점
  1. 통계정보를 전혀 가지지 않음
  2. 경우에 따라 비현실적인 처리경로 수립

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

• 장점
  1. 수립될 처리경로 예측가능
  2. 사용자가 원하는 처리경로로 유도하기가 용이
  3. 일반적인 보편타당성이 있음(신뢰성)

나. 비용기준 옵티마이져(Cost_based Optimizer) 
  - 관계형 데이터베이스가 추구하는 이상형
   - 처리방법들에 대한 비용을 미리 산정해보고 그 중 가장 적은 비용이 들어가는 처리방법 선택
   - 통계정보의 형태와 종류는 DBMS 제품이나 버전에 따라 차이
     =>통계정보는 테이블의 로우 수와 블록 수, 블록당 평균 로우수, 로우의 평균길이, 컬럼별 상수값의 종류, 분포도, 컬럼 내 NULL값의 수, 클러스터링 팩터, 인덱스의 길이, 최대 최소값, 리프 블록수, 가동 시스템의 I/O나 CPU 사용정보 등 

• 장점

1. 현실을 감안한 최적화
   1. 위의 예시에서 B인덱스 조건이 A조건보다 적은 범위를 처리하고 있다면 옵티마이져는 B인덱스를 처리주관 조건으로 선택
   2. 분포도 : 통계정보에서 가장 주용한 정보
      • 분포도가 좋다→ 컬럼값의 종류가 많다 →조건을 만족하는 처리범위가 좁다는 것을 의미하므로 일의 양을 결정하는 가장 중요한 요소
   3. 컬럼의 모든 값들에 대해 분포도를 가지면 정확하겠지만 불가능 ⇒컬럼값의 범위별로 분포도 보유하는 방법
   4. 컬럼값에 저장할 분포도의 종류는 버켓(Bucker)의 개수에 따라 결정
      • 넓이균형 히스토그램(Width_balanced Histogram) :버켓에 저장할 분포도는 컬럼값의 최소치와 최대치에 대해 균등한 범위로 분할하여 각 범위별로 보유한 로우의 수를 보관 (컬럼값의 종류가 적거나 분포도의 편차가 심하지 않는 경우)
      • 높이균형 히스토그램(Height_balanced Histogram) : 총 로우 수를 버켓수만큼으로 나누어 각 버켓에 저장되는 값의 범위를 기록 (분포도나 컬럼값의 편차가 심한 경우) ⇒ 두가지 방법은 사용자가 선택 할 수 없음. 단 버켓 수 지정 가능. 그러나 DBMS의 패키지 dbms_stats 등의 프로시져를 활용하여 자동 결정
2. 통계정보의 관리를 통한 제어
   1. 통계정보를 어떻게 관리할 것이냐을 고심
   2. 테이블마다 통계정보를 관리할 수 없음 ⇒ 테이블을 모니터링하는 기능 이용 ( USER_TAB_MODIFICATION와 같은 딕셔너리 뷰를 통해 확인)
   3. 만약 통계테이블을 수동으로 관리 하고자 한다면 수집방법, 수집 주기 결정 ⇒스크립트 작성, 작업스케줄링 할 수 있는 S/W활용, 제공된 프로시져(GRTHER_DATABASE_STAT 등)
3. 최악의 상황이 발생할 확률의 감소
   1. 적절하지 못한 인덱스 구성, 고품질의 SQL을 사용하지 못해도 대부분 최소한의 수행 속도 보장
   2. 사용자의 쿼리를 재해석하여 새로운 쿼리로 변형시킴

• 단점

1. 실행계획 예측의 곤란
2. 사용자가 예측불가능하므로 애플리케이션을 작성할 때 미리 적절한 대응하기 어렵고, 종합적인 전략을 수립하기 곤란
3. 버전에 따른 변화
4. 실행계획 제어가 곤란

다. 옵티마이져의 발전 방향
 - 비용기준 옵티마이져로 발전해 나간다.
 - 단위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은 데이터베이스의 데이터딕셔너리를 참조하여 파싱. 옵티마이져는 파싱된결과를 이용하여 실행계획 선택. 힌트 감안하여 일차적 계획 생 성 ⑵ 각 실행계획의 비용을 계산 ⑶ 최소의 비용을 가진 실행계획을 선택