解决enq: TX - row lock contention的性能故障

来源：互联网发布：董小飒淘宝店编辑：程序博客网时间：2024/05/17 05:16

今天下午接到同事电话，说Tuxedo中间件有点堵，让看一下数据库有没有问题！于是马上抓了一个AWR：

下面记录一下处理的经过：

1 首先，从报告头中看到DB Time达到135分钟，(DB Time)/Elapsed=4.5，这个比值在这个时间段来说有点偏高：

2，看到Top 1是enq: TX - row lock contention等待事件，也就是说过去半个小时发生了比较严重的行级锁等待事件。

通常，产生enq: TX - row lock contention事件的原因有以下几种可能：

不同的session更新或删除同一条记录；
唯一索引有重复索引；
位图索引同时被更新或同时并发的向位图索引字段上插入相同字段值；
并发的对同一个数据块上的数据进行update操作；
等待索引块完成分裂；

3,于是又做了一个ADDM，发现都是在tf_b_trade表发生的阻塞

4，查看tf_b_trade表的索引，可以看到主键索引trade_id在别的索引中也存在，于是可以判断唯一索引有重复索引导致。

  1* select index_name,table_name,column_name from dba_ind_columns where table_name='TF_B_TRADE'SQL> /INDEX_NAME                     TABLE_NAME                     COLUMN_NAME------------------------------ ------------------------------ ----------------------------------------IDX_TF_B_TRADE_SORDER_ID       TF_B_TRADE                     SUB_ORDER_IDIDX_TF_B_TRADE_SUBID           TF_B_TRADE                     SUBSCRIBE_IDIDX_TF_B_TRAD_NEXT_DEAL_TA     TF_B_TRADE                     NEXT_DEAL_TAGPK_TF_B_TRADE                  TF_B_TRADE                     TRADE_IDPK_TF_B_TRADE                  TF_B_TRADE                     ACCEPT_MONTHPK_TF_B_TRADE                  TF_B_TRADE                     CANCEL_TAGIDX_TF_B_TRADE_EXECTIME        TF_B_TRADE                     EXEC_TIMEIDX_TF_B_TRADE_ORDER_ID        TF_B_TRADE                     ORDER_IDIDX_TF_B_TRADE_SN              TF_B_TRADE                     SERIAL_NUMBERIDX_TF_B_TRADE_USERID          TF_B_TRADE                     USER_IDIDX_TF_B_TRADE_CUSTID          TF_B_TRADE                     CUST_IDIDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     TRADE_IDIDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     NEXT_DEAL_TAGIDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     PROVINCE_CODEIDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     CANCEL_TAGIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     ACCEPT_DATEIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     TRADE_TYPE_CODEIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     NEXT_DEAL_TAGIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     SUBSCRIBE_STATEIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     ACCEPT_MONTHIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     FINISH_DATEIDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     TRADE_ID22 rows selected.SQL>

