行级锁引起insert操作超时

binjia_001

现有系统redhat 5.5 oracle 10.2.0.4.0 RAC
8.14开发人员反映数据库比较慢，从ASH报告中发现数据库主要消耗在行级锁上，行级锁产生是由于程序提交方式引起的，自己推理是由于大量行级锁造成shared pool不足引起，row cache lock事件，以致于insert插入时间达2分钟，造成超时，但不知道对否，请老大帮我诊断下，附件是当时的ASH和AWR报告

binjia_001

有，已经帮开发找到原因，但insert动作超时是什么原因，需要怎么确认

Maclean Liu(刘相兵

UPDATE TMSTPOS SET

TM_SMP=:1, PIN_KEY=:2, MAC_KEY=:3, BAT_NO=:4, SIG_DT=:5, DATA_KEY=:6, SGN_STS='1', DATA_KEY_TS=:7

WHERE MERC_ID=:8 AND TRM_NO=:9



"MERC_ID", "TRM_NO", "TRM_SN" => UNIQUE KEY
"ORG_NO", "TRM_NO"   => UNIQUE KEY


建议你与开发 复核下 这个UPDATE语句，应用上可能存在交叉更新

binjia_001

CREATE TABLE "TMSTPOS"
   ( "MERC_ID" VARCHAR2(15),
"TRM_NO" VARCHAR2(8),
"TRM_SN" VARCHAR2(16) DEFAULT ' ',
"MFR_NO" VARCHAR2(3) DEFAULT ' ',
"MOD_NO" VARCHAR2(4) DEFAULT ' ',
"TRM_STS" VARCHAR2(1) DEFAULT 'Y',
"SGN_STS" VARCHAR2(1) DEFAULT '0',
"TRM_OWN" VARCHAR2(1) DEFAULT '0',
"TRM_TYP" VARCHAR2(1) DEFAULT '0',
"TRM_NM" VARCHAR2(30) DEFAULT ' ',
"INS_AD" VARCHAR2(100) DEFAULT ' ',
"INS_DT" VARCHAR2(8) DEFAULT ' ',
"BEG_DT" VARCHAR2(8) DEFAULT ' ',
"PM_DW_FLG" VARCHAR2(1) DEFAULT '0',
"EMV_DW_FLG" VARCHAR2(1) DEFAULT '0',
"PER_CCY" VARCHAR2(1) DEFAULT '0',
"DPS_AMT" NUMBER(13,2) DEFAULT 0,
"CONN_MOD" VARCHAR2(3) DEFAULT ' ',
"EMV_PM_MOD" VARCHAR2(2) DEFAULT ' ',
"PRV_FLG" VARCHAR2(1) DEFAULT '0',
"RARD_CD" VARCHAR2(4) DEFAULT ' ',
"BAT_NO" VARCHAR2(6) DEFAULT ' ',
"ORG_NO" VARCHAR2(10) DEFAULT ' ',
"TRM_KEY" VARCHAR2(64) DEFAULT ' ',
"TRM_TEK" VARCHAR2(64) DEFAULT ' ',
"TRM_SEK" VARCHAR2(64) DEFAULT ' ',
"MAC_KEY" VARCHAR2(64) DEFAULT ' ',
"PIN_KEY" VARCHAR2(64) DEFAULT ' ',
"RFD_FLG" VARCHAR2(1) DEFAULT '1',
"SFT_VER" VARCHAR2(20) DEFAULT ' ',
"OFF_FLG" VARCHAR2(1) DEFAULT '0',
"SIG_DT" VARCHAR2(8) DEFAULT ' ',
"RSV_DAT" VARCHAR2(50) DEFAULT ' ',
"UPD_DT" VARCHAR2(8) DEFAULT ' ',
"UPD_TLR" VARCHAR2(10) DEFAULT ' ',
"TM_SMP" VARCHAR2(26) DEFAULT ' ',
"REG_DT" VARCHAR2(8),
"INS_TLR" VARCHAR2(30) DEFAULT '',
"OPER_MGR" VARCHAR2(30) DEFAULT '',
"MEM_DLD_TYP" VARCHAR2(2),
"CONN_TY" VARCHAR2(10) DEFAULT '',
"SIM_SN" VARCHAR2(16) DEFAULT '',
"LAI" VARCHAR2(10) DEFAULT '',
"CID" VARCHAR2(10) DEFAULT '',
"LONGITUDE" VARCHAR2(30) DEFAULT '',
"LATITUDE" VARCHAR2(30) DEFAULT '',
"ADDRESS" VARCHAR2(100) DEFAULT '',
"TEL_NO" VARCHAR2(20) DEFAULT '',
"TEL_CITY" VARCHAR2(50) DEFAULT '',
"MAC_ADDR" VARCHAR2(30) DEFAULT '',
"IP_ADDR" VARCHAR2(30) DEFAULT '',
"IP_CITY" VARCHAR2(50) DEFAULT '',
"RMK" VARCHAR2(500) DEFAULT '',
"DATA_KEY_TS" VARCHAR2(32),
"SATTLE_SMP" VARCHAR2(32),
"DATA_KEY" VARCHAR2(32),
  SUPPLEMENTAL LOG GROUP "GGS_TMSTPOS_12801" ("MERC_ID", "TRM_NO", "TRM_SN") ALWAYS
   )    TABLESPACE "TBS_POSP" ;
CREATE UNIQUE INDEX  "TMSTPOS_PK" ON "POSP"."TMSTPOS" ("MERC_ID", "TRM_NO", "TRM_SN")
    TABLESPACE "TBS_POSP_IDX" ;

  CREATE UNIQUE INDEX "TMSTPOS_UI1" ON "POSP"."TMSTPOS" ("ORG_NO", "TRM_NO")
   TABLESPACE "TBS_POSP_IDX" ;


  GRANT SELECT ON "TMSTPOS" TO "RPTADM";

  GRANT SELECT ON "TMSTPOS" TO "VAS_PLATFORM";

  GRANT SELECT ON "TMSTPOS" TO "BMP_USER";

Maclean Liu(刘相兵

enq: TX - row lock contention         105,229         51,443         489         78.2        Application

行锁争用是TOP 1 wait event

Elapsed Time (s)        CPU Time (s)        Executions        Elap per Exec (s)        % Total DB Time         SQL Id        SQL Module        SQL Text
47,735        0        189        252.57        72.55        gzt34x6x7p8r0                 UPDATE TMSTPOS SET TM_SMP=:1, ...


值得怀疑的语句是

UPDATE TMSTPOS SET TM_SMP=:1, PIN_KEY=:2, MAC_KEY=:3, BAT_NO=:4, SIG_DT=:5, DATA_KEY=:6, SGN_STS='1', DATA_KEY_TS=:7 WHERE MERC_ID=:8 AND TRM_NO=:9

Segments by Row Lock Waits
% of Capture shows % of row lock waits for each top segment compared
with total row lock waits for all segments captured by the Snapshot
Owner        Tablespace Name        Object Name        Subobject Name        Obj. Type        Row Lock Waits        % of Capture
POSP        TBS_POSP        PUBSEQREC                 TABLE        78        67.24
VAS_PLATFORM        TBS_VAS        PK_USER_RELATION                 INDEX        15        12.93
POSP        TBS_POSP        TMSTPOS                 TABLE        8        6.90


请列出表TMSTPOS  及其索引和所有约束的DDL语句