达梦数据库压测问题记录及解决

1、临时表删除时间过长

解决：由SQLSERVER转换到dm时，由于ts字段函数不支持。改为手动导出ddl，处理后手工导入达梦。导致部分临时表被转成物理表。后删除错误的临时表后问题解决。

2、部分场景失败用例过多

原因：达梦-付款单保存  用例选择错误，没有用飞虎录制的

解决：飞虎重新录制一个，替换后解决。

3、报错

com.yonyou.runner.exception.CheckException: 数据包<ListElement[110]>检查点校验失败！当前值为：{  "data": null,  "error": {    "exceptionClass":"nc.bs.uif2.validation.ValidationException",    "message":"sql:SELECT * FROM fa_depmethod WHERE pk_group \u003d \u002700011A10000000000D8J\u0027 or pk_group\u003d\u0027GLOBLE00000000000000\u0027 超出全局hash join空间,适当增加HJ_BUF_GLOBAL_SIZE",    "type":null,    "stack":"",    "status": 1000  },  "success": false}

 at com.yonyou.runner.packet.DataPacket.check(DataPacket.java:315)

 at com.yonyou.runner.caze.Case.replay(Case.java:213)

 at com.yonyou.runner.user.User.replay(User.java:231)

 at com.yonyou.runner.user.User.businessLogic(User.java:138)

 at com.yonyou.runner.thread.RThread.run(RThread.java:135)

解决：调整dm.ini

HJ_BUF_GLOBAL_SIZE改为4000

HJ_BUF_SIZE改为200

4、付款单管理保存提交9秒+

第一期解决：dm调整dm.ini

5、采购订单维护保存，单SQL耗时2秒

select barriveclose, bborrowpur, bcompare, binvoiceclose, blargess, bpayclose, breceiveplan, bstockclose, btransclosed, btriatradeflag, casscustid, castunitid, ccontractid, ccontractrowid, ccurrencyid, cdestiareaid, cdesticountryid, cdevaddrid, cdevareaid, cecbillbid, cecbillid, cectypecode, cffileid, cfirstbid, cfirstid, cfirsttypecode, chandler, corigareaid, corigcountryid, corigcurrencyid, cpraybillbid, cpraybillcode, cpraybillhid, cpraybillrowno, cpraytypecode, cpriceaudit_bb1id, cpriceaudit_bid, cpriceauditid, cproductorid, cprojectid, cprojecttaskid, cqpbaseschemeid, cqtunitid, cqualitylevelid, crececountryid, crowno, csendcountryid, csourcebid, csourceid, csourcetypecode, ctaxcodeid, ctaxcountryid, cunitid, cvenddevaddrid, cvenddevareaid, dbilldate, dcorrectdate, dorigplanarrvdate, dplanarrvdate, factpurtype, fbuysellflag, fisactive, fneedpurtype, ftaxtypeflag, istorestatus, nacccancelinvmny, naccumarrvnum, naccumdevnum, naccuminvoicemny, naccuminvoicenum, naccumpickupnum, naccumrpnum, naccumstorenum, naccumwastnum, nastnum, nbackarrvnum, nbackstorenum, ncalcostmny, ncaltaxmny, nexchangerate, nfeemny, nglobalexchgrate, nglobalmny, nglobaltaxmny, ngroupexchgrate, ngroupmny, ngrouptaxmny, nitemdiscountrate, nmny, nnetprice, nnosubtax, nnosubtaxrate, nnum, norigmny, norignetprice, norigordernum, norigorderprice, norigprice, norigtaxmny, norigtaxnetprice, norigtaxprice, npacknum, nprice, nqtnetprice, nqtorignetprice, nqtorigprice, nqtorigtaxnetprc, nqtorigtaxprice, nqtprice, nqttaxnetprice, nqttaxprice, nqtunitnum, nsendplannum, nsuprsnum, ntax, ntaxmny, ntaxnetprice, ntaxprice, ntaxrate, nvolumn, nweight, pk_apfinanceorg, pk_apfinanceorg_v, pk_apliabcenter, pk_apliabcenter_v, pk_arrliabcenter, pk_arrliabcenter_v, pk_arrvstoorg, pk_arrvstoorg_v, pk_batchcode, pk_cenpurule_b, pk_discount, pk_flowstockorg, pk_flowstockorg_v, pk_group, pk_material, pk_order, po_order_b.pk_order_b, pk_org, pk_org_v, pk_psfinanceorg, pk_psfinanceorg_v, pk_receiveaddress, pk_recvstordoc, pk_reqcorp, pk_reqdept, pk_reqdept_v, pk_reqstoorg, pk_reqstoorg_v, pk_reqstordoc, pk_schedule, pk_schedule_b, pk_srcmaterial, pk_srcorder_b, pk_supplier, po_order_b.ts, vbatchcode, vbdef1, vbdef10, vbdef11, vbdef12, vbdef13, vbdef14, vbdef15, vbdef16, vbdef17, vbdef18, vbdef19, vbdef2, vbdef20, vbdef3, vbdef4, vbdef5, vbdef6, vbdef7, vbdef8, vbdef9, vbmemo, vchangerate, vcontractcode, vecbillcode, vfirstcode, vfirstrowno, vfirsttrantype, vfree1, vfree10, vfree2, vfree3, vfree4, vfree5, vfree6, vfree7, vfree8, vfree9, vpriceauditcode, vqtunitrate, vsourcecode, vsourcerowno, vsourcetrantype, vvenddevaddr, vvendinventorycode, vvendinventoryname from po_order_b_ec, po_order_b where po_order_b.pk_order = '1001A1100000000MBQX0' and po_order_b_ec.dr = 0 and po_order_b.dr = 0 and po_order_b.pk_order_b = po_order_b_ec.pk_order_b

