数据库有优化案例培训ppt课件

HUAWEI TECHNOLOGIES CO., LTD.,Page,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,HUAWEI Confidential,Thank You,ICD数据问题总结培训,李凌志 37090,CONTENTS,重大事故总结,重大事故分类,主要数据库问题,数据库案例,总结,Page,2,发生局点,问题描述,深圳联通,客服数据库,由于低性能的SQL语句频繁调用，导致数据库重启，业务中一个半小时，,优化SQL后问题才解决,广州联通,客服数据库,由于SQL语句引起两次数据库的IDLE为0，导致工作流、知识库停止使用，,数据库重新启动，业务中断半个小时,福建移动,客服数据库,由于索引未及时创建，导致业务中断半个小时，删除部分历史数据后，,重建索引，问题解决,天津移动,客服数据库,T_PUB_COMMINFO的关键索引未创建导致，对应的SQL语句非常消耗资源，来话接入很慢，小型机的IDLE为0,创建索引后问题解决,深圳电信,客服数据库,SQL语句索引使用不合理，引起数据库的IDLE长时间为0,座席无法嵌入，业务中断半个小时候,海南移动,客服数据库,人为删除流程调用的一个表，导致存储过程失效，引起业务中断半个小时，重新创建表、恢复数据后问题解决,浙江金华,114系统,人为删除所有存储过程，然后重新加载，导致座席调用的接入失败，导致业务中断5分钟,山西移动,客服数据库,存储过程未及时加载，触发ORACLE的BUG，导致人工业务中断一个半个小时,陕西联通,客服数据库,低性能的SQL语句引起小型机的IDLE长时间为0，导致来话接入非常慢,新疆电信,客服数据库,诸多有问题的SQL语句引起数据库的IDLE长时间为0,通过手工杀数据库的进程，维持系统运行,2006年与数据库相关的事故、重大事故,Page,3,数据库问题总结,SQL性能问题，是引起重大事故的主要原因,人为操作不规范，也是引起重大事故的原因,ORACLE的BUG，引起业务中断诱发重大事故,Page,4,数据库性能案例,局点 新疆电信,日期 20060614,表现症状,座席接入很慢，数据库的IDLE几乎为0,IOWAIT到达60%。,SQL语句写法有问题,索引使用不合理，使用的效率比较低,对大表、分区表的查询，没有使用到索引,分区表的查询，没有添加分区条件,数据库参数配置不合理,db_block_buffers 的值设置太小。,问题解决情况：通过优化后，使系统的cpu的idle由0左右提升到业务高峰期的40%。,Page,5,主要有问题的SQL大表的查询没有使用到索引,SELECT MAX(T.SERIALNO),FROM T_PUB_COMMONINFO T,WHERE T.ACCEPTERNO = :b1 AND PARTID = :b2,问题描述,该语句虽然按分区条件查询，但没有在字段ACCEPTERNO 上创建索引,，导致对整个分区扫描,优化方式,对该字段创建分区索引，避免对分区扫描,create index ix_pub_commaccepterno on t_Pub_CommonInfo(ACCEPTERNO),online nologging tablespace service_hist_idx,Page,6,主要有问题的SQL对分区表查询没有使用到分区条件,SELECT COUNT(*) FROM T_AG_RECORDFILE,WHERE SERIALNO = :b1,问题描述：,该表T_AG_RECORDFILE是分区表，数据量非常大，上面的查询，虽然用到了流水号对应的索引，但是没有添加分区条件，导致对每个分区都访问，增加了系统的开销。,优化方式：,添加按分区条件查询,SELECT COUNT(*) FROM T_AG_RECORDFILE WHERE SERIALNO = :b1 AND,PARTID=substr(:b1,5,4),Page,7,主要有问题的SQL索引创建和使用不合理,SELECT 1 FROM T_WF_SKILLSTAFFAUTH T,WHERE T.SKILLID = :b1 AND T.STAFFNO = :b2,问题描述：,该表存在字段SKILLID的单独索引以及SKILLID和STAFFNO的组合索引。上面的SQL语句通过SKILLID字段的单独索引访问，效率比较低。,优化方式：,索引1 create index ix_skillid on T_WF_SKILLSTAFFAUTH(SKILLID),索引2 create index ix_skillid on T_WF_SKILLSTAFFAUTH(SKILLID,STAFFNO),当字段SKILLID单独出现在查询条件中时，可以通过索引2代替索引1，而这两个索引同时存在的时，ORACLE优先使用单个字段的索引，因此索引1是多余的,通过删除字段SKILLID对应的单独索引,使SQL语句使用到复合索引,Page,8,主要有问题的SQL使用的索引选择性不好,SELECT COUNT(*) FROM T_QC_INFO,WHERE OBJECTID = :b1 AND RECORDERNUM = :b2,问题描述：,上面的SQL语句,使用的是OBJECTID字段的索引，通过查询该表的数据分析,发现该字段为有限的几类数据。,问题解决：,通过对RECORDERNUM 字段创建索引，使SQL语句用到新的索引，提高了查询效率。,Page,9,主要有问题的SQL使用的索引选择性不好,SELECT count(*) FROM (SELECT 1,FROM t_Pub_CommonInfo a,T_PUB_USERSATISFY b, T_Pub_Orga c, t_ag_language,d,t_Pub_Receptmethod e,WHERE,AcceptBeginTime,BETWEEN TO_DATE(2005-06-01 00:00:00,YYYY-MM-DD HH24:MI:SS),AND TO_DATE(2006-06-14 23:59:59,YYYY-MM-DD HH24:MI:SS),And a.USERSATISFY = b.ID(+) And a.UserCity = c.OrgaID,And a.LANGUAGE = d.LANGUAGEID(+),AND a.ACCEPTID=e.ID(+) AND a.servicecity in,(select cityid from t_pm_cityauth where staffno = 2049),AND (A.PartID = 0601 OR A.PartID = TO_DATE(2006-03-31 00:00:00, YYYY-MM-DD HH24:MI:SS) AND A.Accepttime = TO_DATE(2006-03-31 00:00:00, YYYY-MM-DD HH24:MI:SS) AND A.Accepttime = to_date(2006-03-08 00:00:00, yyyy-mm-dd hh24:mi:ss) and t.handledate = to_date(2006-03-08 00:00:00, yyyy-mm-dd hh24:mi:ss),and t.handledate SYSDATE - :b4 AND ROWNUM = 1,UNION,SELECT SERIALNO FROM T_WF_DISPOSALSTATUSHIS A WHERE (,ACCEPTPHONE = :b1 OR CALLERNO = :b2 OR USERPHONE1 = :b3,) AND ACCEPTTIME SYSDATE - :b4 AND ROWNUM = 1,Page,16,有问题的SQL优化后的SQL,SELECT SERIALNO,FROM T_WF_DISPOSALSTATUSHIS,WHERE (ACCEPTPHONE = :B4),AND ACCEPTTIME SYSDATE - :B1,UNION,SELECT SERIALNO,FROM T_WF_DISPOSALSTATUSHIS,WHERE (CALLERNO = :B3),AND ACCEPTTIME SYSDATE - :B1,UNION,SELECT SERIALNO,FROM T_WF_DISPOSALSTATUSHIS A,WHERE (USERPHONE1 = :B2),AND ACCEPTTIME SYSDATE - :B1,优化方式：,通过用union 代替or ，通过创建索引，避免了全表扫描，由于每个or对,应的条件不同，因此此处不能用union all代替or ,否则会改变查询结果,Page,17,有问题的SQL相同的问题的不同的SQL,SELECT count(*) FROM (SELECT /*+ index(a,IX_COMMONINFO_CALLERNO),*/ SerialNo,DECODE(UserName,没有此客户资料!,无资料!,UserName) as UserName,FROM t_Pub_CommonInfo a,T_PUB_USERSATISFY b, T_Pub_Orga c,WHERE,AcceptBeginTime,BETWEEN TO_DATE(2006-05-01 00:00:00,YYYY-MM-DD HH24:MI:SS) AND TO_DATE(2006-05-26 23:59:59,YYYY-MM-DD HH24:MI:,SS) And a.USERSATISFY = b.ID(+) And a.UserCity = c.OrgaID AND A.PartID = 0501,AND A.PartID = 0526 AND (,A.UserPhone1= OR A.UserPhone2=),AND A.UserCity = 02 AND A.StaffCity = 02),此SQL语句同上面的SQL语句存在同样的问题。此语句虽然用到了,AcceptBeginTime,对应的索引，但是此索引的效率非常差，反而会使查询效率更低,Page,18,有问题的SQL使用的索引不是最优的,Page,19,有问题的SQL优化后的SQL语句,【优化前的SQL】,SELECT,DISTILLSTAFF, HANDLESKILLID, HANDLESTATUS,FROM,T_WF_PROCESS,WHERE,SERIALNO = :B2,AND,SERVICECLASSID = :B1,AND,HANDLESTATUS,IN,( 0, 4 ),【优化后的SQL】,SELECT,DISTILLSTAFF, HANDLESKILLID, HANDLESTATUS,FROM,T_WF_PROCESS,WHERE,SERIALNO = :B2,AND,SERVICECLASSID = :B1,AND HANDLESTATUS0 IN ( 0 , 4 ),优化说明：通过添加表达式，屏蔽选择性比较差的索引,Page,20,有问题的SQL优化后的SQL语句执行计划,Page,21,有问题的SQL驱动表全表扫描,Page,22,有问题的SQL优化后的SQL,select,distinct,a.nextnodeid,as,nodeid, a.nextnodeid | c.nodename,as,nodename,from,t_wf_nodeskill a, t_wf_skillstaff b, t_wf_node c,where,b.skillid = a.nextskillid,and,b.staffno = :P1,and,c.nodeid = a.nextnodeid,通过创建下面的索引,create,index,ix_wf_nodeskillnextskillid,on,t_wf_nodeskill(nextskillid),tablespace,service_hist_idx,create,index,ix_wf_ndeskillstaffno,on,t_wf_skillstaff(staffno),tablespace,service_hist_idx,Page,23,有问题的SQL优化后的SQL语句的执行计划,Page,24,那些情况需要关注执行计划,对大表的查询，包括分区表、历史记录表,对多个表的查询，有很过多表的关联查询，超过3个。,调用非常频繁的SQL语句，有的SQL语句调用非常频繁，每次调用虽然不特别消耗资源，但是调用次数多，累计效应对系统的性能影响非常明显。,Page,25,对分区表的查询,尽可能的用到索引，尽可能的避免对全表或分区扫描,查询分区范围尽可能的少,尽可能用到选择性比较好的分区索引。,Page,26,常用的SQL调优手段,通过表达式，屏蔽性能比较差的索引,通过指定索引，使用SQL语句用到比较优的索引,通过选择CBO或RBO的方式，改变执行计划，从而使用SQL语句用到最佳的索引,通过优化对应SQL语句的写法，达到优化效果,通过修改表结构，减少过多表的关联查询，尤其是对历史表、大分区表的查询。,Page,27,