一次数据库性能调优报告

1 10.*********（单实列）存在的问题：

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绑定变量的数据类型代入有问题

[@more@]

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

转载于:http://blog.itpub.net/24487368/viewspace-1037323/