文档联动
Oracle 12c数据库参数最佳实践
https://blog.51cto.com/u_13482808/7438509
Oracle 11.2数据库参数最佳实践
https://blog.51cto.com/u_13482808/7462240
Oracle 18c数据库参数最佳实践
https://blog.51cto.com/u_13482808/7479370
1）非隐藏参数
参数类别 |
参数名称 |
建议值 |
设置命令 |
说明 |
静态 |
control_files |
3 |
alter system set controlfiles='FIILE1','FILE2','FILE3' scope=spfile sid='*'; |
建议控制⽂件设置3个 |
动态 |
recyclebin |
on |
alter system set recyclebin=on sid='*'; |
默认为on ，推荐开启回收站，并且建议定期清理回收站。 |
动态 |
undo_retention |
5400 |
alter system set undo_retention=5400 sid='*'; |
undo数据默认保留期为900秒，建议设置为5400秒或更⼤(在_undo_autotune设置为false的情况下) |
静态 |
processes |
2000 |
alter system set processes=2000 scope=spfile sid='*'; |
最⼤连接数默认值为150 ，建议调⼤(具体参数值要根据业务进程数来设置) |
静态 |
db_files |
2000 |
alter system set db_files=2000 scope=spfile sid='*'; |
最⼤数据⽂件数默认值为200，建议调⼤ |
静态 |
audit_trail |
none |
alter system set audit_trail=none scope=spfile sid='*'; |
默认值为DB,建议设置为none，关闭数据库层⾯的审计 |
动态 |
parallel_force_local |
TRUE |
alter system set parallel_force_local=true sid='*'; |
RAC环境，禁⽤跨节点并⾏， Bug 31247532， ORA-12805 FOLLOWING "IPC RECEIVER DUMP DETECTED" IN 12.2.0.1 |
动态 |
max_dump_file_size |
1024M |
alter system set max_dump_file_size='1024M' sid='*'; |
限制trc文件大小，避免文件系统使用率过高。 |
动态 |
control_file_record_keep_time |
31 |
alter system set control_file_record_keep_time=31 sid='*'; |
默认7天，建议控制⽂件中的元数据保留时间设置为31天 |
动态 |
parallel_max_servers |
1/2 CPU |
alter system set parallel_max_servers=24sid='*'; |
最大并行数，建议修改为1/2 cpu数 |
动态 |
parallel_min_servers |
0 |
alter system set parallel_min_servers=0 sid'*'; |
|
动态 |
open_cursors |
1000 |
alter system set open_cursors=1000 sid='*'; |
默认值为300，open_cursors参数用于指定⼀个会话能同时打开游标的最⼤数⽬ |
动态 |
session_cached_cursors |
100 |
alter system set session_cached_cursors=100 sid='*'; |
每个session最多可以缓存多少个关掉的cursor |
动态 |
deferred_segment_creation |
FALSE |
alter system set deferred_segment_creation=false sid='*'; |
禁用延时段创建，属于11g新特性，会导致exp无法导出空表，建议关闭。 |
静态 |
pga_aggregate_limit |
0 |
alter system set pga_aggregate_limit=0 scope=spfile sid='*'; |
设置为0 ，表示实例所使⽤PGA内存总量没有限制。 |
静态 |
pga_aggregate_target |
/ |
alter system set pga_aggregate_target=20g scope=spfile sid='*'; |
设置pga⼤⼩ |
静态 |
sga_max_size |
/ |
alter system set sga_max_size=80g scope=spfile sid='*'; |
使⽤⾃动共享内存管理(ASMM) |
静态 |
sga_target |
/ |
alter system set sga_target=80g scope=spfile sid='*'; |
|
静态 |
memory_target |
0 |
alter system set memory_target=0 scope=spfile sid='*'; |
设置为0 ，禁⽤ AMM |
静态 |
memory_max_target |
0 |
alter system set memory_max_target=0 scope=spfile sid='*'; |
|
静态 |
result_cache_max_size |
0 |
alter system set result_cache_max_size=0 scope=spfile sid='*'; |
禁⽤结果缓存,避免出现性能问题。 |
动态 |
optimizer_adaptive_reporting_only |
TRUE |
alter system set optimizer_adaptive_reporting_only=true sid='*' ; |
Bug 33558058 - Wrong Results With Invisible Column On Primary Key (Doc ID 33558058.8) |
动态 |
optimizer_adaptive_plans |
FALSE |
alter system set optimizer_adaptive_plans=false scope=spfile sid='*'; |
Bug 32505009 - ORA-7445: exception encountered: core dump [kkqcscpopnwithmap()+6328] (Doc ID 32505009.8)，Bug 27000158 - adaptive plan takes more time to execute than equivalent non-adaptive plan (Doc ID 27000158.8) |
动态 |
query_rewrite_enabled |
FALSE |
alter system set query_rewrite_enabled=false sid='*' ; |
Bug 28164480 - Query Rewrite fails with ORA-07445: exception encountered: core dump [evaopn3()+149] (Doc ID 28164480.8) |
2）隐藏参数
参数类别 |
参数名称 |
建议值 |
设置命令 |
说明 |
动态 |
_drop_stat_segment |
1 |
alter system set "_drop_stat_segment"=1 sid='*'; |
bug 30186706: ORA-600 [kpdbSwitchPreRestore: Txn] Crash RAC Instances |
静态 |
_PX_use_large_pool |
FALSE |
alter system set "_PX_use_large_pool"=TRUE sid='*' scope=spfile ; |
在11G+版本中，建议启用大池（Large Pool）分配并行进程内存，减少对共享池（Shared Pool）的争用。 |
动态 |
_b_tree_bitmap_plans |
FALSE |
alter system set "_b_tree_bitmap_plans"=false sid='*'; |
将b-tree索引转换成位图索引,Bug 28776431 - A Query With Large OR List Can Crash the Instance (Doc ID 28776431.8) |
动态 |
_bloom_filter_enabled |
FALSE |
alter system set "_bloom_filter_enabled"=FALSE sid='*'; |
禁用布隆过滤 |
动态 |
_cleanup_rollback_entries |
5000 |
alter system set "_cleanup_rollback_entries"=5000 sid='*' ; |
默认值100该参数指定回滚时每次回滚的ENTRIES个数，设置成20000加快回滚速度。文档 ID414242.1Database Hangs Because SMON Is Taking 100% CPU Doing Transaction Recovery |
静态 |
_cursor_obsolete_threshold |
1024 |
alter system set "_cursor_obsolete_threshold" =1024 sid='*' scope=spfile; |
High Version Counts For SQL Statements (>1024) Post Upgrade To 12.2 and Above Causing Database Slow Performance (文档 ID 2431353.1) |
静态 |
_datafile_open_errors_crash_instance |
FALSE |
alter system set "_datafile_open_errors_crash_instance"=false sid='*' scope=spfile; |
当非系统表空间的数据文件出现I/O问题时，不强制将实例crash。 |
静态 |
_datafile_write_errors_crash_instance |
FALSE |
alter system set "_datafile_write_errors_crash_instance"=false sid='*' scope=spfile; |
|
静态 |
_gc_policy_time |
0 |
alter system set "_gc_policy_time"=0 sid='*' scope=spfile; |
关闭RAC的DRM（dynamic remastering）特性，避免频繁的DRM使系统性能不稳定、严重的时候使数据库挂起。同时也关闭Read-mostly Locking新特性，这个特性目前会触发大量的BUG，严重时使数据库实例宕掉 |
静态 |
_gc_undo_affinity |
FALSE |
alter system set "_gc_undo_affinity"=FALSE sid='*' scope=spfile; |
|
动态 |
_optimizer_cartesian_enabled |
FALSE |
alter system set "_optimizer_cartesian_enabled"=FALSE sid='*'; |
关闭merge join connection |
动态 |
_optimizer_enhanced_join_elimination |
FALSE |
alter system set "_optimizer_enhanced_join_elimination"=false sid='*'; |
Bug 29182901 Query with Outer Join Returned a Wrong Result due to Join Elimination Bug 27966472 wrong results on query with multiple left outer join |
动态 |
_optimizer_extended_cursor_sharing |
none |
alter system set "_optimizer_extended_cursor_sharing"='NONE' sid='*'; |
禁用自适应游标共享，避免出现cursor sharing导致的子游标过多的问题 |
动态 |
_optimizer_extended_cursor_sharing_rel |
none |
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE' sid='*'; |
|
动态 |
_optimizer_unnest_scalar_sq |
FALSE |
alter system set "_optimizer_unnest_scalar_sq"=false sid='*'; |
After applying 12.1.0.2.160119 DBPSU or Later, ORA-600[kkogfp:no next table] or ORA-7445 [kkoiqb] is Seen in Alert Log (文档 ID 2304569.1) BUG:22338374 - ORA-07445 [KKOIQB] - EXADATA 12.1.0.2.10 WHEN USING DBLINKTO REMOTE DB 11.2.0.3 |
动态 |
_partition_large_extents |
FALSE |
alter system set "_partition_large_extents"=FALSE sid='*'; |
避免表初始化分区过大 |
动态 |
_rollback_segment_count |
2000 |
alter system set "_rollback_segment_count"=2000 sid='*'; |
默认值0，保持有更多满足系统的online状态的undo，根据系统的特征设置（enq: US – contention等）副作用：startup会变慢文档 ID 1951680.1IF: Undo Wait Event - Enq: US - contention |
静态 |
_shared_pool_reserved_pct |
20 |
alter system set "_shared_pool_reserved_pct"=20 sid='*' scope=spfile; |
适当增加shared pool保留比率 |
动态 |
_use_adaptive_log_file_sync |
FALSE |
alter system set "_use_adaptive_log_file_sync"=FALSE sid='*' ; |
这个参数其实也没有明确的标准，如果有遇到Log file sync问题可以考虑设上进行隔离规避 |
动态 |
_optimizer_cbqt_or_expansion |
off |
alter system set "_optimizer_cbqt_or_expansion"=off sid='*' ; |
Bug 30446820 ORA-7445 [KKEISJ()+275] during SQL execution(Doc ID 30446820.8),Bug 28498976 - dml performed on table with self join does not perform or expansion (Doc ID 28498976.8)， Bug 28201419 - performance degradation in query of some dictionary table (Doc ID 28201419.8) Bug 27421733 - ORA-600 [qctcte1] with fix 26025681 present (Doc ID 27421733.8)， Bug 31570161 - ORE: Bypassed - Unsupported Structure With Patch : 29450812 Installed (Doc ID 31570161.8),Bug 28820669 - Wrong results with Cost-Based OR Expansion and multi-column inlist subqueries (Doc ID 28820669.8)，Bug 27773602 - netsuite query failed with ORA-7445 [qctdeterminecoll()+638] (Doc ID 27773602.8) |
动态 |
_column_tracking_level |
默认值-4 |
alter system set "_column_tracking_level"=17 sid='*' ; |
在18.1与18.8版本中，Bug 31387123 DB Active Standby Apply Rate Is Slow Due to Wait on enq: IV - contention(Doc ID 31387123.8)，修改该参数为默认参数值减去4 |
动态 |
_widetab_comp_enabled |
FALSE |
alter system set "_widetab_comp_enabled"=FALSE sid='*' ; |
Bug 31786282 : V$LOGMNR_CONTENTS HAS INCORRECT SQL_REDO FOR COMPRESSED TABLE WITH CHAINED ROWS |
动态 |
_aq_stop_backgrounds |
FALSE |
alter system set "_aq_stop_backgrounds"=FALSE sid='*' scope=spfile; |
Bug 28024793 - Queue is not dequeued and got stuck (Doc ID 28024793.8),如命中该bug，首先检查该参数，如隐含参数值为TRUE则设置为FALSE，如为FALSE并依然触发该BUG，则先设置为TRUE,再次设置为FALSE |
静态 |
_widetab_comp_enabled |
FALSE |
alter system set "_gc_fast_index_split_wait"=0 sid='*' scope=spfile; |
Bug 28023081 - Sessions Hang Due to Waits 'gc current split'/ 'gc index operation' (Doc ID 28023081.8)，此BUG只在集群环境下触发 |
动态 |
_fix_control |
"25367727:0" |
alter system set "_fix_control"="25367727:0" sid='*' ; |
Bug 33558058 - Wrong Results With Invisible Column On Primary Key (Doc ID 33558058.8) Bug 27321179 - Improvement to fix of bug 20355502 (Doc ID 27321179.8),涉及到这个参数的BUG非常多，不一一列举，当前建议值为最新RU补丁修改值 |
动态 |
_widetab_comp_enabled |
FALSE |
alter system set "_optimizer_unnest_scalar_sq"=false sid='*' ; |
Bug 24841671 - ansi sqls running slow in 12c (Doc ID 24841671.8)，涉及版本18.1/18.4 |
动态 |
_rowsets_enabled |
FALSE |
alter system set "_rowsets_enabled" = false sid='*' ; |
Bug 27060859 - Wrong Results Using CASE Clause (Doc ID 27060859.8),Bug 29048728 - Wrong results or dump using on conversion error option (Doc ID 29048728.8),Bug 28530171 - Wrong Results With Order By And Parallel When Rowsets Disabled (Doc ID 28530171.8)，Bug 27740424 - Unexpected warning ORA-24347 is returned to Pro*C and OCI clients (Doc ID 27740424.8) |
动态 |
_optimizer_nlj_hj_adaptive_join |
FALSE |
alter system set "_optimizer_nlj_hj_adaptive_join" = false sid='*' ; |
Bug 27231051 - nvl() fails with ORA-06502 error having the pl/sql callback function when the adaptive join is enabled (Doc ID 27231051.8) |
动态 |
_simple_view_merging |
FALSE |
alter system set "_simple_view_merging" = false sid='*' ; |
Bug 27256000 - ORA-00600 [qkainitgrpfns.3] Error on Query with Grouping, Rollup and Functional Index (Doc ID 27256000.8)ORA-600 [rwoirw: check ret val] on ctas operation (Doc ID 25686739.8) |
动态 |
_optimizer_unnest_scalar_sq |
FALSE |
alter system set "_optimizer_unnest_scalar_sq" = FALSE sid='*' ; |
Bug 27392968 - Wrong Results with Scalar Subquery Unnesting and Grouping Sets (Doc ID 27392968.8) |
动态 |
_optimizer_order_by_elimination_enabled |
FALSE |
alter system set "_optimizer_order_by_elimination_enabled" = false sid='*' ; |
Bug 28715655 - Wrong Results with ORDER BY Elimination and MODEL Clause (Doc ID 28715655.8) |
动态 |
_px_object_sampling_enabled |
FALSE |
alter system set "_px_object_sampling_enabled" = FALSE sid='*' ; |
Bug 28305607 - Wrong Results May Be Observed on a PX Query When Using OBJECT SAMPLING With An ORDER BY Clause (Doc ID 28305607.8) |
动态 |
_optimizer_join_factorization |
FALSE |
alter system set "_optimizer_join_factorization"=false sid='*' ; |
Bug 27505229 - Wrong Results With Union All And Join Factoring (Doc ID 27505229.8) |
动态 |
_optimizer_free_transformation_heap |
FALSE |
alter system set "_optimizer_free_transformation_heap"=false sid='*' ; |
Bug 22580355 - ORA-7445: kkqjpdinsfronodup()+136 using adi versioning for free sga memory (Doc ID 22580355.8) |
动态 |
_eliminate_common_subexpr |
FALSE |
alter system set "_eliminate_common_subexpr"=false sid='*' ; |
Bug 28073470 - Query With Duplicate Predicates And Ansi Outer Join Returns Wrong Result (Doc ID 28073470.8) |
动态 |
_optimizer_generate_transitive_pred |
FALSE |
alter system set "_optimizer_generate_transitive_pred"=false sid='*' ; |
|
动态 |
_optimizer_outer_to_anti_enabled |
FALSE |
alter system set "_optimizer_outer_to_anti_enabled"=false sid='*' ; |
Bug 28622202 - Select With Outer Join On View Return Wrong Result (Doc ID 28622202.8) |
动态 |
_optimizer_null_accepting_semijoin |
FALSE |
alter system set "_optimizer_null_accepting_semijoin"=false sid='*' ; |
Bug 27587905 - wrong results on query with subquery using or exists (Doc ID 27587905.8),Bug 28373960 - Wrong results from query using OR EXISTS clause (Doc ID 28373960.8) |
动态 |
_replace_virtual_columns |
FALSE |
alter system set "_replace_virtual_columns"=false sid='*' ; |
Bug 28965084 - 12.2 function based index not used by optimizer (Doc ID 28965084.8) |
动态 |
_index_join_enabled |
FALSE |
alter system set "_index_join_enabled"=false sid='*' ; |
Bug 26422277 - Getting ORA-600: [kkqtutlremoveselitem_int: colvop != selopn] on ansi sql (Doc ID 26422277.8) |
静态 |
_db_link_sources_tracking |
FALSE |
alter system set "_db_link_sources_tracking"=false sid='*' scope=spfile ; |
Bug 27544973 - Reco Process Fails with ORA-603 ORA-3106 in oracle 12.2 (Doc ID 27544973.8) |