행위

오라클 히든 파라미터

DB CAFE

목차

1 오라클 히든 파라메터 조회

-- SYS 로 Login:

--
-- List all hidden database parameters.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50
 
SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx 
AND
  substr(ksppinm,1,1) = '_'
ORDER BY ksppinm
select a.ksppinm name, b.ksppstvl value
from 
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx 
and a.ksppinm like '%bloom%';

2 추천 히든 파라메터 목록

2.1 _optim_peek_user_binds

_optim_peek_user_binds 파라미터는 바인드 변수를 사용한 SQL을 실행하여 Hard Parsing 하는 단계에서, 실제 입력한 값을 Peek하여 컬럼 히스토그램 통계 정보를 참조한 실행 계획을 생성하게 하는 파라미터임.

Peek된 Bind 값은 v$sql_bind_capture에 기록되며, 기본 900초 (15분) 마다 CAPTURE 된다.

Side Effect 로는 Peek된 Bind 값에 따라 적절한 Plan이 다른 경우에 성능 문제가 발생할 가능성이 있다.

예를 들어 날짜를 범위 조회하는 SQL 의 경우에 조회 범위가 짧을 때 생성된 실행 계획이 공유가 된다면 조회 범위가 넓을 때는 비효율 적인 경우가 생길 수 있다.

참고로, 파라미터 비활성시에도 v$sql_bind_capture 에는 저장됨.

alter system set "_optim_peek_user_binds" = false scope=both;
. Bind variable peeking(바인드변수 엿보기) 기능 활서 여부
. Bind Peeking : Bind 변수를 포함하는 SQL문이 최초로 실행될 때 Bind 변수값을 이용해서 실행계획을 수립하는 기능
=> Bind Peeking에 따른 잘못된 실행계획이 수립되는 것을 방지하기 위해 false 로 설정 권고


2.2 _optimizer_use_feedback

. 쿼리의 최초 실행 시 예측 건수와 실제 건수의 차이가 크면 opt_estimate 힌트의 형태로 쿼리가 변경되도록 함
=> 의도되지 않은 SQL 실행계획 유발의 우려가 있으므로 false로 설정 권고 

2.3 _add_col_optim_enabled

. column add 시 dictionary만 update 하고 이후 insert 시 해당 컬럼이 생성
=> 권고: wrong result scale로 인해 false(참고 : 19183343.8)

2.4 _optimizer_aggr_groupby_elim

. group-by and aggregation elimination 기능의 활성화 여부
=> 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1)

2.5 _optimizer_reduce_groupby_key

. Group by key reduction
=> 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1)

3 히든 파라메터 목록

3.1 _b_tree_bitmap_plans

enable the use of bitmap plans for tables w. only B-tree indexes FALSE 설정 Def. = TRUE

3.2 _cleanup_rollback_entries

no. of undo entries to apply per transaction cleanup 2000 설정

Def. = 100

3.3 _clusterwide_global_transactions

enable/disable clusterwide global transactions FALSE 설정 Def. = TRUE

3.4 _complex_view_merging

enable complex view merging FALSE 설정 Def. = TRUE

Complex View란 Group By나 Distinct가 있는 뷰 또는 인라인 뷰를 말하며, Complex View Merging은 이러한 View를 해체하는 개념이다.

힌트로는 Merge / No_Merge를 사용하면 된다

3.5 _datafile_write_errors_crash_inst

ance datafile write errors always crash instance FALSE 설정 Def. = TRUE

3.6 _db_mttr_advice

MTTR advisory OFF 설정 Def. = ON

3.7 _diag_daemon

start DIAG daemon FALSE 설정 Def. = TRUE

3.8 _dump_rcvr_ipc

if TRUE enables IPC dump at instance eviction time FALSE 설정 Def. = TRUE

3.9 _enable_minscn_cr

  1. enable/disable minscn optimization for CR

FALSE 설정 Def. = TRUE

3.10 _gc_maximum_bids

maximum number of bids which can be prepared 4 설정 Def. = 0

3.11 _gc_policy_time

how often to make object policy decisions in minutes 0 설정 Def. = 10

3.12 _gc_undo_affinity

if TRUE, enable dynamic undo affinity FALSE 설정 Def. = TRUE

3.13 _in_memory_undo

Make in memory undo for top level transactions FALSE 설정 Def. = TRUE

3.14 _kgl_large_heap_warning_thresh

old maximum heap size before KGL writes warnings to the alert log 33554432 설정

3.15 _library_cache_advice

whether KGL advice should be turned on FALSE 설정 Def. = TRUE

3.16 _memory_imm_mode_without_a

utosga Allow immediate mode without sga/memory target FALSE 설정 Def. = TRUE

3.17 _nlj_batching_enabled

enable batching of the RHS IO in NLJ 0 설정 Def. = 1

3.18 _nlj_batching_misses_enabled

enable exceptions for buffer cache misses 0 설정 Def. = 1

3.19 _optimizer_adaptive_cursor_sharing

Oracle 11g 부터 Bind Peeking 기능의 단점을 보완하기 위해서 바인드 변수 값에 따라

