文档联动
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) |