解决：建索引（疑似索引丢失），处理后sql到0.02秒

create index po_order_b_pk_order_dr_pk_order_b on po_order_b(pk_order,dr,pk_order_b); 

stat 100 on index po_order_b_pk_order_dr_pk_order_b; 

stat 100 on po_order_b(pk_order,dr,pk_order_b);

create index po_order_b_ec_dr_pk_order_b on po_order_b_ec(dr,pk_order_b); 

stat 100 on index po_order_b_ec_dr_pk_order_b;

stat 100 on  po_order_b_ec(dr,pk_order_b);

6、固定资产卡片保存， 这个1.7秒

update fa_log set ts=to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),redep_flag = 'Y' where log_type = null and pk_accbook = null and accyear = null and period = null and pk_org = null

待开发跟进

7、用户量上调后报错

com.yonyou.runner.exception.CheckException: 数据包<ListElement[77]>检查点校验失败！当前值为：{  "data": null,  "error": {    "exceptionClass":"nc.vo.pub.BusinessException",    "message":"nc.vo.pub.BusinessException: 多版本操作冲突过多",    "type":null,    "stack":"",    "status": 1000  },  "success": false}

达梦：把UNDO_RETENTION改小了，有可能和这个参数有关

基于回放时间是2小时。

UNDO_RETENTION=8000

MVCC_RETRY_TIMES=50

8。凭证维护保存

--凭证维护保存

