[Oracle] Tunning 관련 파라미터 정리

// ※ Parameter는 Hint보다 우선권이 낮음. 

# Index 제어

   - OPTIMIZER_INDEX_CACHING (Default : 0)

      Index가 메모리에 캐싱되어 있을 확률을 의미. 80 ~ 95가 적당.

   - OPTIMIZER_INDEX_COST_ADJ (Default : 100)

      Index Cost를 조정하는 역할을 함. 즉, 값을 50으로 바꾸게 되면 Index Cost를 1/2로 감소시킴.

      그만큼 Index Scan이 선택될 확률을 높임. 5 ~ 10이 적당.

 

# CBQT(Cost Based Query Transformation) 제어

   - _OPTIMIZER_COST_BASED_TRANSFORMATION (Default : Linear)

      CBQT의 동작여부를 지정. 

   - _OPTIMIZER_PUSH_PRED_COST_BASED (Default : True)

      Cost Based Join Predicate Pushing의 동작여부를 지정.

   - _OPTIMIZER_CONNECT_BY_COST_BASED (Default : True)

      Cost Based Connect By Transformation의 동작여부를 지정.

 

# Prefetch

   - _INDEX_PREFETCH_FACTOR (Default : 100)

      이 값을 더 작게 설정할수록 옵티마이저는 인덱스 Prefetch를 더 선호하게 됨.

   - _DB_FILE_NONCONTIG_MBLOCK_READ_COUNT

     한 번에 최대 몇 개 블록을 Prefetch할지를 지정한다. 1로 지정하면 Prefetch 기능이 정지됨.

   - 테이블 Prefetch 

      + _TABLE_LOOKUP_PREFETCH_SIZE (Default : 40)

      + _TABLE_LOOKUP_PREFETCH_THRESH (Default : 2)

      + _MULTI_JOIN_KEY_TABLE_LOOKUP (Default : TRUE)

 

# Push Predicate 제어

   - _PUSH_JOIN_PREDICATE (Default : TRUE)

     뷰 Merging에 실패한 뷰 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

   - _PUSH_JOIN_UNION_VIEW (Default : TRUE)

     union all을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

   - _PUSH_JOIN_UNION_VIEW2 (Default : TRUE)

     union을 포함하는 Non-mergeable View 안쪽으로 조인 조건을 Pushdown하는 기능을 활성화함.

 

# Result Cache (11g 이상)

   - RESULT_CACHE_MODE (Default : MANUAL) [MANUAL | FORCE] 

     Result 캐시 등록 방식을 결정

     // manual : result_cache 힌트를 명시한 SQL만 등록 force : no_result_cache 힌트를 명시하지 않은 모든 SQL 등록

   - RESULT_CACHE_MAX_SIZE

     SGA 내에서 result_cache가 사용할 메모리 총량을 바이트로 지정. 0으로 설정하면 이 기능이 작동하지 않음.

   - RESULT_CACHE_MAX_RESULT (Default : 5)

     하나의 SQL 결과집합이 전체 캐시 영역에서 차지할 수 있는 최대 크기를 %로지정

   - RESULT_CACHE_REMOTE_EXPIRATION (Default : 0)

     remote 객체의 결과를 얼마동안 보관할지를 분 단위로 지정.

     remote 객체는 result 캐시에 저장하지 않도록 하려면 0으로 설정.

 

# 그 밖의 Parameter

   - OPTIMIZER_PERCENT_PARALLEL (Default : 0)

      이 값의 수치가 높을수록 parallel을 이용하여 풀 테이블 스캔으로 테이블을 액세스하려고 함.

   - OPTIMIZER_MODE

      옵티마이저 모드 설정

   - HASH_AREA_SIZE

      해쉬 조인할 때  PGA에 드라이빙할 수 있는 공간의 크기 설정

   - SORT_AREA_SIZE

      소트 머지 조인할 때 PGA에 드라이빙할 수 있는 공간의 크기 설정

   - DB_FILE_MULTIBLOCK_READ_COUNT (Default : 128) // db_block_size가 8k일 때, 최대값 128

      이 값의 수치가 높을수록 Optimizer가 Full Scan하려는 비중이 높아짐. (소트 머지 또는 해쉬 조인하려는 경향이 커짐.)

   - WORKAREA_SIZE_POLICY (Default : AUTO) [AUTO | MANUAL] 

      Optimizer가 *_AREA_SIZE를 자동으로 또는 수동으로 PGA 관리함을 설정

   - OPTIMIZER_DYNAMIC_SAMPLING (Default : 1 > 9i, 2 > 10g이상) [ 0 ~ 10 ]

      이 값의 크기가 클 수록  더 정확한 선택도와 카디널리티를 구하기위해 추가적인 Recursive SQL을 실행하며 OLTP 환경에서는 Default 값 이상으로 높이지 않음. 10g의 경우 통계정보가 없다면 '다이내믹 샘플링' 이 적용됨.

 

