"오라클 히든 파라미터"의 두 판 사이의 차이
DB CAFE
(→_add_col_optim_enabled) |
|||
1번째 줄: | 1번째 줄: | ||
− | == | + | ==오라클 히든 파라메터 조회== |
+ | -- SYS 로 Login: | ||
+ | <source lang=sql> | ||
+ | -- | ||
+ | -- 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 | ||
+ | </source> | ||
+ | <source lang=sql> | ||
+ | select a.ksppinm name, b.ksppstvl value | ||
+ | from | ||
+ | x$ksppi a, | ||
+ | x$ksppsv b | ||
+ | WHERE | ||
+ | a.indx=b.indx | ||
+ | and a.ksppinm like '%bloom%'; | ||
+ | </source> | ||
+ | |||
+ | === _optim_peek_user_binds === | ||
<source lang=sql> | <source lang=sql> | ||
alter system set "_optim_peek_user_binds" = false scope=both; | alter system set "_optim_peek_user_binds" = false scope=both; | ||
9번째 줄: | 46번째 줄: | ||
− | == _optimizer_use_feedback == | + | === _optimizer_use_feedback == = |
. 쿼리의 최초 실행 시 예측 건수와 실제 건수의 차이가 크면 opt_estimate 힌트의 형태로 쿼리가 변경되도록 함 | . 쿼리의 최초 실행 시 예측 건수와 실제 건수의 차이가 크면 opt_estimate 힌트의 형태로 쿼리가 변경되도록 함 | ||
=> 의도되지 않은 SQL 실행계획 유발의 우려가 있으므로 false로 설정 권고 | => 의도되지 않은 SQL 실행계획 유발의 우려가 있으므로 false로 설정 권고 | ||
− | == _add_col_optim_enabled== | + | === _add_col_optim_enabled== = |
. column add 시 dictionary만 update 하고 이후 insert 시 해당 컬럼이 생성 | . column add 시 dictionary만 update 하고 이후 insert 시 해당 컬럼이 생성 | ||
=> 권고: wrong result scale로 인해 false(참고 : 19183343.8) | => 권고: wrong result scale로 인해 false(참고 : 19183343.8) | ||
− | == _optimizer_aggr_groupby_elim == | + | === _optimizer_aggr_groupby_elim == = |
. group-by and aggregation elimination 기능의 활성화 여부 | . group-by and aggregation elimination 기능의 활성화 여부 | ||
=> 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1) | => 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1) | ||
− | == _optimizer_reduce_groupby_key == | + | === _optimizer_reduce_groupby_key == = |
. Group by key reduction | . Group by key reduction | ||
=> 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1) | => 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1) | ||
− | == _b_tree_bitmap_plans == | + | === _b_tree_bitmap_plans === |
enable the use of bitmap plans for tables w. only B-tree indexes | enable the use of bitmap plans for tables w. only B-tree indexes | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _cleanup_rollback_entries == | + | === _cleanup_rollback_entries === |
no. of undo entries to apply per transaction cleanup | no. of undo entries to apply per transaction cleanup | ||
2000 설정 | 2000 설정 | ||
Def. = 100 | Def. = 100 | ||
− | == _clusterwide_global_transactions == | + | === _clusterwide_global_transactions === |
enable/disable clusterwide global transactions | enable/disable clusterwide global transactions | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _complex_view_merging == | + | === _complex_view_merging === |
enable complex view merging | enable complex view merging | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _datafile_write_errors_crash_inst == | + | === _datafile_write_errors_crash_inst === |
ance datafile write errors always crash instance | ance datafile write errors always crash instance | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _db_mttr_advice == | + | === _db_mttr_advice === |
MTTR advisory | MTTR advisory | ||
OFF 설정 | OFF 설정 | ||
Def. = ON | Def. = ON | ||
− | == _diag_daemon == | + | === _diag_daemon === |
start DIAG daemon | start DIAG daemon | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _dump_rcvr_ipc == | + | === _dump_rcvr_ipc === |
if TRUE enables IPC dump at instance eviction time | if TRUE enables IPC dump at instance eviction time | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _enable_minscn_cr == | + | === _enable_minscn_cr === |
# enable/disable minscn optimization for CR | # enable/disable minscn optimization for CR | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _gc_maximum_bids == | + | === _gc_maximum_bids === |
maximum number of bids which can be prepared | maximum number of bids which can be prepared | ||
4 설정 | 4 설정 | ||
Def. = 0 | Def. = 0 | ||
− | == _gc_policy_time == | + | === _gc_policy_time === |
how often to make object policy decisions in minutes | how often to make object policy decisions in minutes | ||
0 설정 | 0 설정 | ||
Def. = 10 | Def. = 10 | ||
− | == _gc_undo_affinity == | + | === _gc_undo_affinity === |
if TRUE, enable dynamic undo affinity | if TRUE, enable dynamic undo affinity | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _in_memory_undo == | + | === _in_memory_undo === |
Make in memory undo for top level transactions | Make in memory undo for top level transactions | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _kgl_large_heap_warning_thresh == | + | === _kgl_large_heap_warning_thresh === |
old maximum heap size before KGL writes warnings to the alert log | old maximum heap size before KGL writes warnings to the alert log | ||
33554432 설정 | 33554432 설정 | ||
− | == _library_cache_advice == | + | === _library_cache_advice === |
whether KGL advice should be turned on | whether KGL advice should be turned on | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _memory_imm_mode_without_a == | + | === _memory_imm_mode_without_a === |
utosga Allow immediate mode without sga/memory target | utosga Allow immediate mode without sga/memory target | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _nlj_batching_enabled == | + | === _nlj_batching_enabled === |
enable batching of the RHS IO in NLJ | enable batching of the RHS IO in NLJ | ||
0 설정 | 0 설정 | ||
Def. = 1 | Def. = 1 | ||
− | == _nlj_batching_misses_enabled == | + | === _nlj_batching_misses_enabled === |
enable exceptions for buffer cache misses | enable exceptions for buffer cache misses | ||
0 설정 | 0 설정 | ||
Def. = 1 | Def. = 1 | ||
− | == _optimizer_adaptive_cursor_sharing == | + | === _optimizer_adaptive_cursor_sharing === |
optimizer adaptive cursor sharing | optimizer adaptive cursor sharing | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _optimizer_extended_cursor_sharing == | + | === _optimizer_extended_cursor_sharing === |
optimizer extended cursor sharing | optimizer extended cursor sharing | ||
NONE 설정 | NONE 설정 | ||
Def. = UDO | Def. = UDO | ||
− | == _PX_use_large_pool == | + | === _PX_use_large_pool === |
Use Large Pool as source of PX buffers | Use Large Pool as source of PX buffers | ||
TRUE 설정 | TRUE 설정 | ||
Def. = FALSE | Def. = FALSE | ||
− | == _row_cache_cursors == | + | === _row_cache_cursors === |
number of cached cursors for row cache management | number of cached cursors for row cache management | ||
1000 설정 | 1000 설정 | ||
Def. = 20 | Def. = 20 | ||
− | == _smm_advice_enabled == | + | === _smm_advice_enabled === |
if TRUE, enable v$pga_advice | if TRUE, enable v$pga_advice | ||
FALSE 설정 | FALSE 설정 | ||
Def. = TRUE | Def. = TRUE | ||
− | == _trace_files_public == | + | === _trace_files_public === |
Create publicly accessible trace files | Create publicly accessible trace files | ||
TRUE 설정 | TRUE 설정 | ||
Def. = FALSE | Def. = FALSE | ||
− | == _undo_autotune == | + | === _undo_autotune === |
enable auto tuning of undo_retention | enable auto tuning of undo_retention | ||
FALSE설정 | FALSE설정 | ||
152번째 줄: | 189번째 줄: | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
[[Category:oracle]] | [[Category:oracle]] |
2020년 2월 25일 (화) 18:07 판
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
- 1 오라클 히든 파라메터 조회
- 2 == _optimizer_use_feedback ==
- 3 == _add_col_optim_enabled==
- 4 == _optimizer_aggr_groupby_elim ==
- 5 == _optimizer_reduce_groupby_key ==
- 5.1 _b_tree_bitmap_plans
- 5.2 _cleanup_rollback_entries
- 5.3 _clusterwide_global_transactions
- 5.4 _complex_view_merging
- 5.5 _datafile_write_errors_crash_inst
- 5.6 _db_mttr_advice
- 5.7 _diag_daemon
- 5.8 _dump_rcvr_ipc
- 5.9 _enable_minscn_cr
- 5.10 _gc_maximum_bids
- 5.11 _gc_policy_time
- 5.12 _gc_undo_affinity
- 5.13 _in_memory_undo
- 5.14 _kgl_large_heap_warning_thresh
- 5.15 _library_cache_advice
- 5.16 _memory_imm_mode_without_a
- 5.17 _nlj_batching_enabled
- 5.18 _nlj_batching_misses_enabled
- 5.19 _optimizer_adaptive_cursor_sharing
- 5.20 _optimizer_extended_cursor_sharing
- 5.21 _PX_use_large_pool
- 5.22 _row_cache_cursors
- 5.23 _smm_advice_enabled
- 5.24 _trace_files_public
- 5.25 _undo_autotune
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%';
1.1 _optim_peek_user_binds[편집]
alter system set "_optim_peek_user_binds" = false scope=both;
. Bind variable peeking(바인드변수 엿보기) 기능 활서 여부 . Bind Peeking : Bind 변수를 포함하는 SQL문이 최초로 실행될 때 Bind 변수값을 이용해서 실행계획을 수립하는 기능 => Bind Peeking에 따른 잘못된 실행계획이 수립되는 것을 방지하기 위해 false 로 설정 권고
2 == _optimizer_use_feedback ==[편집]
. 쿼리의 최초 실행 시 예측 건수와 실제 건수의 차이가 크면 opt_estimate 힌트의 형태로 쿼리가 변경되도록 함 => 의도되지 않은 SQL 실행계획 유발의 우려가 있으므로 false로 설정 권고
3 == _add_col_optim_enabled==[편집]
. column add 시 dictionary만 update 하고 이후 insert 시 해당 컬럼이 생성 => 권고: wrong result scale로 인해 false(참고 : 19183343.8)
4 == _optimizer_aggr_groupby_elim ==[편집]
. group-by and aggregation elimination 기능의 활성화 여부 => 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1)
5 == _optimizer_reduce_groupby_key ==[편집]
. Group by key reduction => 권고 : wrong result 발생으로 false(참고 : 19567916.8, 1924440.1)
5.1 _b_tree_bitmap_plans[편집]
enable the use of bitmap plans for tables w. only B-tree indexes FALSE 설정 Def. = TRUE
5.2 _cleanup_rollback_entries[편집]
no. of undo entries to apply per transaction cleanup 2000 설정
Def. = 100
5.3 _clusterwide_global_transactions[편집]
enable/disable clusterwide global transactions FALSE 설정 Def. = TRUE
5.4 _complex_view_merging[편집]
enable complex view merging FALSE 설정 Def. = TRUE
5.5 _datafile_write_errors_crash_inst[편집]
ance datafile write errors always crash instance FALSE 설정 Def. = TRUE
5.6 _db_mttr_advice[편집]
MTTR advisory OFF 설정 Def. = ON
5.7 _diag_daemon[편집]
start DIAG daemon FALSE 설정 Def. = TRUE
5.8 _dump_rcvr_ipc[편집]
if TRUE enables IPC dump at instance eviction time FALSE 설정 Def. = TRUE
5.10 _gc_maximum_bids[편집]
maximum number of bids which can be prepared 4 설정 Def. = 0
5.11 _gc_policy_time[편집]
how often to make object policy decisions in minutes 0 설정 Def. = 10
5.12 _gc_undo_affinity[편집]
if TRUE, enable dynamic undo affinity FALSE 설정 Def. = TRUE
5.13 _in_memory_undo[편집]
Make in memory undo for top level transactions FALSE 설정 Def. = TRUE
5.14 _kgl_large_heap_warning_thresh[편집]
old maximum heap size before KGL writes warnings to the alert log 33554432 설정
5.15 _library_cache_advice[편집]
whether KGL advice should be turned on FALSE 설정 Def. = TRUE
5.16 _memory_imm_mode_without_a[편집]
utosga Allow immediate mode without sga/memory target FALSE 설정 Def. = TRUE
5.17 _nlj_batching_enabled[편집]
enable batching of the RHS IO in NLJ 0 설정 Def. = 1
5.18 _nlj_batching_misses_enabled[편집]
enable exceptions for buffer cache misses 0 설정 Def. = 1
5.19 _optimizer_adaptive_cursor_sharing[편집]
optimizer adaptive cursor sharing FALSE 설정 Def. = TRUE
5.20 _optimizer_extended_cursor_sharing[편집]
optimizer extended cursor sharing NONE 설정 Def. = UDO
5.21 _PX_use_large_pool[편집]
Use Large Pool as source of PX buffers TRUE 설정 Def. = FALSE
5.23 _smm_advice_enabled[편집]
if TRUE, enable v$pga_advice FALSE 설정 Def. = TRUE
5.24 _trace_files_public[편집]
Create publicly accessible trace files TRUE 설정 Def. = FALSE
5.25 _undo_autotune[편집]
enable auto tuning of undo_retention FALSE설정 Def. = TRUE