Oracle 19C 資料庫引數推薦(四)

jason_yehua發表於2024-01-25
引數名 19c預設值 標準參考值 引數相關參考文件 COMMAND
_adg_parselock_timeout 0 ADG設定 使用新的隱含引數可以避免adg環境mrp程式crash之後資料庫關閉資料庫緩慢問題
在下面的版本中fix
Key PSUs/Bundles to target by version:
- 11.2.0.x Apply the latest or terminal PSU/DBBP that the customer can access.
- 12.1.0.2: 12.1.0.2.190416 or newer
- 12.2.0.1: 12.2.0.1.DBRU:191015 or newer
- 18.8.0.0: 18.8.0.0.DBRU:191015 or newer
- 19.5.0.0 19.5.0.0.DBRU:191015 or newer
alter system set "_adg_parselock_timeout"=0 scope=spfile sid='*';
_ash_size 1048618 最小100M 增加_ash_size大小,避免alert日誌中頻繁出現ASH執行緊急重新整理及ASH緩衝溢位
Doc ID 1385872.1:Active Session History (ASH) Performed An Emergency Flush Messages In The Alert Log
Doc ID 1403406.1:MMON Consuming a Huge Amount of Memory Raising ORA-4030 and Causing an Instance Hang
Document 2268127.1 No Data in V$ACTIVE_SESSION_HISTORY When Size is _ASH_SIZE=1G
Document 243132.1 Analysis of Active Session History (Ash) Online and Offline
Document 1952274.1  MMNL Background Process Creates Large Trace Files Containing ASH Information
如果SGA足夠大,設定為250M
alter system set "_ash_size"=250M scope=spfile sid='*';
_clusterwide_global_transactions TRUE false 叢集範圍全域性事務是11g引入的特性,其容許XA事務在RAC中更加透明,當設定為FALSE時候,資料庫會將本地事務當作單獨的事務透過多階段提交協調處理,設定該引數不會有任何效能影響,可解決Bug 13605839 ORA-600 [ktbsdp1] ORA-600 [kghfrempty:ds] ORA-600 [kdBlkCheckError]. Corruption in Rollback with Clusterwide Global Transactions in RAC  ORA-00600: [kjuscl:!free] alter system set "_clusterwide_global_transactions"=FALSE scope=spfile sid='*';
_complex_view_merging TRUE TRUE Bug 28959493  Wrong Result When Using Hash Join With Concatenated Columns Of Char alter system set "_complex_view_merging"=TRUE scope=spfile sid='*';
_cursor_obsolete_threshold 8192 1024
alter system set "_cursor_obsolete_threshold"=1024 scope=spfile sid='*';
_datafile_write_errors_crash_instance TRUE FALSE 防止PDB中使用者datafile offline導致整個CDB crash,Multitenant best Practice and Known issues (Doc ID 1604135.1)
Bug 13745317 - Datafile offline cause all instance down in a RAC env (Doc ID 13745317.8)
alter system set "_datafile_write_errors_crash_instance"=FALSE scope=spfile sid='*';
_gc_policy_minimum 15000 15000 There is no need to set _gc_policy_minimum if DRM is disabled by setting _gc_policy_time = 0. _gc_policy_minimum is a dynamic parameter, _gc_policy_time is a static parameter and rolling restart is not supported. To disable DRM, instead of _gc_policy_time, _lm_drm_disable should be used as it's dynamic. (Doc ID 1619155.1) alter system set "_gc_policy_minimum"=15000 scope=spfile sid='*';
_log_segment_dump_parameter TRUE FALSE 控制log.xml切換時是否把非預設引數列印到alert中,建議設定成FALSE,12c Alert Log Appears To Show Unexpected Instance Restart (Doc ID 2049516.1)
可能會導致log file sync,浩成提供,徽商銀行
alter system set "_log_segment_dump_parameter"=FALSE scope=spfile sid='*';
_log_segment_dump_patch TRUE FALSE 控制log.xml切換時是否把補丁資訊列印到alert中,建議設定成FALSE,12c Alert Log Appears To Show Unexpected Instance Restart (Doc ID 2049516.1)
可能會導致log file sync,浩成提供,徽商銀行
alter system set "_log_segment_dump_patch"=FALSE scope=spfile sid='*';
_optimizer_adaptive_cursor_sharing TRUE FALSE disabling extended cursor sharing and Adaptive Cursor Sharing,避免子游標過多,產生4031 alter system set "_optimizer_adaptive_cursor_sharing"=FALSE scope=spfile sid='*';
_optimizer_ads_use_result_cache TRUE FALSE 在 12C 上看到大量的'latch free'等待 (無論是從低版本升級上來的還是新安裝的)
當檢查 AWR 報告的時候,即使設定 RESULT_CACHE_MODE 為 MANUAL,在 latch statistics 部分顯示了對"Result Cache: RC Latch"的高競爭
see MOS 2002089.1,關閉自動動態統計資訊使用結果快取的機制
alter system set "_optimizer_ads_use_result_cache"=FALSE scope=spfile sid='*';
_optimizer_extended_cursor_sharing UDO none disabling extended cursor sharing and Adaptive Cursor Sharing,避免子游標過多,產生4031 alter system set "_optimizer_extended_cursor_sharing"=none scope=spfile sid='*';
_optimizer_extended_cursor_sharing_rel SIMPLE none disabling extended cursor sharing and Adaptive Cursor Sharing,避免子游標過多,產生4031 alter system set "_optimizer_extended_cursor_sharing_rel"=none scope=spfile sid='*';
_optimizer_gather_feedback TRUE false disables the gathering of execution feedback in the optimizer alter system set "_optimizer_gather_feedback"=FALSE scope=spfile sid='*';
_optimizer_use_feedback TRUE FALSE 11.2開始Oracle有了一種新的特性
Cardinality Feedback,Cardinality
Feedback?是一個最佳化器自動最佳化的過程,最佳化器會自動修正重複執行的查詢的執行計劃。對於一些複雜的查詢,比如多欄位條件,字串範圍比較,資料SKEW?等等, 以及缺乏統計資訊,最佳化器可能不能夠產生一個完全準確的基數估計,?如丟失或統計資料不準確,或複雜的謂詞的基數估計。?cardinality feedback?就是基於這一原因而產生的。
_optimizer_use_feedback?引數預設是TRUE,即開啟Cardinality Feedback,FALSE?為關閉Cardinality feedback。
建議關閉?,目前Bug:
Bug 8521689 - SubOptimal execution plan?
on second execution of GROUP BY query?
[ID 8521689.8]?
Bug 20782505 : HIGH CPU USAGE VALIDATING?
QUERY BLOCK HINTS?
Bug 20370037 : KGLH0 GROWTH LEADING TO?
ORA-4031?
Bug 21571505 : REL11: ADAPTIVE PLAN?
FEATURE POOR PERFORMANCE IN 12.1.0.2
alter system set "_optimizer_use_feedback"=FALSE scope=spfile sid='*';
_partition_large_extents TRUE FALSE 推薦值false,取消分割槽物件的8M區大小
11.2.0.2中引入了_PARTITION_LARGE_EXTENTS 的新特性,在EXTENT SIZE AUTO ALLOCATE的表空間上若建立分割槽表,則分割槽的INITIAL EXTENT SIZE為8M
alter system set "_partition_large_extents"=FALSE scope=spfile sid='*';


來自 “ ITPUB部落格 ” ,連結:https://blog.itpub.net/31547506/viewspace-3005044/,如需轉載,請註明出處,否則將追究法律責任。

相關文章