数据库性能优化报告
序号 | 版本号 | 编制/修改日期 | 修改内容 | 修改人 |
1 | V1.0 | 2019-5-15 | 数据库性能优化报告 | 天凯科技-冯工 |
|
|
|
|
|
经过对客户数据库一段时间的性能监控，发现数据库整体负载极高，从LINUX系统的负载来看，平均活动会话高达10个/秒以上。业务经常出现卡死堵塞状况，此时需要运维人员重启DB才能缓解，但经过一段时间后问题依旧重现。从下面AWR报告进一步分析，直接路径读direct path read的等待占比较大，并且存在严重的read by other session等待，该等待的出现说明系统正处于资源缺乏，严重拥堵状况! DML写操作也存在一定的行锁冲突，每事务的等待次数竟然到达10000以上；经过后续的SQL分析和客户的反映，该系统运作多年没做任何优化，此类SQL存在很大的优化空间，下面详细介绍这些SQL的优化过程，并在优化前后分别做了AWR基线与采样，后续运行了性能对比报告，可直观地反映出这次性能优化的效果。
数据库性能问题很大程度上是由于应用引起的，而应用反映在数据库层面就是SQL代码，所以一个数据库的性能90%以上是由于SQL代码的设计和访问路径不当引起的性能问题，下面是对数据库TOP SQL语句的优化记录，目前已对大部分消耗较高的SQL进行了调整，从后续的优化报告来看，已极大减少了IO的消耗，从而提高数据库的性能效率；另外从数据库的配置参数层面出发，也存在一定的优化空间，下面也给出调整优化SQL的具体操作步骤（注意：下面是罗列了一些关键SQL的优化记录，还有部分优化记录没有贴上）。
UPDATE CT_OUT_OUTWAREHOUSEDETAIL
SET FSEQ =:1,
CFPICKING_ID =:2,
CFSTOCK_OUT_NO =:3,
CFSUPPLIER_CODE =:4,
CFSUPPLIER_NAME =:5,
CFMATERIEL_NAMEID =:6,
CFISSUE_NO =:7,
CFMATERIEL_CATE =:8,
CFPLAN_PACKIG_NUM =:9,
CFPLAN_NUM =:10,
CFPLAN_PALLET_NUM =:11,
CFACTUAL_PACKIG_NUM =:12,
CFACTUAL_NUM =:13,
CFACTUAL_PALLET_NUM =:14,
CFDELIVERY_DATE =:15,
CFDELIVERY_ORG =:16,
CFMID_WAREHOUSEID =:17,
CFDELIVERY_NAME =:18,
CFBATCH_NO =:19,
CFSPEC =:20,
CFMARK =:21,
CFCONTAINER_TYPE =:22,
CFMANUFACTURE_DATE =:23,
CFINVENTERY_UNIT =:24,
CFPACKING_UNIT =:25,
CFMANTISSA =:26,
CFEXCEPTION_LAB =:27,
CFSTATUS =:28,
CFMATERIEL_REALNAME =:29,
CFVOLUME =:30,
CFWEIGHT =:31,
CFACTUAL_VOLUNE =:32,
CFACTUAL_WEIGHT =:33,
CFCARRY_VOLUME =:34,
CFCARRY_WEIGHT =:35,
CFPACKING_L =:36,
CFPACKING_W =:37,
CFPACKING_H =:38,
CFACTUAL_PACKING_L =:39,
CFACTUAL_PACKING_W =:40,
CFACTUAL_PACKING_H =:41,
CFCARRY_PACKING_L =:42,
CFCARRY_PACKING_W =:43,
CFCARRY_PACKING_H =:44,
CFORDER_NO =:45,
CFSTOCK_OUT_TYPE =:46,
CFPACKING_TYPE =:47,
CFDELIVERY_DATE_REAL =:48,
CFDELIVERY_DATE_TXT =:49,
CFOFFICE_NAME =:50,
CFOFFICE_CODE =:51,
CFORIGIN =:52,
CFNEORDER_ID =:53,
CFSNP =:54,
CFREMARK =:55,
CFORDER_NO2 =:56,
CFMATERIEL_ALIAS =:57,
CFPL_REC_NO =:58,
CFCONTAINERFOLD =:59,
CFPLACE_NAMEID =:60,
CFSTOCKINDATE =:61
WHERE FID =:62
优化措施：createindex HZDEAS.ix_fid_ct_out on HZDEAS.CT_OUT_OUTWAREHOUSEDETAIL(FID)online;
优化效果：数据库COST值有上万倍的下降，性能提升效果非常明显。
SELECT"T0"."FID" "ID",
"T0"."FSEQ" "SEQ",
"T0"."FPARENTID" "T1.ID",
"T0"."CFCHOICE" "CHOICE",
"T0"."CFORDER_ID" "ORDER_ID",
"T0"."CFPRO_CODEID" "T2.ID",
"T0"."CFPRO_NAMEID" "T3.ID",
"T0"."CFORG_NAMEID" "T4.ID",
"T0"."CFCOLLECTION_CODEI" "T5.ID",
"T0"."CFCOLLECTION_NAMEI" "T6.ID",
"T0"."CFDELIVERY_CODEID" "T7.ID",
"T0"."CFSUPPLIER_NAME" "SUPPLIER_NAME",
"T0"."CFISSUE_NO" "ISSUE_NO",
"T0"."CFPACKIG_NUM" "PACKIG_NUM",
"T0"."CFDELIVERY_DATE" "DELIVERY_DATE",
"T0"."CFDELIVERY_WAR_NAM" "T8.ID",
"T0"."CFCOLLECTION_IN_NUM" "COLLECTION_IN_NUM",
"T0"."CFCOLLECTION_OUT_NUM" "COLLECTION_OUT_NUM",
"T0"."CFDELIVERY_IN_NUM" "DELIVERY_IN_NUM",
"T0"."CFDELIVERY_NAME" "DELIVERY_NAME",
"T0"."CFCUST_NAMEID" "T9.ID",
"T0"."CFCUST_CODE" "CUST_CODE",
"T0"."CFORG_CODE" "ORG_CODE",
"T0"."CFVOLUME" "VOLUME",
"T0"."CFDELIVERY_TIME" "DELIVERY_TIME",
"T0"."CFDELIVERY_OUT_NUM" "DELIVERY_OUT_NUM",
"T0"."CFSUPPLIER_CODE" "SUPPLIER_CODE",
"T0"."CFPACKING_TYPE" "PACKING_TYPE",
"T0"."CFSIGN_NUM" "SIGN_NUM",
"T0"."CFNUM" "NUM",
"T0"."CFWEIGHT" "WEIGHT",
"T0"."CFORG_BIZID" "T10.ID",
"T0"."CFORG_BIZ_REAL" "ORG_BIZ_REAL",
"T0"."CFOFFICE_CODE" "OFFICE_CODE",
"T0"."CFOFFICE_NAME" "OFFICE_NAME",
"T0"."CFMATERIEL_CODEID" "T11.ID",
"T0"."CFMATERIEL_NAME_REAL" "MATERIEL_NAME_REAL",
"T0"."CFMATERIEL_TYPE" "MATERIEL_TYPE",
"T0"."CFCONTAINER_NAMEID" "T12.ID",
"T0"."CFCONTAINER_NAME_REAL" "CONTAINER_NAME_REAL",
"T0"."CFPALLET_NAME_REAL" "PALLET_NAME_REAL",
"T0"."CFPALLET_NUM" "PALLE T_NUM",
"T0"."CFCONTAINER_NUM" "CONTAINER_NUM",
"T0"."CFPACKING_UNIT" "PACKING_UNIT",
"T0"."CFINVENTORY_UNIT" "INVENTORY_UNIT",
"T0"."CFDELIVERY_ORG" "DELIVERY_ORG",
"T0"."CFMID_WAREHOUSE" "MID_WAREHOUSE",
"T0"."CFPACKING_SNP" "PACKING_SNP",
"T0"."CFCONTAINER_SNP" "CONTAINER_SNP",
"T0"."CFPALLET_SNP" "PALLET_SNP",
"T0"."CFMATERIEL_CODE_REAL" "MATERIEL_CODE_REAL",
"T0"."CFPACKING_L" "PACKING_L",
"T0"."CFPACKING_W" "PACKING_W",
"T0"."CFACTUAL_L" "ACTUAL_L",
"T0"."CFACTUAL_W" "ACTUAL_W",
"T0"."CFACTUAL_H" "ACTUAL_H",
"T0"."CFCARRY_L" "CARRY_L",
"T0"."CFCARRY_H" "CARRY_H",
"T0"."CFCARRY_W" "CARRY_W",
"T0"."CFPACKING_H" "PACKING_H",
"T0"."CFACTUAL_VOLUME" "ACTUAL_VOLUME",
"T0"."CFACTUAL_WEIGHT" "ACTUAL_WEIGHT",
"T0"."CFCARRY_VOLUME" "CARRY_VOLUME",
"T0"."CFCARRY_WEIGHT" "CARRY_WEIGHT",
"T0"."CFMATERIEL_SRC" "MATERIEL_SRC",
"T0"."CFDELIVERY_DATE_REAL" "DELIVERY_DATE_REAL",
"T0"."CFDELIVERY_DATE_TXT" "DELIVERY_DATE_TXT",
"T0"."CFBACK_PACKING_NUM" "BACK_PACKING_NUM",
"T0"."CFBACK_VOLUME" "BACK_VOLUME",
"T0"."CFBACK_WEIGHT" "BACK_WEIGHT",
"T0"."CFSTATUS" "STATUS",
"T0"."CFIN_LETTER_NO" "IN_LETTER_NO",
"T0"."CFOUT_LETTER_NO" "OUT_LETTER_NO",
"T0"."CFADDCONTAINID" "T13.ID",
"T0"."CFADDCONTAINQTY" "ADDCONTAINQTY",
"T0"."CFCONTAINNAMEID" "T14.ID",
"T0"."CFCOLLECTION_OUTGOODS_NUM" "COLLECTION_OUTGOODS_NUM",
"T0"."CFCOLLECTION_INGOODS_NUM" "COLLECTION_INGOODS_NUM",
"T0"."CFDELIVERY_INGOODS_NUM" "DELIVERY_INGOODS_NUM",
"T0"."CFDELIVERY_OUTGOODS_NUM" "DEL IVERY_OUTGOODS_NUM",
"T0"."CFREMARK" "REMARK",
"T0"."CFSUPPLIERID" "T15.ID"
FROM HZDEAS."CT_OMS_NEORDERENTRY" "T0"
INNERJOIN HZDEAS."CT_OMS_NEORDER" "T1"
ON "T0"."FPARENTID" = "T1"."FID"
WHERE "T0"."FID" =:1
优化措施：createindex HZDEAS.ix_fid_CT_OMS on HZDEAS.CT_OMS_NEORDERENTRY(FID)online;
优化效果：数据库COST值有上万倍的下降，性能提升效果非常明显。
SELECT*
FROM HZDEAS.T_JOB_INST
WHERE((((fstate IN('Created','ReScheduled'))AND
(fisolationboundary ='server1@192.168.0.254'OR
(fisolationboundary ='none'AND
((fhashcode >=0)AND(fhashcode <=1999)))OR
fisolationboundary ='flr'OR
(fisolationboundary ='192.168.0.254'AND
((fhashcode >=0)AND(fhashcode <=1999)))))AND
(fholderid ISNULL))AND(fscheduledtime <=:1))
ORDERBY fscheduledtime ASC
优化措施：
createindex HZDEAS.IDX$$_308210001 on HZDEAS.T_JOB_INST("FSTATE","FHOLDERID","FSCHEDULEDTIME") online;
优化效果：数据库COST值有上万倍的下降，性能提升效果非常明显。
在对数据库做性能优化前，我们在做了AWR性能基线报告，也就是能反映数据库在优化前的性能状态，然后在优化后运行AWR性能差别报告，下面就是优化后的报告的内容截图，从这个差别报告来看，我们可以比较直接地反映出这次性能优化的效果。
报告分析说明：
从报告的等待事件报告来看，优化效果还是有非常巨大效益的，首先从数据库的负载方面来看，优化后，数据库的负载从6下降到0.1以下（这个数值主要是从Elapsed/DB Time所得，是一个能直观体现DB负载的参考值），LINUX系统的负载来看，CPU负载从原来的10下降到到0.7左右，从数据库等待事件方面来看，优化前存在严重的read by other session等待，优化后该等待事件已消失，并且DML写操作阻塞已消失，数据库以CPU TIME等待为主，该事件如占比在90%以上，说明性能非常理想，并且优化前出现的业务卡死情况，需要定期重启DB来缓解的问已彻底解决。总的来说，本次优化效果显著，保守估计，数据库整体性能500%的提升目标已超额完成，下面列出优化前后的关键性能指标对比。
性能指标 | 优化前 | 优化后 |
数据库负载（Elapsed/DB Time） | 6 | 0.1 |
Linux系统CPU负载 | 10 | 0.7 |
数据库等待事件 | read by other session direct path read TX-lock contention | CPU TIME |
数据库事务等待数 | 10000+ | 30- |
业务运作状况 | 业务操作经常卡死，缓慢等待， 必须重启DB来解决 | 业务操作畅通无阻，无异常状况出现 |