# Hidden Parameter

   - _b_tree_bitmap_plans (Default : TRUE) [TRUE | FALSE]

      bitmap conversion이 일어나게 할지 설정

   - _or_expand_nvl_predicate (Default : TRUE) [TRUE | FALSE]

      조건절에 nvl 사용 시 or expand가 일어나게 할지 설정

   - _direct_path_insert_features (Default : 0)

      direct path i/o가 발생하게 할지 설정

   - _nlj_batching_enabled (Default : 1)

      NL조인할 때 physical read할 때와 logical read할 때의 정렬 순서가 달라질 수 있는데 해당 파라미터를 0으로 바꿔 해결할 수 있음. (NL조인이 10g의 NL조인과 같은 방식으로 동작한다고 함.) 

      // NO_NLJ_BATCHING 힌트로도 해결할 수 있음.

   - _nlj_batching_misses_enabled (Default : 1)

      해당 파라미터를 0으로 설정 시, Batching NL조인은 그대로 유지하면서 bufferCache에 존재할 때와 그렇지 않을 때의 결과가 같을 수 있도록 함.

   - _optimizer_batch_table_access_by_rowid (Default : TRUE) (12c 이상)

      특정 테이블에 대해 배치 작업을 하고자 할 때 rowid로 접근할지 설정(?)

   - _no_or_expansion (Default : FALSE)

      or expand 사용할지 설정

   - _optimizer_unnest_scalar_sq (Default : TRUE) (12c 이상)

      TRUE로 설정 시, 스칼라 서브쿼리를 unnseting 할지 여부를 옵티마이저가 결정함.

      FALSE로 설정 시, 옵티마이저가 이 기능을 사용하지 않지만, 사용자가 unnest 힌트로 유도할 수 있음.

    - _optimizer_native_full_outer_join (Default : FORCE)

      TRUE로 설정 시, 옵티마이저가 Native Hash Full Outer 조인을 사용할 수 있게 함.

      FALSE로 설정 시, 옵티마이저가 Native Hash Full Outer 조인을 사용할 수 없게 함.

    - _complex_view_merging (Default : TRUE)

      TRUE로 설정 시, view가 merging 할 수 있게 함.

      FALSE로 설정 시, view가 merging 할 수 없게 함.

    - _optimizer_compute_index_stats (Default : TRUE)

      TRUE로 설정 시, 인덱스 신규생성이나 리빌드 시에 통계정보를 갱신하게함.

      FALSE로 설정 시, 인덱스 신규생성이나 리빌드 시에 통계정보를 갱신하지 않게함.

    - _optimizer_gather_stats_on_load (Default : TRUE) (12c 이상)

      batch load 시 테이블 통계정보 업데이트할지 여부 설정하는 파라미터

      보통 OFF 시키고 수동으로 통계정보 업데이트한다고 함. 

    - _optimizer_gather_stats_on_load_index (Default : TRUE) (12c 이상)

      batch load 시 index 통계정보 업데이트할지 여부 설정하는 파라미터

      보통 OFF 시키고 수동으로 통계정보 업데이트한다고 함. 

    - _query_execution_cache_max_size (Default : 4194304?) (10g 이상?)

      스칼라(scalar) 서브쿼리 8i, 9i 기준으로는 256개 엔트리를 캐싱, 10g이후로는 입력과 출력 값 크기,

      _query_execution_cache_max_size 파라미터에 의해 사이즈를 결정함.

    - _optimizer_group_by_place (Default : TRUE) 

 

 

출처 : https://chess-drive.tistory.com/14