ORACLE_010. OPTIMIZER - Choosing Optimizer Approach and Goal

OPTIMIZER - Choosing Optimizer Approach and Goal                        

 기본적으로 CBO의 목표는 최상의 출력결과 입니다. 이는 최소한의 자원을 이용하여 쿼리를 수행하는데 필요한 행을 읽어들이는 방법을 선택하는 것 입니다. 또한 오라클은 반응시간의 최소화를 위한 최적화도 수행합니다. 반응시간이 뜻하는 바는 SQL 구문에 의해 최소의 자원을 이용하여 첫번째 행에 접근하는 방법 입니다.

 

 옵티마이저에 의해 생성된 실행계획은 옵티마이저의 목표에 따라 다양하기도 합니다. 많은 처리량을 위한 최적화는 인덱스 스켄보다 오히려 풀 테이블 스켄이거나 Nested Loop Join 보다 Sort Merge Join 일 수 있습니다. 최상의 반응시간을 위한 최적화는 Index Scan, 혹은 Nested Loop Join 입니다.

 

 예를들어, Sort-Merge 나 Nested Loop 중 하나를 사용하여 Join 구문을 수행한다고 가정해 봅시다. Sort-merge 방식은 아마도 Query 의 모든 결과를 뱉어내는데 빠를 것이고 Nested Loop 방식은 첫번째 행을 뱉어네는데 효율적일 것 입니다. 많은 처리량에 대한 향상이 목표라면 Sort merge join 을 사용하면 될 것 입니다. 반면 좀더 빠른 반응을 원한다면, 옵티마이저는 Nested loop join 을 사용하는게 좋을 것 입니다.

 

  - Oracle Reports Application 과 같은 어플리케이션이 Batch로 동작할때는 처리량에 관해 최적화를 합니다. Batch Application 에서의 처리량은 굉장히 중요한데 사용자는 어플리케이션이 끝마치는 시간이 얼만큼 필요할지만 고려하기 때문입니다. 반응시간은 좀 덜 중요한데, 어플리케이션이 수행되는 동안 개별적으로 실행된 구문의 결과를 계산하지 않기 때문입니다.

 

 - Interactive Applicatoin (대화형 어플리케이션), 예를들어 Oracle Forms App. 나 SQL*Plus 쿼리등은 반응시간에 촛점을 맞춥니다. 사용자들은 구문을 통해 첫번째 행이나 혹은 첫 몇개의 행을 눈으로 확인하기 위해 기다리기 때문입니다.

 

 옵티마이저는 다음과 같은 SQL 문의 요소로 데이터로의 접근과 목표를 최적화 합니다.

  - OPTIMIZER MODE Initialization Parameter

  - CBO Statistics in the Data Dictionary

  - Optimizer SQL Hints for Changing the CBO Goal

OPTIMIZER_MODE INITIALIZATION PARAMETER
 OPTIMIZER_MODE 초기화 파라미터는 인스턴스에서 옵티마이저가 선택할 기본 행동 방법을 설정합니다.

 

Value

Description

CHOOSE                     ALL_ROWS     FIRST_ROWS_n      FIRST_ROWS     RULE

옵티마이저는 Cost-base와 Rule-base중 하나를 선택합니다. 이는 Statistics 가 유효한지에서 결정됩니다.     - DD(Data Dictionary)가 최소 하나의 테이블에 유효한 통계를 가지고 있다면 옵티마이저는 Cost-Based 방식을 사용합니다.   - DD에 아주 소량의 통계만 있다면 그 값이 유효할때까진 Cost-base 를 사용합니다. 하지만 옵티마이저가 반드시 다른 통계 없이 이 구문을 수행하기 위한 통계를 추측합니다. 이는 차선책의 실행계획을 수행할 수 있습니다.   - DD에 통계가 전혀 전재하지 않다면 Rule-base 방식을 사용합니다.    통계의 존재 유무를 떠나서 세션의 모든 SQL문에 Cost-base 방식을 사용합니다. 많은 작업량 처리에 적합한 최적화를 실행합니다.(최소의 자원으로 모든 구문을 해결합니다.)    통계의 유무에 상관없이 Cost-base 를 선택합니다. 또한 첫 n 개의 행을 뱉어내기 위해 최소의 시간을 소요하는 최적화를 수행합니다. n값은 1,10,100 혹은 1000 입니다.    옵티마이저는 COST를 섞어 스스로 첫번째 행을 뱉어네는데 가장 좋은 계획을 결정해 냅니다.    RULE-BASE 방식으로 설정합니다. 통계자료의 유무는 고려대상이 아닙니다.

 

 옵티마이저의 파라메터는 다음과 같은 방법으로 변경할 수 있습니다.

 

  ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_1;

 

OPTIMIZER SQL HINTS FOR CHANGING THE CBO GOAL
 개별적으로 수행하는 SQL 문의 CBO 를 변경하기 위해서 다음의 힌트를 개별 SQL에 삽입할 수 있습니다.

 - FIRST_ROWS(n),   n은 어떤 양수도 가능합니다.

 - FIRST_ROWS

 - ALL-ROWS

 - CHOOSE

 - RULE

 

CBO STATISTICS IN THE DATA DICTIONARY

 CBO가 사용하는 통계정보는 DD(Data Dictionary)에 저장되어 있습니다. DBMS_STATS 패키지나 ANALYZE 구문을 통해 물리적 저장장치의 특성이나 스키마 오브젝트의 데이터 분산정도를 수집할 수 있습니다.

 

       * ORACLE은 ANALYZE 구문보다 DBMS_STATS 패키지를 이용하여 통계정보를 모으는 것을

       권장합니다. 이 패키지는 통계정보를 병렬로 수집 가능하게 해 주며 파티션된 오브젝트의 정보

       를 모을 수 있습니다. 또한 다른 여러가지 방법으로 통계정보를 모을 수 있는 수단을 제공합니

       다. 게다가 CBO 는 DBMS_STATS를 이용해 수집한 통계정보만을 사용할 것 입니다.

 

       하지만 DBMS_STATS 보다 ANALYZE를 반드시 사용해야 할 경우가 있습니다.

        - VALIDATE 혹은 LIST CHAINED ROWS 단서를 달아 사용할때

        - FREELIST BLOCKS 의 정보를 수집할 때

 보다 효과적으로 CBO를 사용하기 위해 존재하는 데이터들의 통계정보를 가지고 있어야 합니다. SKEW DATA라 불리우는 중복되는 넓은 범위의 숫자로 이루어진 컬럼이 있다면 히스토그램 정보를 모으길 권장합니다.

 

 통계정보의 결과는 CBO에게 데이터의 유일함과 분산정도의 정보를 제공합니다. 이 정보를 이용하여 CBO는 높은 수준의 정확한 실행계획의 비용을 계산할 수 있습니다. 이는 CBO가 가장 적은 비용을 소요하여 실행 계획을 선택하는 것을 가능하게 해 줍니다.

FIN
REF) Oracle Documents (Server .920)/a96533 "Introduction to the Optimizer"

 

Next => Understanding the Cost-Based Optimizer