실행계획을 여러 가지로 관리할 수 있도록 하는 파라미터로 Adaptive Cursor Sharing이란 말

그대로 상황에 맞게 유연하게 Cursor를 Share하겠다는 의미이다.

이 개념을 구현하기 위해 Oracle은 Bind Sensitive Cursor, Bind Aware Cursor라는 새로운 개념을 도입함

Bind Sensitive Cursor란, 말 그대로 Bind 값에 민감한 Cursor라는 의미이다.

즉, Bind 값이 바뀌면 그것을 민감하게 처리하겠다는 의미이며

1) Equal 조건에서는 조건절에 쓰인 컬럼에 Histogram이 있고
2) Range 조건인 경우 ( Oracle은 이것을 Bind Senstive Cursor라고 부름)

Bind Aware Cursor란, Bind Sensitive Cursor에 입력된 Bind 값에 따라 실행 계획이 분화된 Cursor를 의미하는데,

Bind Aware Cursor가 생성되었다는 것은 Bind 변수의 값에 따라 Oracle이 적절한 Child Cursor를

생성했다는 것을 의미하며 이에 맞게 실행 계획을 분화(새로운 Child Cursor 생성)시킨다.

따라서 Bind Peeking에 의한 부작용이 사실상 없어지게 되지만, 조건절에 쓰인 컬럼에 Histogram이 있고,

Histogram의 분포도에 따라 실행 계획에 큰 차이가 있을 수 있다고 판단된다는 조건이 중요함

(즉, 적절한 Histogram 없이는 의미가 없음)

optimizer adaptive cursor sharing FALSE 설정 Def. = TRUE

3.20 _optimizer_adaptive_plans

SQL 수행 시 Plan 변경을 가능하도록 설정하는 파라미터(Adaptive Execution Plans)

3.21 _optimizer_mode_force

Recursive SQL(Packaged Sql)에 대해서도 현재의 Optimizer_Mode를 사용할 것인지의 여부.

3.22 _optimizer_undo_changes

이 매개 변수는 동적이 아니며 RBO 호환성은 True로 유지되어야 함(이것은 향후 릴리스에서 제거 될 예정)

3.23 _optimizer_extended_cursor_sharing

optimizer extended cursor sharing NONE 설정 Def. = UDO

3.24 _or_expand_nvl_predicate

Nvl, Decode 조건식 및 Rank 함수를 사용한 조건절을 이용하여 OR-Expansion 쿼리 변환이 가능하도록 제어하는 파라미터로

사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 OR 조건을 Full Table Scan 처리가 아닌 union all 형태로 변경 처리해주는 경우가 있는데

이를 OR-Expansion 이라 한다.

힌트)

- Use_concat : OR-Expansion을 유도하고자 할 때 사용

- No_expand : OR-Expansion을 방지하고자 할 때 사용


3.25 _ordered_nested_loop

Join Input이 Sort가 되어 있거나 Index를 이용하였으므로 Sort가 불필요 할 경우

Nested Loop Join을 선호하도록 Optimizer가 Cost 계산시 반영토록 하는 파라미터

3.26 _push_join_predicate

Non-Mergeable View에 대해서 View를 하나의 Query Block으로 처리하지 않고

Join으로 해결할 수 있는 방법이 가능한지 조사하도록 설정하는 파라미터

3.27 _push_join_union_view

Union All을 가지고 있는 Non-Mergeable View에 대해서 Join으로 해결 가능한지 조사하여

Union All을 포함하는 Non-Mergeable View 안쪽으로 조인 조건을 Pushdown 하는 기능을 활성화하는 파라미터


3.28 _shared_pool_reserved_min_alloc

shared_pool_reserved_size에서 지정된 Memory에서 Cache화 될 수 있는 최소 Size(Bytes)를 지정한다.

Default는 5K이며 Size가 작을 수록 shared_pool_reserved_size에 관리될수 있는 수를 늘리는 역할을 한다.

3.29 _sort_elimination_cost_ratio

Index를 이용한 불필요한 Sorting을 줄이기 위한 Parameter이다.

  1. This parameter fixes the FIRST_ROWS bug 780376 which optimizes for the
  2. order by case as opposed to the selective index filters in the WHERE
  3. clause. Setting it to five forces the optimizer to only eliminate the
  4. sort when it is 1/5th the cost of the index probe (or conversely the index probe is 5 times as costly as the sort).

3.30 _sqlexec_progression_cost

SQL실행을 위한 진행과정 Monitoring을 위한 대상 COST(Default=1000)의 Threshold를 지정한다.

진행 과정은 V$SESSION_LONGOPS에서 확인 가능하다.

또한 Cursor_Sharing Bug 관련 설정에 필요하다.

3.31 _table_scan_cost_plus_one

Small lookup table에 대한 Full Table Scan과 Index Scan 또는 Range Scan의 경우 Cost가 같게 나오는 경우를 방지하기 위해 설정한다.

즉 Full Table Scan에 Cost를 부과하여 Index Scan위주로 처리되도록 한다.

3.32 _trace_files_public

