Oracle init.ora常用参数

When the Oracle database is started, one of the first things it needs to do is read the database initialization parameter file. The parameter file (init.ora) is created by the DBA and defines the overall instance configuration, such as how much memory should be allocated to the instance, the file locations, and internal optimization parameters.

The initialization parameters are a very important part of the Oracle database. Oracle reads the initialization parameter values from either a PFILE or SPFILE as the database is starting.

审计(Audit)用于监视用户所执行的数据库操作,审计记录可存在数据字典表,当数据库的审计是开启时,在语句执行阶段产生审计记录。由于审计表(AUD$)存放在SYSTEM表空间,因此为了不影响系统的性能,保护SYSTEM表空间,建议把AUD$移动到其他的表空间上,或者关闭审计。 参考命令:

1
alter system set audit_trail='NONE' #INSTANCE# scope=spfile;

隐含参数 _optimizer_adaptive_cursor_sharing 能控制自适应式游标共享的部分行为,由 Oracle 自适应的处理绑定变量的窥探,但这可能会触发性能问题。 Oracle 建议在非技术指导下,将其关闭掉。 参考命令:

1
alter system set "_optimizer_adaptive_cursor_sharing"=FALSE #INSTANCE#;

建议禁用自适应游标共享,将隐含参数_optimizer_extended_cursor_sharing设置为 NONE。 参考命令:

1
alter system set "_optimizer_extended_cursor_sharing"='NONE' #INSTANCE#;

建议禁用自适应游标共享,将隐含参数_optimizer_extended_cursor_sharing_rel设置为 NONE。 参考命令:

1
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE' #INSTANCE#;

为了降低集群间的数据交互,建议并行进程强制在本地实例分配,以便降低集群间的数据交互。 参考命令:

1
alter system set parallel_force_local=TRUE #INSTANCE#;

DRM (Dynamic Resource Mastering )负责将 Cache 资源 Remaster 到频繁访问这部分数据的节点上,从而提高 RAC 的性能。但是 DRM 在实际使用中存在诸多 Bug ,频繁的 DRM 会引发实例长时间 Hang 住甚至是宕机,建议关闭 DRM 。 参考命令:

1
alter system set "_gc_policy_time"=0 #INSTANCE# scope=spfile;

建议关闭集群 Undo Affinity ,降低集群 交互,避免触发相关 BUG 。 参考命令:

1
alter system set "_gc_undo_affinity"=FALSE #INSTANCE# scope=spfile;

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

延迟段创建会导致使用 Direct 方式的 Export 出来的 DMP 文件无法正常导入(文档 ID 1604983.1),建议关闭延迟段创建的特性。 参考命令:

1
alter system set deferred_segment_creation=FALSE #INSTANCE#;

隐含参数 _undo_autotune 负责 undo retention(即 undo 段的保持时间)的自动调整,若由 Oracle 自动负责 undo retention,则 Oracle 会根据事务量来占用 undo 表空间,可能会形成 undo 表空间的争用,建议将其关闭。 参考命令:

1
alter system set "_undo_autotune"=FALSE #INSTANCE#;

参数 _optimizer_null_aware_antijoin 是在 Oracle 11g 引入的新参数,它用于解决在反连接(Anti-Join)时,关联列上存在空值(NULL)或关联列无非空约束的问题。但是该参数不稳定,存在较多的 Bug,为避免触发相关 Bug,建议关闭。 参考命令:

1
alter system set "_optimizer_null_aware_antijoin"=FALSE #INSTANCE#;

并行执行的从属进程在工作时需要交换数据和信息,默认从 Shared Pool 中分配内存空间。当 _PX_use_large_pool=TRUE 时并行进程将从 Large Pool 中分配内存,减少对共享池(Shared Pool)的争用。 参考命令:

1
alter system set "_PX_use_large_pool"=TRUE scope=spfile #INSTANCE#;

