(FAQ)往来通知单操作造成大量的行锁等待，造成系统整体慢
原因分析： 1.T_CSL_RequisitionEntry表中记录数据较多(几百万条数据)，且用了in 子查询与or 条件后走的是全表扫描。 2.where条件中 fid in (..中T_CSL_RequisitionEntry表出现过两次，造成热点争用，同时fid in (.. ) or fid in (...)，外关联传入一条记录，两个内关联子查询分别要执行一次，从而需从缓存中获取更多的数据块。 3.热点块争用造成以下SGA内部锁 latch: cache buffers chains 解决方法： 1.经与总账的研发人员涂工沟通，最后确定由实施人员新提一个单到总账模块，由总账人员发私包处理该问题。解锁是暂时处理问题的办法，改造语句才是从根本上解决问题的最好办法。 2.建议语句改造时，update … fid in (..) or fid in (…),分成两个update来写，将or 进行分开,避免全表扫描，in 可改写为exists,减少该语句的执行速度。 性能表现： Top User Events显示了如下的信息: Top User Events Event Event Class % Activity Avg Active Sessions enq: TX - row lock contention Application 80.72 81.37 CPU + Wait for CPU CPU 11.62 11.72 latch: cache buffers chains Concurrency 4.79 4.83 enq: TX - allocate ITL entry Configuration 2.10 2.12 SQL语句如下所示: UPDATE T_CSL_RequisitionEntry SET Fsummary = Fsummary WHERE (fid IN (SELECT FSrcRequisitionEntryID FROM (SELECT b.FID FSRCREQUISITIONENTRYID, a.FID FSRCREQUISITIONID, b.FAssistRecordID FSRCASSISTRECORDID, d.FID FTARGREQUISITIONENTRYID, c.FID FTARGREQUISITIONID, d.FAssistRecordID FTARGASSISTRECORDID, a.FVchNumber FSRCVCHNUMBER, c.FVchNumber FTARGVCHNUMBER, NEWBOSID('5C45F8FF') FCHECKNUMBER FROM T_CSL_Requisition A, T_CSL_RequisitionEntry B, T_CSL_Requisition C, T_CSL_RequisitionEntry D, T_GL_VoucherAssistRecord ASSTSRC, T_GL_VoucherAssistRecord ASSTTARG, t_gl_voucher SRCVOUCHER, t_bd_period SRCPI, t_gl_voucher TARGVOUCHER, t_bd_period TARGPI WHERE (((((((((((((((((((a.FID = b.FRequisitionID AND c.FID = d.FRequisitionID) AND b.FStatus = :1) AND d.FStatus = b.FStatus) AND a.FSrcCompanyID = c.FTargCompanyID) AND a.FTargCompanyID = c.FSrcCompanyID) AND b.FCurrencyID = d.FCurrencyID) AND ((b.FOriginalAmount = d.FOriginalAmount AND (b.FDc <> d.FDc)) OR (b.FOriginalAmount = (d.FOriginalAmount * -1) AND b.FDc = d.FDc))) AND b.FAssistRecordID = asstSrc.FID) AND d.FAssistRecordID = asstTarg.FID) AND a.FSrcCompanyID = :2) AND a.FIsAvoidCheck = 0) AND srcvoucher.fid = a.FVoucherID) AND srcpi.fid = srcvoucher.fperiodid) AND targvoucher.fid = c.FVoucherID) AND targpi.fid = targvoucher.fperiodid) AND srcpi.fperiodYear = :3) AND srcpi.fperiodNumber = :4) AND targpi.fperiodYear = :5) AND targpi.fperiodNumber = :6)) A) OR fid IN (SELECT FTargRequisitionEntryID FROM (SELEC T b.FID FSRCREQUISITIONENTRYID, a.FID FSRCREQUISITIONID, b.FAssistRecordID FSRCASSISTRECORDID, d.FID FTARGREQUISITIONENTRYID, c.FID FTARGREQUISITIONID, d.FAssistRecordID FTARGASSISTRECORDID, a.FVchNumber FSRCVCHNUMBER, c.FVchNumber FTARGVCHNUMBER, NEWBOSID('5C45F8FF') FCHECKNUMBER FROM T_CSL_Requisition A, T_CSL_RequisitionEntry B, T_CSL_Requisition C, T_CSL_RequisitionEntry D, T_GL_VoucherAssistRecord ASSTSRC, T_GL_VoucherAssistRecord ASSTTARG, t_gl_voucher SRCVOUCHER, t_bd_period SRCPI, t_gl_voucher TARGVOUCHER, t_bd_period TARGPI WHERE (((((((((((((((((((a.FID = b.FRequisitionID AND c.FID = d.FRequisitionID) AND b.FStatus = :7) AND d.FStatus = b.FStatus) AND a.FSrcCompanyID = c.FTargCompanyID) AND a.FTargCompanyID = c.FSrcCompanyID) AND b.FCurrencyID = d.FCurrencyID) AND ((b.FOriginalAmount = d.FOriginalAmount AND (b.FDc <> d.FDc)) OR (b.FOriginalAmount = (d.FOriginalAmount * -1) AND b.FDc = d.FDc))) AND b.FAssistRecordID = asstSrc.FID) AND d.FAssistRecordID = asstTarg.FID) AND a.FSrcCompanyID = :8) AND a.FIsAvoidCheck = 0) AND srcvoucher.fid = a.FVoucherID) AND srcpi.fid = srcvoucher.fperiodid) AND targvoucher.fid = c.FVoucherID) AND targpi.fid = targvoucher.fperiodid) AND srcpi.fperiodYear = :9) AND srcpi.fperiodNumber = :10) AND targpi.fperiodYear = :11) AND targpi.fperiodNumber = :12)) B)) 关键字 往来通知单