Oracle 19c RAC 为了避免一些不必要的bug设定参数

mahan

1.关闭自适应游标共享参数

为了避免oracle 19c 遇到自适应游标共享（隐藏参数）会带来过多子游标和ORA-04031问题。将其先关闭。

[grid@node1 ~]$
[grid@node1 ~]$ sqlplus / as sysasm

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 10 17:22:43 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;

System altered.

SQL> alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;

System altered.

SQL>

2.关闭RAC隐藏参数"_gc_bypass_reader"和 "_gc_read_mostly_lock"

为了避免RAC的用户进程卡死在内部等待 "gc current request"这个数据库内部等待，大多与隐藏参数"_gc_bypass_reader"和 "_gc_read_mostly_lock"有关系，关闭参数可以减少bug并且不会影响性能，因此建议关闭。

alter system set "_gc_bypass_readers"=false;

alter system set "_gc_read_mostly_locking"=false;
3.关闭RAC全局事务支持隐藏参数

alter system set "_clusterwide_global_transactions"=false sid='*' scope=spfile;
4.关闭跨实例并行参数

alter system set parallel_force_local=true scope=spfile sid='*';
5.关闭Cardinality feedback

12c版本的Cardinality feedback可能会导致执行计划的不稳定，为了避免19c发生，建议关闭：

alter system set "_optimizer_use_feedback"=false sid ='*' scope=spfile;
6.关闭UNDO_RETENTION的自动调整

避免undo自动调整发生的一系列bug：

alter system set "_undo_autotune"=false scope=spfile sid='*';

alter system set undo_retention=10800 scope=spfile sid='*';
7.关闭DRM的相关隐藏参数

alter system set "_gc_policy_time"=0 scope=spfile sid='*';

alter system set "_gc_undo_affinity"=false sid='*';
8.关闭result_cache

alter system set result_cache_max_size=0 scope=spfile sid='*';
9.关闭初始化extent的延迟创建功能

alter system set deferred_segment_creation=false scope=spfile sid='*';