建议关闭分区使用大的初始化区(Extent)。 参考命令:

1
alter system set "_partition_large_extents"=FALSE #INSTANCE#;

Oracle 默认启用 _use_adaptive_log_file_sync 参数,使得 LGWR 进程写日志的方式能自动在 post/wait 和 polling 两种方式之间进行取舍,可能会导致比较严重的写日志等待(log file sync的平均单次等待时间较高),建议关闭此功能。 参考命令:

1
alter system set "_use_adaptive_log_file_sync"=FALSE #INSTANCE#;
1
alter system set "_memory_imm_mode_without_autosga"=false sid='*' scope=spfile;

说明:11.2.0.3开始,即使是手工管理内存方式下,如果某个POOL内存吃紧,Oracle仍然可能会自动调整内存,用这个参数来关闭这种行为

1
alter system set event='28401 trace name context forever,level 1','10949 trace name context forever,level 1' sid='*' scope=spfile;

说明:这个参数主要设置2个事件: 1) 10949事件用于关闭11g的自动serial direct path read特性,避免出现过多的直接路径读,消耗过多的IO资源。 2) 28401事件用于关闭11g数据库中用户持续输入错误密码时的延迟用户验证特性,避免用户持续输入错误密码时产生大量的row cache lock或library cache lock等待,严重时使数据库完全不能登录。

1
alter system set _b_tree_bitmap_plans=false sid=* scope=spfile;

说明:对于OLTP系统,Oracle可能会将两个索引上的ACCESS PATH得到的rowid进行bitmap操作再回表,这种操作有时逻辑读很高,对于此类SQL使用复合索引才能从根本上解决问题。

1
alter system set enable_ddl_logging=true sid='*' scope=spfile;

说明:在11g里面,打开这个参数可以将ddl语句记录在alert日志中。以便于某些故障的排查。建议在OLTP类系统中使用。

1
alter system set parallel_max_servers=cpu_count 逻辑CPU  sid='*' scope=spfile;

说明:这个参数默认值与CPU相关,OLTP系统中将这个参数设置小一些,可以避免过多的并行对系统造成冲击。

1
alter system set "_optimizer_ads_use_result_cache" = FALSE scope=spfile sid='*';

说明:12c 中关闭result_cache ,容易触发latch free 等bug 。

1
alter system set "_datafile_write_errors_crash_instance"=FALSE scope=spfile sid='*';

说明:在 PDB 由于某些原因丢失数据文件后,允许 CDB 继续运行。注意: 只对 PDB 的非系统数据文件有效。

1
alter system set "_optimizer_adaptive_plans"=FALSE scope=spfile sid='*';

说明:关闭自适应执行计划。

1
alter system set "_optimizer_aggr_groupby_elim"=FALSE scope=spfile sid='*';

19567916.8 ,Wrong results when GROUP BY uses nested queries in 12.1.0.2

1
alter system set "_optimizer_reduce_groupby_key"=FALSE scope=spfile sid='*';

说明:Wrong results from OUTER JOIN with a bind variable and a GROUP BY clause

1
alter system set "_optimizer_reduce_groupby_key"=off scope=spfile sid='*';

说明:关闭COST查询转换。

1
alter system set job_queue_processes=cpu_core CPU 核数) scope=spfile sid='*';

说明:默认1000,建议调整为CPU核数。

1
alter system set optimizer_dynamic_sampling=4 scope=both sid='*';

说明:动态采样,有些场景下,可根据时间情况调大采样级别,比如调到4 。

1
alter system set optimizer_index_cost_adj=40 scope=both sid='*';

说明:优化器计算通过索引扫描访问表数据的cost 开销

某些场景下,值越大优化器越倾向于使用全表扫描。相反,值越小,优化器越倾向于使于索引扫描。可根据实际情况进行调整,比如调小到40 。

1
alter system set "_optimizer_mjc_enabled" = false scope=both sid='*';