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

最新推荐文章于 2022-01-19 17:01:16 发布

cn_mos

最新推荐文章于 2022-01-19 17:01:16 发布

阅读量1.7k

点赞数

分类专栏： oracle db 文章标签： enq TX - row lock co 11g

本文链接：https://blog.csdn.net/cn_mos/article/details/40084465

版权

oracle db 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

今天下午接到同事电话，说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_ID
IDX_TF_B_TRADE_SUBID           TF_B_TRADE                     SUBSCRIBE_ID
IDX_TF_B_TRAD_NEXT_DEAL_TA     TF_B_TRADE                     NEXT_DEAL_TAG
PK_TF_B_TRADE                  TF_B_TRADE                     TRADE_ID
PK_TF_B_TRADE                  TF_B_TRADE                     ACCEPT_MONTH
PK_TF_B_TRADE                  TF_B_TRADE                     CANCEL_TAG
IDX_TF_B_TRADE_EXECTIME        TF_B_TRADE                     EXEC_TIME
IDX_TF_B_TRADE_ORDER_ID        TF_B_TRADE                     ORDER_ID
IDX_TF_B_TRADE_SN              TF_B_TRADE                     SERIAL_NUMBER
IDX_TF_B_TRADE_USERID          TF_B_TRADE                     USER_ID
IDX_TF_B_TRADE_CUSTID          TF_B_TRADE                     CUST_ID
IDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     TRADE_ID
IDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     NEXT_DEAL_TAG
IDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     PROVINCE_CODE
IDX_TF_B_TRADE_TRADE_DEAL_TAG  TF_B_TRADE                     CANCEL_TAG
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     ACCEPT_DATE
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     TRADE_TYPE_CODE
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     NEXT_DEAL_TAG
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     SUBSCRIBE_STATE
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     ACCEPT_MONTH
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     FINISH_DATE
IDX_TF_B_TRADE_ACCEPTDATE1     TF_B_TRADE                     TRADE_ID

22 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_TAG

SQL> 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  WHERE
trade_id = TO_NUMBER(:VTRADE_ID)    AND accept_month =
TO_NUMBER(SUBSTR(:VTRADE_ID,5,2))    AND cancel_tag = :VCANCEL_TAG

Plan 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,foregif
t=TO_NUMBER(:VFOREGIFT)+foregift,advance_pay=TO_NUMBER(:VADVANCE_PAY)+ad
vance_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_staf
f_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_trade
WHERE 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) and
accept_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，解决方法就是干掉重复的索引