DBA Group이나 DB Owner User뿐만 아니라 일반 OS User에게도 Trace File에 대한 Read권한을 주어 Trace 분석등을 할 수 있도록 허용한다.

3.33 _use_column_stats_for_function

[col + 0] 또는 [col || ] 과 같은 No-Op Expression에 대한 Column 자체의 Analyze의 Statistics 값을 적용여부를 결정한다.



3.34 _PX_use_large_pool

Use Large Pool as source of PX buffers TRUE 설정 Def. = FALSE

3.35 _row_cache_cursors

number of cached cursors for row cache management 1000 설정

Def. = 20

3.36 _smm_advice_enabled

if TRUE, enable v$pga_advice FALSE 설정 Def. = TRUE

3.37 _trace_files_public

Create publicly accessible trace files TRUE 설정 Def. = FALSE

3.38 _undo_autotune

enable auto tuning of undo_retention FALSE설정 Def. = TRUE

3.39 _db_block_hash_buckets

_db_block_hash_buckets은 db_block_buffers * 2가 Default이며 Buffer Cache에 Block을 Loading하기 위해

Oracle Block Address(DBA)에 대해 내부적인 Hash Function을 적용하여 각각의 Block을 Hash Bucket에서 관리하게 된다.

이때 SGA영역인 Hash Bucket에 대한 여러 Process들이 동시에 같은 Hash Bucket에 접근하려는 것을 막기 위해

db block hash latch들을 두고 관리되며, 이 Parameter에 의해 결정된다.

단 Parameter의 값이 2의 승수가 아닐경우는 설정된 값보다 큰 2의 승수가 설정된다.

Default로는 _db_block_hash_buckets/128이나 db_block_buffers가 4,096개 이하일 경우는

db_block_buffers/4로 내부적으로 계산된다.

_db_block_hash_buckets = 262144 ▶ 초기화 파라미터 파일에서 설정

3.40 _db_block_lru_latches

7.3 이전에는 하나의 Latch가 LRU LIST를 관장하였으나 7.3 부터는 Parameter 설정값으로 여러 개의 Latch가 LRU List를 관장하여 LRU에 대한 Contention를 경감시킴. Default는 CPU_COUNT / 2이며 최대 CPU_COUNT x 2 x 3 (CPU*2 와 Buffer Pool의 수(3)) 또는 db_block_buffers/50까지 설정한다.

그러나 너무 많은 LRU Latch를 두게 되면 LRU List들이 짧게 되는 원인이 되므로 Cache Life가 짧아질 수가 있다.

그리고 LRU List는 적어도 50 Block을 가져야 한다. 주로 CPU 또는 CPU * 2로 설정하며, 각 Working Set(여러 Latch의 묶음단위)는 한 DBWn에 의해서 다루어지므로 Multiple DBWR(db_writer_processes)를 이용할 경우는 LRU Latch의 수는 적어도 DBWn의 수보다 같거나 커야 한다.

DBWn별로 효과적인 분산을 위해 Buffer Pool별 DBWn의 배수로 설정하는 것이 효과적이다.

V$LATCH의 Performance View를 확인해 3% 미만의 Misses율을 유지해야 한다.

  • Default Value

- CPU개수 / 2

3.41 _distributed_lock_timeout

Distributed Transaction 처리시 LOCK된 오브젝트에 대한 Waiting Time을 초단위로 지정.

ORA-2049 Error가 자주 발생 시 다소 증가시킬 필요가 있다.

  • Default Value

- 60 (단위, sec)

3.42 _distributed_recovery_connection_hold_time

Distributed Transaction 처리 시 Failed Transaction의 Connection을 얼마나 오랫동안 Open 상태로 유지할 것인지를 설정한다.

Reco Process가 매 30분 마다 Fail된 Distributed Transaction에 대한 정리작업을 수행하며,

이 값이 30분(30*60 = 1,800)이상일 경우는 Hold Time에 제한이 없다.

  • Default Value

- 200 (단위, sec)

3.43 _fast_full_scan_enabled

Index Fast Full Scan(Table을 Access하지 않고 Index Block들을 db_file_multiblock_count 만큼 Full Table Scan과 같이 읽어 처리하는 기능)처리에 대한 사용 여부 결정

  • Default Value

- True {True | False}

3.44 _like_with_bind_as_equality

Bind 변수를 사용한 Like 문장에서 [indexed-column like :b1]와 [index-column = :b1]의 Cost를 같도록 하는 파라미터. - This parameter forces the optimizer to treat expressions of the form - [indexed-column like :b1] similar to [index-column = :b1]. - Oracle Apps has many queries which use the LIKE operator on indexed - columns with binds. Since binds are involved, the CBO assigns - internal default selectivity estimates for the LIKE operator (5%), - and hence does not consider the index selective. - Bug 971158 has an Apps example which resulted in the introduction of - this parameter.

  • Default Value

- False {True | False}

3.45 _optim_enhance_nnull_detection

For bug1389308 : SQL AND EXPLAIN STATEMENT HANGS WITH MANY "OR" AND "AND"