5,从被阻塞的3条sql的执行计划也可以看出使用的不是同一个索引，这就导致两个索引同时被更新

         SQL statement with SQL_ID 3v4r3wuaq5aun.         UPDATE tf_b_trade         SET next_deal_tag = :VNEXT_DEAL_TAG         WHERE trade_id = TO_NUMBER(:VTRADE_ID)         AND accept_month = TO_NUMBER(SUBSTR(:VTRADE_ID,5,2))         AND cancel_tag = :VCANCEL_TAGSQL> select * from table(dbms_xplan.display_cursor('3v4r3wuaq5aun'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  3v4r3wuaq5aun, child number 0-------------------------------------UPDATE tf_b_trade    SET next_deal_tag = :VNEXT_DEAL_TAG  WHEREtrade_id = TO_NUMBER(:VTRADE_ID)    AND accept_month =TO_NUMBER(SUBSTR(:VTRADE_ID,5,2))    AND cancel_tag = :VCANCEL_TAGPlan hash value: 1151300635------------------------------------------------------------------------------------| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |------------------------------------------------------------------------------------|   0 | UPDATE STATEMENT   |               |       |       |     3 (100)|          ||   1 |  UPDATE            | TF_B_TRADE    |       |       |            |          ||*  2 |   INDEX UNIQUE SCAN| PK_TF_B_TRADE |     1 |    36 |     2   (0)| 00:00:01 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("TRADE_ID"=TO_NUMBER(:VTRADE_ID) AND              "ACCEPT_MONTH"=TO_NUMBER(SUBSTR(:VTRADE_ID,5,2)) AND              "CANCEL_TAG"=:VCANCEL_TAG)23 rows selected.SQL> SQL statement with SQL_ID dqmrfpwmrcs0b.UPDATE uop_crm7.tf_b_trade   SET oper_fee     = TO_NUMBER(:VOPER_FEE) + oper_fee,       foregift     = TO_NUMBER(:VFOREGIFT) + foregift,       advance_pay  = TO_NUMBER(:VADVANCE_PAY) + advance_pay,       fee_state    = :VFEE_STATE,       fee_time     = NVL(TO_DATE(:VFEE_TIME, 'YYYY-MM-DD HH24:MI:SS'),                          accept_date),       fee_staff_id = NVL(:VFEE_STAFF_ID, trade_staff_id) WHERE trade_id = TO_NUMBER(:VTRADE_ID);SQL> select * from table(dbms_xplan.display_cursor('dqmrfpwmrcs0b'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  dqmrfpwmrcs0b, child number 0-------------------------------------UPDATE tf_b_trade    SET oper_fee=TO_NUMBER(:VOPER_FEE)+oper_fee,foregift=TO_NUMBER(:VFOREGIFT)+foregift,advance_pay=TO_NUMBER(:VADVANCE_PAY)+advance_pay,fee_state=:VFEE_STATE,fee_time=NVL(TO_DATE(:VFEE_TIME,'YYYY-MM-DD HH24:MI:SS'),accept_date),fee_staff_id=NVL(:VFEE_STAFF_ID,trade_staff_id)    WHERE trade_id=TO_NUMBER(:VTRADE_ID)Plan hash value: 92014494---------------------------------------------------------------------------------------------------| Id  | Operation         | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------------------------------|   0 | UPDATE STATEMENT  |                               |       |       |     3 (100)|          ||   1 |  UPDATE           | TF_B_TRADE                    |       |       |            |          ||*  2 |   INDEX RANGE SCAN| IDX_TF_B_TRADE_TRADE_DEAL_TAG |     1 |    44 |     2   (0)| 00:00:01 |---------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("TRADE_ID"=TO_NUMBER(:VTRADE_ID))23 rows selected.SQL statement with SQL_ID dqumyxvq37fb5.DELETE FROM tf_b_tradeWHERE trade_id=TO_NUMBER(:VTRADE_ID)and accept_month = TO_NUMBER(SUBSTR(:VTRADE_ID,5,2))SQL> select * from table(dbms_xplan.display_cursor('dqumyxvq37fb5'));PLAN_TABLE_OUTPUT------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------SQL_ID  dqumyxvq37fb5, child number 0-------------------------------------DELETE FROM tf_b_trade  WHERE trade_id=TO_NUMBER(:VTRADE_ID) andaccept_month = TO_NUMBER(SUBSTR(:VTRADE_ID,5,2))Plan hash value: 68369797-----------------------------------------------------------------------------------| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------|   0 | DELETE STATEMENT  |               |       |       |     3 (100)|          ||   1 |  DELETE           | TF_B_TRADE    |       |       |            |          ||*  2 |   INDEX RANGE SCAN| PK_TF_B_TRADE |     1 |   106 |     3   (0)| 00:00:01 |-----------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("TRADE_ID"=TO_NUMBER(:VTRADE_ID) AND              "ACCEPT_MONTH"=TO_NUMBER(SUBSTR(:VTRADE_ID,5,2)))21 rows selected.SQL>

6，解决方法就是干掉重复的索引

0 0