SQL server实例配置_新生福利 | Oracle11gR2 安装配置最佳实践(Linux)第四章

点击蓝字关注，创智助你长姿势

在 Oracle 11g 安装并建库后，需要进行一些调整，使数据库能够稳定、高效地运行。除了对数据库使用手工内存管理之外，还需要进行如下的调整(部分内容已经在前面的数据库调整部分进行了说明)。

4.1 针对 RAC 数据库的参数调整

1. Parallel

alter system set parallel_force_local=true sid='*' scope=spfile;

说明：

这个参数是 11g 的新增参数，用于将并行的 slave 进程限制在发起并行 SQL 的会话所在的节点，即避免跨节点并行产生大量的节点间数据交换和引起性能问题。

这个参数用于取代 11g 之前 instance_groups  和 parallel_instance_group 参数设置。

alter system set parallel_max_server =64 sid='*'  scope=spfile;

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

alter system set parallel_adaptive_multi_user=false;

说明：被设置为 true 时，使自适应算法可用，该算法被设计来改善使用并行的多用户环境的性能。该算法在查询开始时基于系统负载来自动减少被要求的并行度。实际的并行度基于默认、来自表或 hints 的并行度，然后除以一个缩减因数。该算法假设系统已经在单用户环境下进行了最优调整。表和 hints 用默认的并行度。

2. DRM

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

说明：这两个参数用于关闭 RAC 的 DRM(dynamic remastering) 特性，避免频繁的 DRM 使系统性能不稳定、严重的时候使数据库挂起。同时也关闭 Read-mostly Locking 新特性，这个特性目前会触发大量的 BUG ，严重时使数据库实例宕掉。

针对 11g RAC ，需要注意的是如果节点的 CPU 数量不一样，这可能导致推导出来的 lms 进程数量不一样，根据多个案例的实践来看， lms 数量不一样在高负载时会产生严重的性能问题，在此种情况下，需要手工设置 gcs_server_processes 参数，使 RAC 数据库所有节点的 lms 进程数相同。

4.2 RAC 数据库和非 RAC 数据库都适用的参数调整

1. 性能相关

alter system set "_optimizer_adaptive_cursor_sharing"=false sid='*' scope=spfile;alter system set "_optimizer_extended_cursor_sharing"=none sid='*' scope=spfile;alter system set "_optimizer_extended_cursor_sharing_rel"=none sid='*' scope=spfile;alter system set "_optimizer_use_feedback"=false  sid ='*' scope=spfile;alter system set "_optimizer_null_aware_antijoin"=false sid ='*' scope=spfile;alter system set "_optimizer_mjc_enabled"=false sid ='*' scope=spfile;

说明：这几个参数都是用于关闭 11g 的 adaptive cursor sharing 、 cardinality feedback 、 null aware antijoin 、 merge join cartesian 特性，避免出现 SQL 性能不稳定、 SQL 子游标过多、 bug 的问题。

alter system set “_b_tree_bitmap_plans”=false sid=’*’ scope=spfile;

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

alter system set "_simple_view_merging"=true scope=spfile;

关闭简单视图合并

alter system set "_cursor_obsolete_threshold" =100 scope=spfile;

Cursor Obsolescence 游标废弃是一种 SQL Cursor 游标管理方面的增强特性，该特性启用后若 parent cursor 父游标名下的子游标 child cursor 总数超过一定的数目，则该父游标 parent cursor 将被废弃，同时一个新的父游标将被开始。这样做有2点好处：

• 避免进程去扫描长长的子游标列表 child cursor list 以找到一个合适的子游标 child cursor

• 废弃的游标将在一定时间内被 age out ，其占用的内存可以被重新利用

实际在版本 10g 中就引入了该 Cursor Obsolescence 游标废弃特性，当时 child cursor 的总数阀值是 1024， 但是这个阀值在 11g 中被移除了,这导致出现一个父游标下大量 child cursor 即 high version count 的发生；由此引发了一系列的版本 11.2.0.3 之前的 cursor sharing 性能问题，主要症状是版本 11.2.0.1 和 11.2.0.2 上出现大量的 Cursor: Mutex S 和 library cache lock 等待事件。

增强补丁 Enhancement patch《 Bug 10187168 – Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold 》 就该问题引入了新的隐藏参数 _cursor_obsolete_threshold( Number of cursors per parent before obsoletion. )， 该 " _cursor_obsolete_threshold " 参数用以指定子游标总数阀值，若一个父游标的 child cursor count<=>version count 高于 " _cursor_obsolete_threshold " ，则触发 Cursor Obsolescence 游标废弃特性。

注意：

版本   11.2.0.3 中默认就有 ”_cursor_obsolete_threshold”了，而且默认值为 100 。对于版本 11.1.0.7、11.2.0.1 和 11.2.0.2 则都有该 Bug 10187168 的 bug backport 存在，从2011年5月开始就有相关针对的 one-off backport 补丁存在。但是这些 one-off backport 补丁不使用 ”_cursor_obsolete_threshold” 参数。在版本 11.1.0.7、11.2.0.1 和 11.2.0.2 上需要设置合适的 ”_cursor_features_enabled”(默认值为 2) 参数，并设置必要的 106001 event，该 event 的 level 值即是 child cursor count 的阀值，必须设置该 106001 事件后该特性才生效。但是请注意 ”_cursor_features_enabled”参数需要重启实例方能生效。而 ”_cursor_obsolete_threshold”参数和 106001 event 则可以在线启用、禁用。

