调优案例

1 10.81.1.6（单实列）存在的问题：

1. 业务高峰期响应慢，处理步骤如下所示，

1.1 创建如下索引：

create index idx_ZZJGDM_20100617_sh on TAX_SI_ZZJGDM_20100617(sh,aac001) online tablespace IND_DATAEXCHANGE;

create index idx_F_KBH1_aac001 on F_KBH1(aac001,ekc092) online tablespace IND_DATAEXCHANGE;

create index idx_iaa2_aac001 on iaa2(aac001) online tablespace DATAEXCHANGE;

create index idx_SBDI_BANKFEE_sblsh on SBDI_BANKFEE(sblsh,sbbm) online tablespace IND_SIBUSI;

create index idx_ic13_aad124 on ic13(aad124,aac001,aic142) online tablespace DATAEXCHANGE;

create index idx_kca1_aac001_aae140 on kca1(aac001,aae140,aae109,eae003) online tablespace DATAEXCHANGE;

create index idx_userlog_aab001 on sbds_userlog(aab001,functionid,eae024) online tablespace SIBUSI;

create index idx_sbds_userlog_flag on sbds_userlog(userflag) online tablespace SIBUSI;

1.2 发现oem消耗较大的数据库资源，关闭oem的job程序

exec emd_maintenance.remove_em_dbms_jobs;（要在sysman下才能关闭的一个进程）

1.3 select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(flags, 1024) = 1024语句

查metalink：

Oracle Server - Enterprise Edition - Version: 10.1 to 10.2；This problem can occur on any platform. Symptoms；After the introduction of temporary tablespace groups following query using a lot of CPU and executed many times，Cause：

Temp tablespace group uses a recursive query on ts$ to get the minimum extent size among its temp tablespaces.

Solution1> Use single temp tablespace as user's temp tablespace.

Solution2> Apply patch for the bug 5455880

1.4 占用资源比较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

-------------- ------------ ------------ ------ -------- --------- -------------

1,926,819 478 4,031.0 12.5 10.35 13.58 6hpkyfr0cmm4c Module: repsc2.exe

SELECT "CUI" ,"REP_SERVER_NAME" ,"REP_MODE" ,"REP_BEGIN_TIME" ,"REP_END_TIME" ,

"REPID" ,"QUERYID" ,"PARAM_STR" ,"OP_ID" ,"OP_NAME" ,"DEP_ID" ,"DEP_NAME" ,

"EXEC_STATE" ,"EXEC_DESC" FROM "REPSERVERLOG" ORDER BY "CUI" ASC

该SQL执行较频率，且无任何过滤条件。

1.5 占用资源比较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

-------------- ------------ ------------ ------ -------- --------- -------------

2,203,673 60 36,727.9 18.2 44.73 44.95 3q1h43vdhaxz9 Module: sxdb2

SELECT '330699' YYCSDM, '3' KLB, '1.00' GFBB, '91560000023306003301005D' JGDM, T

O_CHAR(B.EKC184, 'yyyymmdd') FKRQ, TO_CHAR(B.EKC185, 'yyyymmdd') KYXQ, B.EKC183KH, A.AAC002 SFZH, A.AAC003 XM, A.AAC004 XB, C.AAC005 MZ, SUBSTR(A.AAC002, 1, 6) CSD, TO_CHAR(A.AAC006, 'yyyymmdd') CSRQ, C.AAE005 LXFS, C.AAE006 JTZZ, A.EAC109

ZJLX, C.AAB027 YHBM, A.AAC001 GRBM, B.EKC101 KZT, A.AAB001 DWBM

, C.EAE033 SJHM FROM AC01 A, KCK1 B, ACA1 C WHERE A.AAC001 = B.A

AC001 AND A.AAC001 = C.AAC001 AND B.EKC101 IN ('0', '1')

表kck1，ac01，aca1无有效过滤条件，3个表全扫描，每1分钟运行1次。

1.6 占用资源较多，需要程序开发人员介入的SQL：

Buffer Gets Executions per Exec %Total Time (s) Time (s) SQL Id

-------------- ------------ ------------ ------ -------- --------- -------------

2,003,652 9 222,628.0 7.1 11.28 18.31 8yn7yvp8gdz41

UPDATE ACC8 SET AAC001=:B2 WHERE AAC001=:B1

1,259,027 9 139,891.9 4.4 8.18 33.94 6jrq2qfz2c2bb

INSERT INTO ACC8MERGE SELECT * FROM ACC8 WHERE AAC001=:B1

这两个SQL单次执行逻辑读很高，怀疑:B1这个SQL绑定变量的数据类型代入有问题

1.7 占用资源较多，需要程序开发人员介入的SQL：

DELETE REPSERVERLOG

WHERE TO_CHAR(REP_BEGIN_TIME,'YYYYMMDD')<=TO_CHAR(SYSDATE-2,'YYYYMMDD')

建议改成：

DELETE REPSERVERLOG WHERE REP_BEGIN_TIME <= trunc(sysdate) – 2;

2. 实例抛ORA-04031异常（需重启）

用show parameter shared_pool_size查看shared_pool_size参数，发现shared_pool_size只有400M，该参数在10gR2及以上版本通常设置为800M左右，考虑到该实例物理内存只有8G，将shared_pool_size参数设置为800M；其它参数调整如下所示：

SQL> alter system set sga_target=0 scope=both;

SQL> alter system set db_cache_size=3000m scope=both;

SQL> alter system set shared_pool_size=800m scope=both;

SQL> alter system set max_dump_file_size=2097152 scope=both;

199.120.75.18（RAC）存在的问题：

存在的问题：创建表空间等待时间长。

经检查：没发现不合理的设置与异常；用脚本测试了数据库的小事务性能。

测试代码：

/*

create tab le test0818 as select * from user_objects;

alter table test0818 add rn number;

update test0818 set rn = rownum;

create index idx_test_0818_rn on test_0717(rn);

*/

declare

v_cnt number;

begin

for i in 1..1000 loop

v_cnt := floor(dbms_random.value(1,2286));

insert into test0818

select * from test0818 where rn = v_cnt;

if mod(i,1000) = 0 then

commit;

end if;

end loop;

commit;

end;

测试结果：

1 测试9分钟共写入6000+万条记录，平均每秒写入11万条记录

2 平均每秒产生redo 15M以上

3 用powermt watch观察到光纤链路IO/sec达到10000+

说明：从数据库的角度来看，不存在写入性能差的问题。

analyze table test0809 compute statistics;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20813025/viewspace-1037118/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20813025/viewspace-1037118/