参数名	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博客

Oracle 19C 数据库参数推荐（四）

最新文章