2.  segment \ extent 相关

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

说明：这个参数用于关闭 11g 的段延迟创建特性，避免出现这个新特性引起的 BUG ，比如数据导入导出 BUG 、表空间删除后对应的表对象还在数据字典里面等。

alter system set "_partition_large_extents"=false sid=’*’ scope=spfile;alter system set "_index_partition_large_extents"=false sid=’*’ scope=spfile;

说明：在 11g 里面，新建分区会给一个比较大的初始 extent 大小(8M)，如果一次性建的分区很多，比如按天建的分区，则初始占用的空间会很大。

3.  Event

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;

说明：这个参数主要设置2个事件：

1)  10949 事件用于关闭 11g 的自动 serial direct path read 特性，避免出现过多的直接路径读，消耗过多的 IO 资源。

2)  28401 事件用于关闭 11g 数据库中用户持续输入错误密码时的延迟用户验证特性，避免用户持续输入错误密码时产生大量的 row cache lock或library cache lock 等待，严重时使数据库完全不能登录。

3)  60025   

4)  10943   

5)  10262

4.  resource

alter system set resource_limit=true sid='*' scope=spfile;alter system set resource_manager_plan='force:' sid='*' scope=spfile;

说明：这两个参数用于将资源管理计划强制设置为“空”，避免 Oracle 自动打开维护窗口(每晚 22:00 到早上 6:00，周末全天)的资源计划 (resource manager plan) ，使系统在维护窗口期间资源不足或触发相应的 BUG 。

5.  undo

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

说明：关闭 UNDO 表空间的自动调整功能，避免出现 UNDO 表空间利用率过高或者是 UNDO 段争用的问题。

alter system set undo_retention=10800;alter system set "_highthreshold_undoretention"=50000 scope=spfile;

6. 审计

alter system set audit_trail=none sid=’*’ scope=spfile;

说明：11g 默认打开数据库审计，为了避免审计带来的 SYSTEM 表空间的过多占用，可以关闭审计, 考虑关闭审计

7. 内存相关

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

说明：11.2.0.3版本里面，即使是手工管理内存方式下，如果某个 POOL 内存吃紧， Oracle 仍然可能会自动调整内存，用这个参数来关闭这种行为。

alter system set "_px_use_large_pool"=true  sid ='*' scope=spfile;

说明：11g 数据库中，并行会话默认使用的是 shared pool 用于并行执行时的消息缓冲区，并行过多时容易造成 shared pool 不足，使数据库报 ORA-4031 错误。将这个参数设置为 true ，使并行会话改为使用 large pool 。

8. 后台进程

alter system set "_use_adaptive_log_file_sync"=false sid=’*’ scope=spfile;

说明：11.2.0.3 版本里面，这个参数默认为 true ， LGWR会自动选择两种方法来通知其他进程 commit 已经写入：post/wait 、 polling 。前者 LGWR 负担较重，后者等待时间会过长，特别是高负载的 OLTP 系统中。在 10g 及之前的版本中是 post/wait 方式，将这个参数设置为 false 恢复到以前版本方式。

9. 安全

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

说明：从 11g 开始，用户密码区分大小写，而此前的版本则是不区分大小写，在升级时，如果这个参数保持默认值 TRUE ，可能会使一些应用由于密码不正确而连接不上。

alter profile "DEFAULT" limit PASSWORD_GRACE_TIME UNLIMITED;alter profile "DEFAULT" limit PASSWORD_LIFE_TIME UNLIMITED;alter profile "DEFAULT" limit PASSWORD_LOCK_TIME UNLIMITED;alter profile "DEFAULT" limit FAILED_LOGIN_ATTEMPTS UNLIMITED;

说明：11g 默认会将 DEFAULT 的 PROFILE 设置登录失败尝试次数( 10次 )。这样在无意或恶意的连续使用错误密码连接时，导致数据库用户被锁住，影响业务。因此需要将登录失败尝试次数设为不限制。

alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;

该参数的默认值是 FALSE ，表示用户即使被授予 “ select any table ” 权限也不允许查询 SYS 用户下的数据字典

alter system set sec_max_failed_login_attempts=100 scope=spfile;

该参数只对使用了 OCI 的特定程序生效，而使用 SQLPLUS 是无法生效 

SEC_MAX_FAILED_LOGIN_ATTEMPTS only works application uses OCI Program.SEC_MAX_FAILED_LOGIN_ATTEMPTS not work in sqlplus.

OCI Program have the following ,it wil work.
1) You need to use OCI_THREADED mode.
2) You need to set the attribute ofserver, username, password attributes in the appropriate handles:
3) You need to useOCISessionBegin to connect to the database

10. 其他

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

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

4.3 定时任务

   1) ORACLE_OCM

exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_CONFIG_JOB' );exec dbms_scheduler.disable( 'ORACLE_OCM.MGMT_STATS_CONFIG_JOB' );

说明：关闭一些不需要的维护任务，这两个属于 ORACLE_OCM 的任务不关闭，可能会在 alert 日志中报错。

   2) 自动统计信息收集

考虑是否要关闭自动统计信息收集

BEGINDBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);END;

说明：如果是需要采用手工收集统计信息策略，则关闭统计信息自动收集任务。

1. 自动收集直方图

考虑是否要关闭自动收集直方图

exec DBMS_STATS.SET_GLOBAL_PREFS( 'method_opt','FOR ALL COLUMNS SIZE 1' );

或者

exec DBMS_STATS.SET_PARAM( 'method_opt','FOR ALL COLUMNS SIZE 1' );

说明：为减少统计信息收集时间，同时为避免直方图引起的 SQL 执行计划不稳定，可以在数据库全局级关闭自方图的收集，对于部分需要收集直方图的表列，可以使用 DBMS_STATS.SET_TABLE_PREFS 过程来设置。

2.  auto space advisor

关闭 auto space advisor

BEGINDBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto space advisor',operation => NULL,window_name => NULL);END;/

说明：关闭数据库的空间 Advisor ，避免消耗过多的 IO ，还有避免出现这个任务引起的 library cache lock 。

3.  auto sql tuning

关闭 auto sql tuning

BEGINDBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);END;

说明：关闭数据库的 SQL 自动调整 Advisor ，避免消耗过多的资源。

4. 调整时间窗口

EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW','repeat_interval','freq=daily;byday=SAT;byhour=22;byminute=0;bysecond=0');EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW','repeat_interval','freq=daily;byday=SUN;byhour=22;byminute=0;bysecond=0');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SATURDAY_WINDOW', 'duration', '+000 08:00:00');EXEC DBMS_SCHEDULER.SET_ATTRIBUTE('SUNDAY_WINDOW', 'duration', '+000 08:00:00');exec dbms_scheduler.disable('WEEKNIGHT_WINDOW', TRUE);exec dbms_scheduler.disable('WEEKEND_WINDOW', TRUE);

说明：一些业务系统即使在周末，也同样处于正常的业务工作状态，比如面向公众的业务系统，在月底(虽然是周末)有批处理操作的系统，以及节假日调整的周末等，建议调整周六和周日窗口的起止时间和窗口时间长度，避免有时候周六或周日影响业务性能。

4.4 可选

1.  IO

alter system set filesystemio_options=setall scope=spfile;

使用 FILESYSTEMIO_OPTIONS 初始化参数在文件系统文件上启用或者禁用异步 I/O 或者直接 I/O 。这个参数是平台特有的，针对特定的平台最好有个默认值。

FILESYTEMIO_OPTIONS can be set to one of the following values:

• ASYNCH: enable asynchronous I/O on file system files, which has no timing requirement for transmission.

  在文件系统文件上启用异步 I/O ，在数据传送上没有计时要求。

• DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache.
  在文件系统文件上启用直接 I/O ，绕过 buffer cache 。

• SETALL: enable both asynchronous and direct I/O on file system files.
  在文件系统文件上启用异步和直接 I/O 。

• NONE: disable both asynchronous and direct I/O on file system files.
  在文件系统文件上禁用异步和直接 I/O 。

2. 内存

alter system set memory_target=0 scope=spfile;

关闭 AMM

3.   cursor

alter system set session_cached_cursors=400 scope=spfile;alter system set open_cursors=4000 scope=spfile;

4. 性能相关

alter system set "_sort_elimination_cost_ratio"=1 scope=spfile;

cost ratio for sort eimination under first_rows mode

5.  dblink

alter system set open_links=40 scope=spfile;alter system set open_links_per_instance=40 scope=spfile;

说明：open_links

6. 数据文件

alter system set db_files=2000 scope=spfile;

7. 控制文件

alter system set control_file_record_keep_time=31 scope=spfile;

8.  shared servers

alter system set shared_servers=0 scope=spfile;alter system set max_shared_servers=0 scope=spfile;

最后是一些bug的解决方法

xtts 元数据导出 hung bug

Oracle Bug 16318046 : TTS EXPORT STUCK AT INITIAL STAGES

说明：该bug影响Oracle 11.2.0.3版本，如果安装11.2.0.3环境，建议安装补丁。

Oracle 11gR2 取消 hint  /*+BYPASS_UJVC */

Oracle 11gR2版本开始取消了该 hint，因此如果数据库从9i 或者10g 升级到11gR2(12c)时，必须检查是否使用了该 hint，否则一旦升级之后，可能导致应用异常。

wmsys.VM_CONCAT 在 11gR2 中的变化

从 oracle 11g 版本开始，wmsys.VM_CONCAT 调用时返回的结果为 clob，而不再是10g 版本中返回为 varchar2，这可能导致 temp 的大量消耗，进而引发严重的性能问题。

Oracle 建议使用 listagg 函数来代替 vmsys.vm_concat 的使用。

 

创智俱乐部

微信：sziitlSA

一个让你涨姿势的社团

长按二维码关注