【参数优化】11gR2安装后参数优化

以下为11gR2安装完成后需要优化的参数。
前序文章：1.【安装】CentOS7.7下图形化安装Oracle11gR2，2.【补丁】Oracle11gR2补丁更新（PSU）

一、参数优化

#如果指定为TRUE， 并行操作只会在当前instance里面并行，而不会垮多节点
alter system set parallel_force_local=true         scope=spfile;

#这两个参数用于关闭RAC的DRM（dynamic remastering）特性，避免频繁的DRM使系统性能不稳定、严重的时候使数据库挂起。同时也关闭Read-mostly Locking新特性，这个特性目前会触发大量的BUG，严重时使数据库实例宕掉。
alter system set "_gc_policy_time"=0               scope=spfile;

alter system set "_gc_undo_affinity"=false         scope=spfile;

#关闭AMM
alter system set memory_target=0                   scope=spfile;

#SGA使用虚拟内存的最大大小
alter system set sga_max_size=${SGA}m              scope=spfile;

#ASMM，自动分配Shared Pool、Buffer Cache等内存区大小
alter system set sga_target=${SGA}m                scope=spfile;

#此参数用来指定所有session总计可以使用最大PGA内存
alter system set pga_aggregate_target=${PGA}m      scope=spfile;

#在启用ASMM的情况下，设置streams_pool_size此值用作流池的最小值，datapump使用流功能在进程之间进行通信
alter system set streams_pool_size=256m            scope=spfile;

# 关闭审计
Alter system set audit_trail=none                  scope=spfile;

#指定（以秒为单位）撤消保留的下阈值
alter system set undo_retention=10800              scope=spfile;

#指定要缓存的会话游标的数量
alter system set session_cached_cursors=200        scope=spfile;

#指定可以为此数据库打开的最大数据库文件数
alter system set db_files=4000                     scope=spfile;

#指定允许同时运行的最大共享服务器进程数
alter system set max_shared_servers=0              scope=spfile;

#指定客户端在与服务器进程的连接上可以进行的身份验证尝试次数。在指定次数的失败尝试之后，服务器进程将自动断开连接。
alter system set sec_max_failed_login_attempts=100 scope=spfile;

#段延迟创建，设为true，那么数据库中空表就不会立即分配extent，即不占数据空间
alter system set deferred_segment_creation=false   scope=spfile;

#指定实例的并行执行进程和并行恢复进程的最大数量
alter system set parallel_max_servers=32           scope=spfile;

#在数据库中启用或禁用密码区分大小写
alter system set sec_case_sensitive_logon=false    scope=spfile;

#设置最大连接进程数
alter system set processes=4000                    scope=spfile;

#指定会话一次可以具有的最大打开游标数
alter system set open_cursors=3000                 scope=spfile;

#指定一个会话中与远程数据库的并发打开连接的最大数目
alter system set open_links = 40                   scope=spfile;

#指定每个数据库实例的全局可迁移打开连接的最大数量
alter system set open_links_per_instance =40       scope=spfile;

#通过V$DB_CACHE_ADVICE性能视图启用或禁用用于收集具有不同缓存大小的行为的统计信息收集
alter system set db_cache_advice=off               scope=spfile;

#指定结果缓存可以使用的最大SGA内存量（以字节为单位）,设置0为禁用
alter system set result_cache_max_size=0           scope=spfile;

#启用DDL操作写到alert日志，12c有单独日志写到log/ddl下
alter system set enable_ddl_logging=true scope=spfile;

#指定是否将LOB文件视为SecureFiles，设置always尝试将所有LOB创建为SecureFiles LOB
alter system set db_securefile=always scope=spfile;

#仅作为一个trigger来获得ITL中的invalid SCN ，可以长期使用，没有任何影响,文档 ID 1523623.1
alter system set "_ktb_debug_flags"=8 scope=both;

#Exadata相关参数，用来控制是否启用Offloading，默认值为true，也就是默认是启用Offloading功能
alter system set cell_offload_processing=false scope=spfile;

#表示了将要自动扩展空间占整个表空间的百分比
alter system set "_kttext_warning"=1 scope=spfile;

#12c 中关闭result_cache ，容易触发latch free等bug 
alter system set "_optimizer_ads_use_result_cache" = FALSE  scope=spfile;

#对于OLTP 系统，Oracle 可能会将两个索引上的ACCESS PATH 得到的rowid 进行bitmap 操作再回表，这种操作有时逻辑读很高，对于此类SQL 使用复合索引才能从根本上解决问题
alter system set "_b_tree_bitmap_plans"=false           scope=spfile;

