10.81.***.***(单实列)存在的问题:
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.***.***(RAC)存在的问题:
存在的问题:创建表空间等待时间长。
经检查:没发现不合理的设置与异常;用脚本测试了数据库的小事务性能。
测试代码:
/*
create tab le test_0717 as select * from user_objects;
alter table test_0717 add rn number;
update test_0717 set rn = rownum;
create index idx_test_0717_rn on test_0717(rn);
*/
declare
v_cnt number;
begin
for i in 1..1000000 loop
v_cnt := floor(dbms_random.value(1,2286));
insert into test_0717
select * from test_0717 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+
说明:从数据库的角度来看,不存在写入性能差的问题。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20813025/viewspace-668651/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20813025/viewspace-668651/