select gl_detail.pk_detail, gl_detail.assid, gl_detail.bankaccount, gl_detail.checkdate, gl_detail.checkno, gl_detail.checkstyle, gl_detail.contrastflag, gl_detail.creditamount, gl_detail.creditquantity, gl_detail.debitamount, gl_detail.debitquantity, gl_detail.detailindex, gl_detail.errmessage, gl_detail.excrate1, gl_detail.excrate2, gl_detail.excrate3, gl_detail.excrate4, gl_detail.explanation, gl_detail.fraccreditamount, gl_detail.fracdebitamount, gl_detail.verifyno, gl_detail.verifydate, gl_detail.billtype, gl_detail.innerbusno, gl_detail.innerbusdate, gl_detail.localcreditamount, gl_detail.localdebitamount, gl_detail.modifyflag, gl_detail.oppositesubj, gl_detail.pk_accasoa, gl_detail.pk_org, gl_detail.pk_currtype, gl_detail.pk_innerorg, gl_detail.pk_innersob, gl_detail.pk_voucher, gl_detail.price, gl_detail.recieptclass, gl_detail.pk_accountingbook, gl_detail.pk_sourcepk, gl_detail.convertflag, gl_detail.DIRECTION, gl_detail.PREPAREDDATEV, gl_detail.YEARV, gl_detail.adjustperiod, gl_detail.PERIODV, gl_detail.PK_VOUCHERTYPEV, gl_detail.NOV, gl_detail.PK_MANAGERV, gl_detail.VOUCHERKINDV, gl_detail.ERRMESSAGE2, gl_detail.DISCARDFLAGV, gl_detail.signdatev, gl_detail.pk_systemv, gl_detail.pk_offerdetail, gl_detail.isdifflag, gl_detail.busireconno, gl_detail.errmessageh, gl_detail.pk_othercorp, gl_detail.pk_otherorgbook, gl_detail.free7, gl_detail.free8, gl_detail.free9, gl_detail.free10, gl_detail.pk_group, gl_detail.groupdebitamount, gl_detail.groupcreditamount, gl_detail.globaldebitamount, gl_detail.globalcreditamount, gl_detail.pk_org, gl_detail.pk_org_v, gl_detail.pk_unit, gl_detail.unitname, gl_detail.pk_unit_v, gl_detail.netbankflag, gl_detail.tempsaveflag, gl_dtlfreevalue.freevalue1, gl_dtlfreevalue.freevalue2, gl_dtlfreevalue.freevalue3, gl_dtlfreevalue.freevalue4, gl_dtlfreevalue.freevalue5, gl_dtlfreevalue.freevalue6, gl_dtlfreevalue.freevalue7, gl_dtlfreevalue.freevalue8, gl_dtlfreevalue.freevalue9, gl_dtlfreevalue.freevalue10, gl_dtlfreevalue.freevalue11, gl_dtlfreevalue.freevalue12, gl_dtlfreevalue.freevalue13, gl_dtlfreevalue.freevalue14, gl_dtlfreevalue.freevalue15, gl_dtlfreevalue.freevalue16, gl_dtlfreevalue.freevalue17, gl_dtlfreevalue.freevalue18, gl_dtlfreevalue.freevalue19, gl_dtlfreevalue.freevalue20, gl_dtlfreevalue.freevalue21, gl_dtlfreevalue.freevalue22, gl_dtlfreevalue.freevalue23, gl_dtlfreevalue.freevalue24, gl_dtlfreevalue.freevalue25, gl_dtlfreevalue.freevalue26, gl_dtlfreevalue.freevalue27, gl_dtlfreevalue.freevalue28, gl_dtlfreevalue.freevalue29, gl_dtlfreevalue.freevalue30, gl_detail.ts from gl_detail left outer join gl_dtlfreevalue on gl_detail.pk_detail = gl_dtlfreevalue.pk_detail where pk_voucher in ( '1001A1100000000MIBN2' ) and gl_detail.dr = 0

9。应付单管理

delete from arap_tally where pk_bill in ( '1001A1100000000MK81T' );  delete from arap_tally_agr where pk_bill in ( '1001A1100000000MK81T' );  delete from arap_tally_all where pk_bill in ( '1001A1100000000MK81T' );

2020.06.22

fa_log 固定资产卡片保存卡住 ---   索引增加log_type解决

CREATE INDEXresa_respvoucher_nxz ONresa_respvoucher(voucherno,pk_org,pk_voucher)

CREATE  INDEXI_IC_ATPL_DIM_nxz ONIC_ATP_LOCK(catplockid,ts,dr);

CREATE INDEXIDX_TMPUB_CALOG_TEMP_nxzONTMPUB_CALOG_TEMP(PK_VBILL,vbillno,islast);

CREATE INDEXpub_wf_instance_nxzONpub_wf_instance(billtype,billid,BILLVERSIONPK,PK_WF_INSTANCE,workflow_type);