Oracle Database Parameter Tuning

Hidden parameters

示例：如果想查看_db_block_hash_buckets的参数值。
col ksppinm for a50
col ksppstvl for a50
col ksppdesc for a50
SELECT ksppinm, ksppstvl, ksppdesc
FROM x$ksppi x, x$ksppcv y
WHERE x.indx = y.indx AND ksppinm = '_db_block_hash_buckets';

1.自适应游标共享开启可能会触发性能问题，建议关闭。
alter system set "_optimizer_adaptive_cursor_sharing"=FALSE；
alter system set "_optimizer_extended_cursor_sharing"='NONE'；
alter system set "_optimizer_extended_cursor_sharing_rel"='NONE'；

2.执行的从属进程在工作时需要交换数据和信息，默认从 Shared Pool 中分配内存空间。当 _PX_use_large_pool=TRUE 时并行进程将从 Large Pool 中分配内存，减少对共享池（Shared Pool ）的争用
alter system set "_PX_use_large_pool"=TRUE scope=spfile;

3.避免出现UNDO表空间利用率过高或者UNDO段争用的问题。
alter system set "_undo_autotune"=FALSE;

4.如果有遇到Log file sync问题可以考虑设上进行隔离规避,建议关闭此功能。
alter system set "_use_adaptive_log_file_sync"=FALSE;

5.DRM负责将 Cache 资源 Remaster 到频繁访问这部分数据的节点上，从而提高 RAC 的性能。但是 DRM 在实际使用中存在诸多 Bug ，频繁的 DRM 会引发实例长时间 Hang 住甚至是宕机，建议关闭 DRM。
alter system set "_gc_policy_time"=0;
alter system set "_gc_undo_affinity"=FALSE;

6.避免表初始化分区过大。
alter system set "_partition_large_extents"=FALSE;

7.关闭自适应执行计划。
alter system set "_optimizer_adaptive_plans"=FALSE;

8.12c 中关闭result_cache ，容易触发latch free 等bug。
alter system set "_optimizer_ads_use_result_cache" = FALSE;

9.延迟降级锁的时长（隐含参数）如果延迟降级锁的时间过低，可能导致节点之间心跳超时，导致脑裂。
alter system set "_gc_defer_time" = 3;

10.减少对LOBs字段频繁的update。
alter system set "_securefiles_concurrency_estimate" = 50;

11.当非系统表空间的数据文件出现I/O问题时，不强制将实例crash。
alter system set "_datafile_open_errors_crash_instance" = FALSE;
alter system set "_datafile_write_errors_crash_instance" = FALSE;

General parameters

1.最大数据文件数默认值为200，建议调大。
system set db_files=2000 scope=spfile;

2.限制trc文件大小，避免文件系统使用率过高。
alter system set max_dump_file_size=104857600 scope=both;

3.RAC环境，为了降低集群间的数据交互，建议并行进程强制在本地实例分配，以便降低集群间的数据交互。
alter system set parallel_force_local=TRUE scope=spfile;

4.由于审计表（AUD$ ）存放在SYSTEM 表空间，因此为了不影响系统的性能，保护SYSTEM 表空间，建议把AUD$ 移动到其他的表空间上，或者关闭审计。
alter system set audit_trail='NONE';

5.调整undo信息保存时间3小时，增加后需关注Undo表空间使用率。
alter system set undo_retention=10800 scope=both;

6.指定会话可缓存在用户私有区的游标数目建议根据实际情况调整。
alter system set session_cached_cursors=300 scope=both;

7.延迟段创建会导致使用 Direct 方式的Export 出来的 DMP 文件无法正常导入。
alter system set deferred_segment_creation=FALSE;

8.多列统计信息自动收集功能是自适应查询优化器的一部分，一般来说自动功能bug比较多，建议设置为fasle进行规避。
alter system set optimizer_adaptive_plans=FALSE scope=both;

9.默认7天，建议控制文件中的元数据保留时间设置为31天。
alter system set control_file_record_keep_time=31 scope=both;

10.数据库默认连接方式为独占模式，共享连接模式几乎不用，建议设置为0关闭。
alter system set max_shared_servers=0 scope=both;
alter system set shared_servers=0 scope=both;

System parameters

1.HugePages是Linux内核与2.6版本集成的一个特性。
这个特性基本上提供了4K页面大小(IA64为16K)的替代方案，
提供更大的页面。HugePages是一种拥有更大页面的方法，
它对于处理非常大的内存非常有用

# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`

# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
    echo "The hugepages may not be supported in the system where the script is being executed."
    exit 1
fi

# Initialize the counter
NUM_PG=0

# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
    MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
    if [ $MIN_PG -gt 0 ]; then
        NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
    fi
done

RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`

# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
    echo "***********"
    echo "** ERROR **"
    echo "***********"
    echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:

    # ipcs -m

of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
    exit 1
fi

# Finish with results
case $KERN in
    '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
           echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
    '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '4.14') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    '5.4') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Kernel version $KERN is not supported by this script (yet). Exiting." ;;
esac

# End
chmod +x hugepages_settings.sh

将上面脚本生成内容写入到/etc/sysctl.conf中,通过下面命令生效
sysctl -p

2.RAC的私网，在/etc/sysctl.conf中添加以下内容并生效，这样可以避免一些异常网络发包带来的一些问题：
net.ipv4.ipfrag_high_thresh=16777216
net.ipv4.ipfrag_low_thresh=15728640

一般要求DB服务器和心跳交换机的 mtu 值一致

3.limits.conf，memlock计算的原则：物理内存的90%（单位是KB）
vi /etc/security/limits.conf

4.修改内核参数
kernel.shmmax参数：需要根据实际物理内存计算，
此处按节点物理内存128GB计算；建议kernel.shmmax参数取值为节点物理内存的70%，
单位为字节，
即128*70%*1024*1024*1024=96207267430.4（向上取整为96207267430）。
kernel.shmall参数：根据SGA/PAGE_SIZE计算。
kernel.sem参数：如果客户单个数据库需要支持的process数超过12000，
请按照实际情况配置。

生效命令：/sbin/sysctl --system

Other parameters

1.列出回收哪些段空间可以回收，给出建议（在实际运维中，实用性非常低）
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
commit;

2.检测高负载的 SQL语句性能，并给出调优建议（在实际运维中，实用性非常低）
BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/
commit;

3.密码策略取消
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit  PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;

4.低版本客户端登录，报错：ORA-28040: No matching authentication protocol
sqlnet.ora文件中加
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

5.快速恢复区的设置
SYS@mesdb1> show parameter recover;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 0
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
remote_recovery_file_dest            string

6.快速恢复区设置
是一个默认放置所有备份恢复操作有关文件的地方，
包括：控制文件在线镜像、在线重做日志、归档日志、外来归档日志、控制文件镜像复制、数据文件镜像复制、RMAN备份片和闪回日志。
如果启用的快速恢复区，它就成了RMAN备份默认的去处，无论是RMAN备份片、镜像复制、还是控制文件自动备份，
只要没有在RMAN配置或则backup命令中指明路径就全部进入快速恢复区。

SQL> alter system set db_recovery_file_dest_size=40G scope=spfile;
SQL> alter system set db_recovery_file_dest='+ARCH' scope=spfile;