数据库性能优化报告


序号

版本号

编制/修改日期

修改内容

修改人

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的优化记录,还有部分优化记录没有贴上)。

2.1   优化记录1

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

1.png

2.png

3.png

优化措施:createindex HZDEAS.ix_fid_ct_out on HZDEAS.CT_OUT_OUTWAREHOUSEDETAIL(FID)online;

4.png

优化效果:数据库COST值有上万倍的下降,性能提升效果非常明显。

5.png


2.2   优化记录2

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;

6.png

优化效果:数据库COST值有上万倍的下降,性能提升效果非常明显。


2.3   优化记录3

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

7.png

优化措施:

createindex HZDEAS.IDX$$_308210001 on   HZDEAS.T_JOB_INST("FSTATE","FHOLDERID","FSCHEDULEDTIME")   online;8.png

优化效果:数据库COST值有上万倍的下降,性能提升效果非常明显。


三,优化效果

在对数据库做性能优化前,我们在做了AWR性能基线报告,也就是能反映数据库在优化前的性能状态,然后在优化后运行AWR性能差别报告,下面就是优化后的报告的内容截图,从这个差别报告来看,我们可以比较直接地反映出这次性能优化的效果。

11.png13.png






报告分析说明

从报告的等待事件报告来看,优化效果还是有非常巨大效益的,首先从数据库的负载方面来看,优化后,数据库的负载从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来解决

业务操作畅通无阻,无异常状况出现