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)

 

 아우트라인(Outline)

• 과거에 수립되었던 실행계획의 요약본을 참조하여 실행계획을 수립하는 기능
• 실행계획 요약본은 새롭게 실행계획시 양질의 최적화를 얻을 수 있게 한다.
• 범용적(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 문에 대한 식별자를 생성한다.

 

나) 아우트라인의 관찰

 

 

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

•  규칙기준 옵티마이져를 비용기준으로 전환
1. 특정 카테고리를 주고 아우트라인 생성을 시작한다.
• ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
• 전체적으로 적용시 : ALTER SYSTEM...
2. 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
3. 아우트라인 생성을 종료한다.
• CREATESTORED_OUTLINES 파라메터를 'FALSE'로 지정한다.
4. DBMS_STATS 패키지를 이용하여 통계정보를 생성한다.
5. 옵티마이져 모드를 변경한다.
• RULE => CHOOSE
6. USE_STORED_OUTLINES 파라메터에 앞서 생성한 카테고리를 지정하여 아우트라인을 적용한다.
• 일부만 아우트라인을 적용할 경우 : ALTER SESSION...
• 전체적으로 적용할 경우 : ALTER SYSTEM...

 

• 옵티마이져 버전을 업그레이드 할 경우
1. 특정 카테고리를 주고 아우트라인 생성을 시작한다.
• ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;
• 전체적으로 적용시 : ALTER SYSTEM...
2. 대부분의 SQL에 대해 아우트라인이 생성되도록 오랜 기간 수집한다.
3. 아우트라인 생성을 종료한다.
4. 옵티마이져 버전 업그레이드
5. 애플리케이션을 수행하면서 테스트 실시

 

3.1.2.5 옵티마이져의 한계

• 현재의 정보만으로 처리범위를 예측하기 어려움
• 많은 정보를 가지고 있더라도
• 정보는 COST, 얼마나 많은 정보를 가질수 있는가?

 

• 분포도 산정의 어려움
• 컬럼의 사용 연산자별로 정확한 분포도를 얻을 수 없다.
• 컬럼의 결합에 따른 정확한 분포도를 얻을 수 없다.
• 모든 결합형태에 대한 통계정보를 가질 수 없다.
• 단위 컬럼 분포도들의 계산에 의해 산정한 분포도를 신뢰할 수 없다.

 

종합적이고 전략적인 차원에서 적절한 인덱스나 클러스터링을 결정하고, 수준 높은 SQL을 구사하는 것이 필요