Oracle参数优化

两节点Oracle 9.2.0.7 RAC on AIX 5300-05-CSP, HACMP 5.3，每节点4CPU，7.7G内存

9.2.0.7未安装ONE-OFF PATCH

Page in/out多，IO大，CPU空闲为0，应用响应慢。

原来三个数据库(bmsdb、orcl、itsm)实例跑在一台SUN服务器上，为了系统升级，暂时把它们迁移到一对IBM p550的集群上，把bmsdb配置为两节点RAC，orcl跑在节点1上，itsm跑在节点2上。

原来的SUN服务器为8CPU、16G内存，新的IBM服务器每节点只有4CPU、7.7G内存

Oracle版本由原9.2.0.5 for Solaris变为9.2.0.7 RAC for AIX5L

原因分析

处理方法

#Bmsdb，如果orcl和itsm不迁出

*.db_cache_size=2306867200

*.java_pool_size=134217728

*.large_pool_size=67108864

*.pga_aggregate_target=2147483648

*.sga_max_size=4194304000

*.shared_pool_size=314572800

=>

*.db_cache_size=1.5G

*.java_pool_size=20M

*.large_pool_size=20M

*.pga_aggregate_target=1G

*.sga_max_size=不设，或设到2.4G

#Bmsdb，如果orcl和itsm迁出

*.db_cache_size=2306867200

*.java_pool_size=134217728

*.large_pool_size=67108864

*.pga_aggregate_target=2147483648

*.sga_max_size=4194304000

*.shared_pool_size=314572800

=>

*.db_cache_size=2G

*.java_pool_size=20M

*.large_pool_size=50M

*.pga_aggregate_target=1G

*.shared_pool_size=400M

*.sga_max_size=3.2G 或3G

#orcl

*.java_pool_size=536870912

*.large_pool_size=117440512

*.sga_max_size=1797231144

*.shared_pool_size=536870912

=>

*.java_pool_size=20M

*.large_pool_size=20M

*.sga_max_size=不设或设为1G

*.shared_pool_size=300M

#itsm

*.db_cache_size=536870912

*.java_pool_size=83886080

*.large_pool_size=117440512

*.pga_aggregate_target=251658240

*.processes=150

*.sga_max_size=1411354624

*.shared_pool_size=637534208

=>

*.db_cache_size=536870912

*.java_pool_size=20M

*.large_pool_size=20M

*.pga_aggregate_target=251658240

*.processes=150

*.sga_max_size=不设或设为1G

*.shared_pool_size=300M

p4947798_92070_AIX64-5L.zip  可以大大减轻CPU消耗

p5496862_92070_AIX64-5L.zip  操作系统为AIX 5300-05时必打的IO相关的补丁

其它可选的补丁(这些补丁与本次性能问题无关，在9.2.0.7上遇到的可能性较大，建议安装)：

p4925103_92070_AIX64-5L.zip  WRONG RESULT OCCURS BY USING MAX() FOR NULL DATA AFTER UPGRADED 9.2.0.7

p4192148_92070_AIX64-5L.zip  VIEW DEFINITION IN DBA_VIEWS SHOWS WRONG SYNTAX

p4721492_92070_AIX64-5L.zip  INDEX REBUILD GETS NO ORA-54 WHEN IT WAITS FOR A DML

注：安装补丁需要使用OPatch工具，该工具以一个补丁的形式提供，补丁号为2617419，可以从metalink上下载。

相关网络参数的当前设置：

ipqmaxlen=100

sb_max=1310720

udp_sendspace=65536

udp_recvspace=262144

rfc1323=1

tcp_sendspace=262144

tcp_recvspace=262144

以上黑体的两个参数偏小，需要调大：

no -r -o ipqmaxlen=512

no -p -o udp_recvspace=655360

虚存管理相关参数当前设置：

minperm%=20

maxperm%=80

maxclient%=80

建议调整：

vmo -p -o minperm%=10

vmo -p -o maxclient%=20

vmo -p -o maxperm%=20

oracle用户的ulimit设置：

default:

        fsize = -1

        core = 2097151

        cpu = -1

        data = 262144

        rss = 65536

        stack = 65536

        nofiles = 2000

建议增加以下行：

oracle:

        fsize = -1

        cpu = -1

        data = -1

        rss = -1

        stack = -1

在上述方案实施后，持续监控系统运行，发现消耗资源较大的SQL语句后及时进行优化，进一步减小对系统资源的消耗。

处理结果

p4947798_92070_AIX64-5L.zip  可以大大减轻CPU消耗

p5496862_92070_AIX64-5L.zip  操作系统为AIX 5300-05时必打的IO相关的补丁

其它处理

0,30 * 3,4,5 12 *  /home/oracle/statspack/statspack_snap.sh >/dev/null 2>&1

0 0 * * * /home/oracle/statspack/gather_stats.sh >/dev/null 2>&1

#!/usr/bin/ksh

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME

ORACLE_SID=bmsdb1; export ORACLE_SID

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33

LIBPATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LIBPATH

PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/usr/opt/networker/bin:$PATH; export PATH

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG

NLS_TERRITORY=; export NLS_TERRITORY

NLS_DATE_FORMAT=; export NLS_DATE_FORMAT

NLS_DATE_LANGUAGE=; export NLS_DATE_LANGUAGE

sqlplus -S /nolog <<EOF

connect / as sysdba

exec dbms_stats.gather_schema_stats(ownname=> 'BPM47' , cascade=> TRUE);

exec dbms_stats.gather_schema_stats(ownname=> 'BPM49' , cascade=> TRUE);

exec dbms_stats.gather_schema_stats(ownname=> 'EIPORADB' , cascade=> TRUE);

exit

EOF

#!/usr/bin/ksh

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/9.2.0; export ORACLE_HOME

ORACLE_SID=bmsdb1; export ORACLE_SID

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33

LIBPATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH; export LIBPATH

PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/usr/opt/networker/bin:$PATH; export PATH

NLS_LANG=AMERICAN_AMERICA.ZHS16GBK; export NLS_LANG

NLS_TERRITORY=; export NLS_TERRITORY

NLS_DATE_FORMAT=; export NLS_DATE_FORMAT

NLS_DATE_LANGUAGE=; export NLS_DATE_LANGUAGE

sqlplus -S /nolog <<EOF

connect perfstat/perfstat

exec statspack.snap(i_snap_level=>7);

exit

EOF