#用于确定服务器在将频繁使用的块写入磁盘之前要等待的时间长度 (以 1/1000 秒为单位)，以减少进程对热块的争用，默认为0
alter system set "_gc_defer_time"=3                     scope=spfile;

#大于100G SGA的情况下，需要设置以下两个参数。参考文档：Best Practices and Recommendations for RAC databases with SGA size over 100GB (文档 ID 1619155.1) 
alter system set "_lm_tickets"=5000                     scope=spfile;
alter system set "_lm_sync_timeout"=1200                        scope=spfile;

#基数反馈（Cardinality Feedback ）是 Oracle 11.2 中引入的关于 SQL 性能优化的新特性，该特性主要针对统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况，Cardinality 基数的计算直接影响到后续的 JOIN COST 等重要的成本计算评估，造成 CBO 选择不当的执行计划。但是该参数存在不稳定因素，可能会带来执行效率的问题，建议关闭优化器反馈。
alter system set "_optimizer_use_feedback"=false        scope=spfile;

alter system set "_high_priority_processes"='LMS*|VKTM|LGWR' scope=spfile;
alter system set "_undo_autotune"=false                 scope=both ;

#布隆过滤器(Bloom Filter)算法在Oracle Database 10gR2中被引入到Oracle数据库中,
布隆过滤能够使用极低的存储空间，存储海量数据的映射，从而可以提供快速的过滤机制。
11R2会遇到一个BLOOM过滤器导致的BUG 9124206和BUG 8361126，出现ORA-00060 ORA-10387错误
alter system set "_bloom_filter_enabled"=FALSE          scope=spfile;

#该参数指定回滚时每次回滚的ENTRIES个数，默认为100，设置成4000加快回滚速度
alter system set "_cleanup_rollback_entries"=4000      scope=spfile;

#配置_px_use_large_pool为true,并行查询也会使用大池内存
alter system set "_px_use_large_pool"=true              scope=spfile;

#关闭自适应游标共享(Adaptive Cursor Sharing： ACS)，避免bug
alter system set "_optimizer_extended_cursor_sharing_rel"=NONE  scope=spfile;
alter system set "_optimizer_extended_cursor_sharing"=NONE      scope=spfile;
alter system set "_optimizer_adaptive_cursor_sharing"=false     scope=spfile;

#绑定变量窥探，一般在oltp环境基本都关闭此参数,防止因为绑定变量的原因导致的执行计划不稳定
alter system set "_optim_peek_user_binds"=FALSE                 scope=spfile;
alter system set "_optimizer_cartesian_enabled"=FALSE           scope=spfile;

#https://blog.csdn.net/cotchte0421/article/details/100378033
alter system set "_sort_elimination_cost_ratio"=1               scope=spfile;

#11.2.0.2开始的一个新特性,为了减少extent数量,提高分区表性能,而引入的一个参数,默认为true，即分区表的每个extent为8M
alter system set "_index_partition_large_extents"=FALSE         scope=spfile;

#http://blog.itpub.net/27243841/viewspace-1147107/
alter system set "_memory_imm_mode_without_autosga"=FALSE       scope=spfile;

#https://www.sohu.com/a/152628320_505827
alter system set "_clusterwide_global_transactions"=FALSE       scope=spfile;

alter system set "_part_access_version_by_number"=FALSE         scope=spfile;
alter system set "_partition_large_extents"=FALSE                scope=spfile;
alter system set "_use_adaptive_log_file_sync"=FALSE            scope=spfile; 
alter system set "_ksmg_granule_size"=134217728                 scope=spfile;
alter system set "_external_scn_logging_threshold_seconds"=3600 scope=spfile;
alter system set "_external_scn_rejection_threshold_hours"=24 scope=spfile;
alter system set "_datafile_write_errors_crash_instance"=false  scope=spfile;
alter system set event='28401 trace name context forever,level 1','60025 trace name context forever','10943 trace name context forever,level 2097152','10949 trace name context forever,level 1','10262 trace name context forever, level 90000' scope=spfile;

#强制记录日志，即对数据库中的所有操作都产生日志信息，并将该信息写入到联机重做日志文件
alter database force logging;

#密码过期时间的宽限时间，此期间内登陆会有提示,设置为无限制
alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED;
#默认密码180天过期，改为不过期
alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;
#指定登陆尝试失败次数账户锁定后自动解锁时间,设置为无限制
alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;
#超过失败重试次数，账户会被锁定,设置为无限制
alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

#禁用OCM的jobs
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');
exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

#禁用auto space advisor、sql tuning advisor
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto space advisor',
operation => NULL,
window_name => NULL);
END;
/
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

二、总结

看过一些参数优化的最佳实践，还是各有区别，部分参数在当前知识体系下还不理解，后续深入学习后再补充修正