3.1. SQL과 옵티마이져 (2/3) (3.1.2.3 ~ 3.1.2.5)
2010.10.06 06:22
3.1 SQL과 옵티마이져
3.1.2.3 옵티마이져 목표(Goal)의 선택
가) 옵티마이져 모드의 종류
- 초기결과 최적화(First_rows)
- 일부라도 수행되는 것을 목표
- 비용기준과 경험적 방법(Heuristic method)을 혼합한 접근 방법 사용
- 과거의 'CHOOSE' 모드와 유사 : 비용기준 접근법과 규칙 기준 방법 중에서 상황에 따라 최적화를 수행
- 예) 마라톤과 같이 일부라도 먼저 통과하는 것을 목표로 함
- 전체결과 최적화(ALL_rows)
- 전체가 모두 수행되는 것을 목표
- 비용기준 옵티마이져(CBO, Cost-Based Optimizer) 기본 모드
- 예) 팀간 달리기, 구보
- FIRST_ROWS_n
- n은 커트라인을 의미
- n 이라는 숫자에 큰 의미는 없음. 커트라인에 따라서 실행계획이 크게 변하는 경우 없기 때문
- 비용기준으로 최적화 접근
- FIRST_ROWS 확장 버전
FIRST_ROWS 와 FIRST_ROWS_n 차이점
개념적으로 별 차이 없음
옵티마이져 형태에서 차이를 보임 (FRIST_ROWS_n 비용기준 최적화, FIRST_ROWS 비용기준과 경험적 방법 혼함 )
나) 옵티마이져 모드의 결정 기준
- OLTP 환경
- 'CHOOSE' 모드에서 개발된 경우 : FIRST_ROWS
- 새로운 버전에서 개발된 시스템 : FIRST_ROWS_n
- OLAP 형 업부처럼 배치처리 위주의 시스템 : ALL_ROWS
- 옵티마이져 버전을 지정하는 초기 설정 파라메터에 과거에 사용하던 버전을 지정한다.
- 실행계획 요약본을 생성하여 이를 참조하는 아우트라인(Outline)을 이용한다.
OLTP(오엘티피. OnLine Transaction Procession)
- 여러 과정이 하나의 단위 프로세스로 실행되도록 하는 프로세스
- 데이터 입력이나 거래조회 등을 위한 트랜잭션 지향의 업무를 쉽게 관리해주는 프로그램
OLAP(오랩. OnLine Analytical Processing)
- 최종 사용자(end-user)가 다차원 정보에 직접 접근하여 대화식으로 정보를 분석하고 의사결정에 활용하는 과정
다) 옵티마이져 모드와 관련된 파라메터 지정
- CURSOR_SHARING
- 의 미 : SQL 문장 내의 상수값들을 변수로 전환하여 파싱을 할 수 있다.
- 옵 션 : FORCE, SIMILAR, EXACT
- 기본값 : EXACT(대,소문자, 공백, 비교되는 상수값이 조금만 달라도 공유하지 못함) ⇒ 부하발생 가능성 있음
- SIMILAR 사용 권유 : SQL 조건절에 상수값을 지정했더라도 옵티마이저는 변수로 인정하여 실행계획을 수립하므로 실행계획의 공유 비율을 높일 수 있음
- FORCE, SIMILAR는 실행계획의 등급 적용에 차이만 있을뿐 거의 동일하다
- DB_FILE_MULTIBLOCK_READ_COUNT
- 의 미 : 전체 테이블을 스캔하거나 인덱스 고속전체스캔을 할 때 한번의 I/O에 읽어야 할 블록 수를 지정
- 기본값 : 8
- 대용량의 배치처리를 위주로 한다면 이 값을 좀 더 증가시키면 좋다.
- OPTIMIZER_INDEX_CACHING
- 의 미 : Nested Loops 조인이나 IN-List 탐침으로 수행되어 인덱스를 통해 반복 랜덤 액세스를 할때 인덱스 블록이 버퍼에 캐쉬되어 있을 활률
- 기본값 : 0
- 랜던 액세스가 많이 발생하는 환경에서 반복 액세스가 일어나는 인덱스 들이 적은 블록을 가졌거나 특정한 범위를 주로 액세스하여 재사용률이 매우 높다면 이 값을 증기시켜 주면 좋다.
- 이 값의 수정은 비용의 조정을 의미 => 옵티마이져에 영향을 미칠 수 있음. 주의
- OPTIMIZER_INDEX_COST_ADJ
- 의 미 : 비용 계산을 할 때 인덱스의 액세스 비중을 조정하는 역할을 담당
- 기본값 : 100 (10을 주면 1/10로 계산)
- 인덱스로 액세스되어야 할 경우가 전체테이블 스캔으로 자주 일어날 경우 조정 필요
- 전체적으로 기본값으로 두고 조정시엔 특정한 세션에 선별적으로 적용하는게 바람직하다.
- 동적 표본화(Dynamic Sampling)
- 소량의 표본을 동적으로 추출하여 통계정보를 활용 => 통계정보가 없을 시 비용기준으로 작동 가능
- 적용 사례
- 통계정보가 없을 때
- 에러 등의 문제로 사용할 수 없을 때
- 너무 오래되어 더 이상 신뢰할 수 없을 때
- 적용하면 안되는 경우 (∵ SQL이 파싱될 때마다 표본을 추출해야 하므로)
- 적은 양의 데이터를 처리하는 경우
- 빈번하게 수행되는 경우
3.1.2.4 실행계획의 고정화(Stability)
- 과거에 수립되었던 실행계획의 요약본을 참조하여 실행계획을 수립하는 기능
- 실행계획 요약본은 새롭게 실행계획시 양질의 최적화를 얻을 수 있게 한다.
- 범용적(Public), 개별적(Private)으로 관리 가능
- 필요에 따라 적용(Enable)시키거나 금지(Disable) 가능. 또, 편집도 가능
- 카테고리 생성 가능 (선별적 적용 가능)
가) 아우트라인의 생성과 조정
- 아우트라인을 생성하거나 조정하는 패키지
- DBMS_OUTLN
- DBMS_OUTLN_EDIT
- 프로시저
- CREATE_OUTLINE : 지정된 건을 공유커서에서 찾아 아우트라인을 생성한다.
- CLEAR_USED : 지정한 아우트라인을 제거한다.
- DROP_BY_CAT : 지정한 카테고리에 속한 아우트라인들을 제거한다.
- DROP_UNUSED : SQL 파싱에 사용된 적이 없는 아우트라인을 제거한다.
- UPDATE_BY_CAT : 어떤 카테고리를 새로운 카테고리로 변경한다.
- GENERATE_SIGNATURE : 지정한 SQL 문에 대한 식별자를 생성한다.
나) 아우트라인의 관찰
다) 옵티마이져 업그레이드 시의 적용
- 규칙기준 옵티마이져를 비용기준으로 전환
- 특정 카테고리를 주고 아우트라인 생성을 시작한다.
- ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
- 전체적으로 적용시 : ALTER SYSTEM...
- 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
- 아우트라인 생성을 종료한다.
- CREATESTORED_OUTLINES 파라메터를 'FALSE'로 지정한다.
- DBMS_STATS 패키지를 이용하여 통계정보를 생성한다.
- 옵티마이져 모드를 변경한다.
- RULE => CHOOSE
- USE_STORED_OUTLINES 파라메터에 앞서 생성한 카테고리를 지정하여 아우트라인을 적용한다.
- 일부만 아우트라인을 적용할 경우 : ALTER SESSION...
- 전체적으로 적용할 경우 : ALTER SYSTEM...
- 옵티마이져 버전을 업그레이드 할 경우
- 특정 카테고리를 주고 아우트라인 생성을 시작한다.
- ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
- 전체적으로 적용시 : ALTER SYSTEM...
- 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
- 아우트라인 생성을 종료한다.
- 옵티마이져 버전 업그레이드
- 애플리케이션을 수행하면서 테스트 실시
3.1.2.5 옵티마이져의 한계
- 현재의 정보만으로 처리범위를 예측하기 어려움
- 많은 정보를 가지고 있더라도
- 정보는 COST, 얼마나 많은 정보를 가질수 있는가?
- 분포도 산정의 어려움
- 컬럼의 사용 연산자별로 정확한 분포도를 얻을 수 없다.
- 컬럼의 결합에 따른 정확한 분포도를 얻을 수 없다.
- 모든 결합형태에 대한 통계정보를 가질 수 없다.
- 단위 컬럼 분포도들의 계산에 의해 산정한 분포도를 신뢰할 수 없다.
종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준 높은 SQL을 구사하는 것이 필요
