数据库诊断问题分析解决记录0701.docx
《数据库诊断问题分析解决记录0701.docx》由会员分享，可在线阅读，更多相关《数据库诊断问题分析解决记录0701.docx（30页珍藏版）》请在冰豆网上搜索。
数据库诊断问题分析解决记录0701
数据库诊断问题分析解决记录
巡检人：
罗贵林
报告生成日期：
2011-07-01
文档控制
不得向与此无关的个人或机构传阅或复制。
修改记录
日期
作者
版本
修改记录
2011-07-01
罗贵林
V1.00
新作成
分发者
、姓名
职位
审阅记录
姓名
职位
目录
文档控制2
修改记录2
分发者2
审阅记录2
目录3
1.logfilesync与logfileparallelwrite等待事件问题解决5
1.1问题分析5
1.2解决方案5
2.大量dbfilesequentialread等待事件问题解决6
2.1问题分析6
2.2解决方案6
3.涉及临时表语句查询慢的问题解决7
3.1问题分析7
3.2解决方案8
4.未绑定变量SQL问题解决11
4.1问题分析11
4.2解决方案11
5.查询过慢的SQL问题解决12
5.1问题分析12
5.2解决方案15
6.对一些大表进行分区16
6.1问题分析16
6.2解决方案16
7.调优数据库性能参数17
7.1问题分析17
7.2解决方案17
8.SGA扩展18
8.1问题分析18
8.2解决方案18
9.JOB任务集中执行问题解决19
9.1问题分析19
9.2解决方案19
10.分区表空间清理方案20
10.1问题分析20
10.2解决方案20
11.增加数据库进程连接数21
11.1问题分析21
11.2解决方案21
12.解决WEBLOGIC中间件服务器异常宕机问题22
12.1问题分析22
12.2解决方案22
13.解决重复记录导致查询不准确的问题23
13.1问题分析23
13.2解决方案24
1.logfilesync与logfileparallelwrite等待事件问题解决
1.1问题分析
在时间消耗排名前5的等待事件中，logfilesync与logfileparallelwrite等待事件分别排在第3第5位，说明数据库IO存在一定问题，在8点到10点时段占总消耗时间的2.8%，在0点到7点时段占25.2%。
经分析，发现logfile与datafile存放在不同文件系统下，结合目前的文件系统情况，调优的话，可将每个日志组的一个成员放在/eicparchivelog归档目录下，或者查找各模块程序上的原因，看是否可以一次提交更多记录，采用ORACLE批量处理的SQL方法。
1.2解决方案
1.找出提交频繁的语句，考虑是否可以运用ORACLE的FORALL和BULKCOLLECTINTO批量操作用法进行改造。
尽量减少一次提交一条记录这样的情况，如果无法改造，可根据事务的要求，尽量100/1000提交一次。
2.将每个日志组的一个成员放在/eicparchivelog归档目录下
--先删除每个日志组的一个成员
alterdatabsedroplogfilemember'/eicpsys/eicp/redo01_b.log';
alterdatabsedroplogfilemember'/eicpsys/eicp/redo02_b.log';
alterdatabsedroplogfilemember'/eicpsys/eicp/redo03_b.log';
alterdatabsedroplogfilemember'/eicpsys/eicp/redo04_b.log';
alterdatabsedroplogfilemember'/eicpsys/eicp/redo05_b.log';
alterdatabsedroplogfilemember'/eicpsys/eicp/redo06_b.log';
--在/eicparchivelog归档目录下增加每个日志组成员
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo01_b.log'togroup1;
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo02_b.log'togroup2;
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo03_b.log'togroup3;
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo04_b.log'togroup4;
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo05_b.log'togroup5;
alterdatabaseaddlogfilemember'/eicparchivelog/eicp/redo06_b.log'togroup6;
2.大量dbfilesequentialread等待事件问题解决
2.1问题分析
在时间消耗排名前5的等待事件中，dbfilesequentialread等待事件分别排在第4位，该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕（没有正确选择驱动行源），或者使用了非选择性索引。
通过将这种等待与awr中已知其它问题联系起来（如效率不高的sql）,通过检查确保索引扫描是必须的，并确保多表连接的连接顺序来调整。
2.2解决方案
由开发人员找出一些执行慢的SQL，判断消耗大的关联是否可以优化，此事件待优化完SQL再观察。
3.涉及临时表语句查询慢的问题解决
3.1问题分析
1.SELECTa.org_no,01cp_type_code,
TO_CHAR（a.data_date,'YYYY-MM-DD'）ASdata_date,
TO_CHAR（NVL（SUM（DECODE（a.status_code,01,1,0））,0））ASrun_total,
TO_CHAR（NVL（SUM（DECODE（a.status_code,
01,
DECODE（a.online_flag,1,1,0）,
0））,
0））ASon_line
FROMr_tmnl_statusa,r_tmnl_runb
WHEREa.data_dateBETWEENTO_DATE（'2011-06-29','YYYY-MM-DD'）AND
TO_DATE（'2011-07-1','YYYY-MM-DD'）
ANDa.cp_no=b.cp_no
ANDb.protocol_code!
='100'
ANDEXISTS（SELECT1FROMs_temporgnoWHEREorgno.item=a.org_no）
ANDEXISTS（SELECT1
FROMr_cpcp
WHEREcp.cp_no=a.cp_no
ANDcp.status_code='05'）
GROUPBYa.org_no,a.data_date
2.SELECTcp_no,NAME,cp_type_code,cp_status_code,cp_addr,base.org_no,
cons_org_no,cons_tg_no,cons_tg_name,cons_tg_addr,cons_tg_cap,
cons_tg_runcap,cons_tg_status,trade_code,elec_type_code,volt_code,
cons_sort_code,shift_no,lode_pub_code,mr_sect_no,line_no,
line_name,terminal_addr,
area_code
||RTRIM（LPAD（TRIM（TO_CHAR（terminal_addr,'XXXXXXXX'））,4,'0'））
AStmnl_logic_addr,
tmnl_status_code,coll_mode,TO_CHAR（run_date,'YYYY-MM-DD'）run_date,
area_code,channel_no,protocol_code,TO_CHAR（sim_no）sim_no,
asset_no_equip,tmnl_factory,tmnl_model_code,tmnl_type_code,
cur_status_code,mp_sgrp_id,mp_sgrp_index,mp_sgrp_name,
mp_sgrp_enable,mp_sgrp_prop,mp_ct_ratio,mp_pt_ratio,mp_link_type,
mp_rating_vol,coll_obj_id,coll_port,ct_ratio,pt_ratio,t_factor,
meter_const,port_no,collector_type,collector_id,obj_attrib,mp_no,
mp_name,mp_addr,mp_type_code,mp_attr_code,meas_mode,inst_loc,
TO_CHAR（inst_date,'YYYY-MM-DD'）inst_date,comm_no,baudrate,
comm_mode,meter_asset_no,meter_made_no,meter_sort_code,
meter_type_code,meter_model_code,meter_factory,meter_bar_code,
cons_no,cons_name,cons_addr,cons_office_tel,cons_mobile,
profile_sort,TO_CHAR（e.data_date,'yyyy-mm-dd'）data_date,
TO_CHAR（e.col_time,'yyyy-mm-ddhh24:
mi:
ss'）col_time,pap_r,pap_r1,
pap_r2,pap_r3,pap_r4,prp_r,prp_r1,prp_r2,prp_r3,prp_r4,rap_r,
rap_r1,rap_r2,rap_r3,rap_r4,rrp_r,rrp_r1,rrp_r2,rrp_r3,rrp_r4,
rp1_r,rp1_r1,rp1_r2,rp1_r3,rp1_r4,rp3_r,rp3_r1,rp3_r2,rp3_r3,
rp3_r4,rp2_r,rp2_r1,rp2_r2,rp2_r3,rp2_r4,rp4_r,rp4_r1,rp4_r2,
rp4_r3,rp4_r4,DEMAND.pap_demand,DEMAND.rap_demand,
TO_CHAR（e.get_date,'YYYY-MM-DDHH24:
MI:
SS'）ASget_date
FROMb_archivesbase,
（SELECTREAD.*
FROMe_mp_day_readREAD
WHEREREAD.data_date=TO_DATE（:
1,'yyyy-mm-dd'））e,
（SELECTmpdem.*
FROMe_mp_day_demandmpdem
WHEREmpdem.data_date=TO_DATE（:
2,'yyyy-mm-dd'））DEMAND,
s_tempt
WHEREe.ID（+）=base.mp_sgrp_id
ANDDEMAND.ID（+）=base.mp_sgrp_id
ANDbase.cp_type_code=:
3
ANDbase.cp_no=t.item
ANDbase.profile_sort='1'
ANDbase.cp_status_code='05'
开发人员发现这两个语句在多个会话同时执行时会很慢，经分析，语句用到S_TEMP临时表，存在多个会话同时执行insert、delete操作，这样会造成一定的IO等待，影响性能，因delete操作也是对整个临时表的数据进行删除，所以建议用truncate代替delete操作。
而因为使用临时表是为了多表关联，因此建议在临时表上建立索引。
3.2解决方案
1.在s_temp表item字段建立索引，然后用以下语句替换。
SELECTa.org_no,01cp_type_code,
TO_CHAR（a.data_date,'YYYY-MM-DD'）ASdata_date,
TO_CHAR（NVL（SUM（DECODE（a.status_code,01,1,0））,0））ASrun_total,
TO_CHAR（NVL（SUM（DECODE（a.status_code,
01,
DECODE（a.online_flag,1,1,0）,
0））,
0））ASon_line
FROMr_tmnl_statusa,r_tmnl_runb
WHEREa.data_dateBETWEENTO_DATE（'2011-06-29','YYYY-MM-DD'）AND
TO_DATE（'2011-07-1','YYYY-MM-DD'）
ANDa.cp_no=b.cp_no
ANDb.protocol_code!
='100'
ANDEXISTS（SELECT/*+INDEX（orgnoIDX_S_TEMP_A）*/1FROMs_temporgnoWHEREorgno.item=a.org_no）
ANDEXISTS（SELECT1
FROMr_cpcp
WHEREcp.cp_no=a.cp_no
ANDcp.status_code='05'）
GROUPBYa.org_no,a.data_date
2.SELECT/*+INDEX（tIDX_S_TEMP_A）*/cp_no,NAME,cp_type_code,cp_status_code,cp_addr,base.org_no,
cons_org_no,cons_tg_no,cons_tg_name,cons_tg_addr,cons_tg_cap,
cons_tg_runcap,cons_tg_status,trade_code,elec_type_code,volt_code,
cons_sort_code,shift_no,lode_pub_code,mr_sect_no,line_no,
line_name,terminal_addr,
area_code
||RTRIM（LPAD（TRIM（TO_CHAR（terminal_addr,'XXXXXXXX'））,4,'0'））
AStmnl_logic_addr,
tmnl_status_code,coll_mode,TO_CHAR（run_date,'YYYY-MM-DD'）run_date,
area_code,channel_no,protocol_code,TO_CHAR（sim_no）sim_no,
asset_no_equip,tmnl_factory,tmnl_model_code,tmnl_type_code,
cur_status_code,mp_sgrp_id,mp_sgrp_index,mp_sgrp_name,
mp_sgrp_enable,mp_sgrp_prop,mp_ct_ratio,mp_pt_ratio,mp_link_type,
mp_rating_vol,coll_obj_id,coll_port,ct_ratio,pt_ratio,t_factor,
meter_const,port_no,collector_type,collector_id,obj_attrib,mp_no,
mp_name,mp_addr,mp_type_code,mp_attr_code,meas_mode,inst_loc,
TO_CHAR（inst_date,'YYYY-MM-DD'）inst_date,comm_no,baudrate,
comm_mode,meter_asset_no,meter_made_no,meter_sort_code,
meter_type_code,meter_model_code,meter_factory,meter_bar_code,
cons_no,cons_name,cons_addr,cons_office_tel,cons_mobile,
profile_sort,TO_CHAR（e.data_date,'yyyy-mm-dd'）data_date,
TO_CHAR（e.col_time,'yyyy-mm-ddhh24:
mi:
ss'）col_time,pap_r,pap_r1,
pap_r2,pap_r3,pap_r4,prp_r,prp_r1,prp_r2,prp_r3,prp_r4,rap_r,
rap_r1,rap_r2,rap_r3,rap_r4,rrp_r,rrp_r1,rrp_r2,rrp_r3,rrp_r4,
rp1_r,rp1_r1,rp1_r2,rp1_r3,rp1_r4,rp3_r,rp3_r1,rp3_r2,rp3_r3,
rp3_r4,rp2_r,rp2_r1,rp2_r2,rp2_r3,rp2_r4,rp4_r,rp4_r1,rp4_r2,
rp4_r3,rp4_r4,DEMAND.pap_demand,DEMAND.rap_demand,
TO_CHAR（e.get_date,'YYYY-MM-DDHH24:
MI:
SS'）ASget_date
FROMb_archivesbase,
（SELECTREAD.*
FROMe_mp_day_readREAD
WHEREREAD.data_date=TO_DATE（:
1,'yyyy-mm-dd'））e,
（SELECTmpdem.*
FROMe_mp_day_demandmpdem
WHEREmpdem.data_date=TO_DATE（:
2,'yyyy-mm-dd'））DEMAND,
s_tempt
WHEREe.ID（+）=base.mp_sgrp_id
ANDDEMAND.ID（+）=base.mp_sgrp_id
ANDbase.cp_type_code=:
3
ANDbase.cp_no=t.item
ANDbase.profile_sort='1'
ANDbase.cp_status_code='05'
2.建议开发人员在程序中用truncate代替delete操作。
3.建议用管道函数功能替换临时表写法：
CREATEORREPLACEPACKAGEpkg1AS
--Purpose:
对表函数的应用实例
TYPEty_rec_userISrecord（--定义一个record类型的TYPE
idnumber（20）,
namevarchar2（60）
）;
TYPEout_rec_setistableofty_rec_user;--定义一个嵌套表集合类型out_rec_set,作为表函数的返回类型
--定义返回集合类型的管道表函数
FUNCTIONf1（xNUMBER）RETURNout_rec_setPIPELINED;
--引用在外部自定义的object类型作为表函数的集合类型
TYPEout_obj_setistableofTY_OBJ_USER;
FUNCTIONF_PIE_TEST（cNUMBER）RETURNout_obj_setPIPELINED;
ENDpkg1;
CREATEORREPLACEPACKAGEBODYpkg1AS
--Purpose:
对表函数的应用实例
FUNCTIONf1（xNUMBER）RETURNout_rec_setPIPELINEDIS
user_recty_rec_user;
BEGIN
FORiIN1..xLOOP
--user_rec:
=ty_rec_user（i,'user'||i）;--ty_rec_user定义为record类型时不能这样赋值，只有定义成obj时才可以
user_rec.id:
=i;
user_rec.name:
='user'||i;
--PIPEROW（1,'user'||1）;
piperow（user_rec）;
ENDLOOP;
RETURN;
END;
--使用在外部自定义的object类型表函数
FUNCTIONF_PIE_TEST（cNUMBER）RETURNout_obj_setPIPELINEDis
user_ty_objTY_OBJ_USER;
BEGIN
FORiin1..cLOOP
user_ty_obj:
=TY_OBJ_USER（i,'name'||i）;
PIPEROW（user_ty_obj）;
ENDLOOP;
RETURN;
END;
ENDpkg1;
--外部自定义的object类型
createorreplacetypeTY_OBJ_USERasobject
（
--Purpose:
测试
idnumber（20）,
namevarchar2（60）,
）
表函数的调用：
select*fromtable（pkg1.f1（4））--直接在plsql中执行
select*FROMTABLE（CAST（pkg1.f1（4）ASout_rec_set））--jav
升级会员 