无关的表引起的enq: TX - row lock contention

最新推荐文章于 2022-08-22 20:10:05 发布

cuiwuzha3492

最新推荐文章于 2022-08-22 20:10:05 发布

阅读量114

点赞数

文章标签： 5g

一生产库最近一个月内cpu逐渐升高，在月底时cpu几乎耗尽
  原因分析如下：
原因：
1.一生产库，从5月20号开始CPU利用率逐渐增高，在高峰时期伴随大量enq: TX - row lock contention，其对应的表都是DBORDERADM.OR_ORDER_INFO.
2.通过查询发现所有的enq: TX - row lock contention都跟语句sql_id:7gurqh8k6ywv5,sql_id:2s85nz7wyrqz6,sql_id:fwtczd409cgcb有关，并且被它们所在会话阻塞。
3.这三条语句都是对表UR_USERRESORD_MID_HIS，PD_USERRES_INFO经行全表扫。由于表的数据量不断加大并且相应的join列没有索引导致这些语句执行缓慢，因为这些语句和update语句同在一个没提交的事物里，间接的影响了update语句提交的时间，最终产生严重的enq: TX - row lock contention等待导致cpu较高。

解决方案：
1.让应用人员在module:ROrdConfirm@w4m903da (TNS V1-V3)下，找到同在一个事物的如下几条语句，在update后键入commit命令，以保证update后立马提交。

----enq: TX - row lock contention对应的语句
update OR_ORDER_INFO set ORDER_STATUS=:ORDER_STATUS , STATE_DATE=to_date(:STATE_DATE, 'YYYYMMDDHH24MISS' ) where ORDER_ID=:ORDER_ID

----阻塞会话对应语句
insert into UR_USERRESORD_MID_HIS(LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RE
S_STATE,OP_TIME, UPDATE_CODE,UPDATE_TYPE,UPDATE_LOGIN,UPDATE_DATE,UPDATE_TIME,UPDATE_ACCEPT)
select LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RES_STATE,OP_TIME,:FUNCTION_C
ODE ,decode(UPDATE_TYPE,'U','X','I','D','X','U','D','I'),:LOGIN_NO
,to_number(substr(:UPDATE_TIME ,1,8)), to_date(:UPDATE_TIME
,'YYYYMMDDHH24MISS'), :NEW_ACCEPT from UR_USERRESORD_MID_HIS where
UPDATE_ACCEPT=:OLD_ACCEPT and UPDATE_TYPE in ('D','U','I','X')

insert into UR_USERRESORD_MID(LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RES_S
ATE,OP_TIME) select LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RES_STATE,OP_TI
E from UR_USERRESORD_MID_HIS where UPDATE_ACCEPT=:OLD_ACCEPT and UPDATE_TYPE in ('D', 'U')

delete from PD_USERRES_INFO where LOGIN_ACCEPT=:OLD_ACCEPT;

2.对上述三条语句关联的表UR_USERRESORD_MID_HIS，PD_USERRES_INFO 的相关列建索引。
注：表UR_USERRESORD_MID_HIS已建索引，索引为IDX_UR_USERRESORD_HIS_ACCEPT；
后续要对表PD_USERRES_INFO的列LOGIN_ACCEPT建索引。

---------------------------------------------------------------------------附-----------------------------------------------------------------------------------------

---.查看系统中的历史会话，是在哪个对象上产生了产生了enq: TX - row lock contention等待事件？
select CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,count(*) from mash where event='enq: TX - row lock contention' group by CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK# order by count(*);
CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# COUNT(*)
------------ ------------- -------------- ----------
498861 462 244462 3
498861 462 244459 3
498845 101 81 3
498845 104 367357 3
498845 105 367973 3
498845 459 62 3
498845 539 15 3
498854 143 390469 3
498845 461 45 4
498845 101 132 4
498854 461 296 4
498861 459 242278 4
399482 102 66219 4
498854 459 187 4
399488 146 105149 5
399477 131 63 6
498845 533 47 14
498845 539 25 45
399472 113 98446 120
399472 115 50448 240

Note:大部分enq: TX - row lock contention的锁都在对象object_id=399472上。

--------查看object_id=399472具体哪个对象
select owner,object_name,object_type,object_id,data_object_id from dba_objects where object_id='399472';

OWNER OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
------------------------------ -------------------- ------------------- ---------- --------------
DBORDERADM OR_ORDER_INFO TABLE PARTITION 399472 399472

注：此对象正好跟第一部分查出的语句关联的对象一致。

-----这个时间点产生enq: TX - row lock contention的sql分布
SQL> select sql_id,count(*) from mash where event='enq: TX - row lock contention' group by sql_id order by count(*);

SQL_ID COUNT(*)
------------- ----------
9rud6uzvs5qmp 3
79nhukqm7jg91 4
2a862dqru8pkk 4
b66k5g02yk490 7
35cqbsgvscuft 79
cxx00p2k4j85g 116
7q34hfjqm2wqq 120
7xq5su92gkw35 254

-----查看sql_id对应的语句
SQL> select sql_id,sql_fulltext,ADDRESS,PLAN_HASH_VALUE,CHILD_NUMBER from v$sql where sql_id='7xq5su92gkw35';

SQL_ID SQL_FULLTEXT ADDRESS PLAN_HASH_VALUE CHILD_NUMBER
------------- -------------------------------------------------------------------------------- ---------------- --------------- ------------
7xq5su92gkw35 UPDATE OR_ORDER_INFO SET LOCK_TIME =SYSDATE WHERE ORDER_ID =:ORDER_ID 000000142D260940 575479123 4
7xq5su92gkw35 UPDATE OR_ORDER_INFO SET LOCK_TIME =SYSDATE WHERE ORDER_ID =:ORDER_ID 000000142D260940 575479123 6

select snap_id,INSTANCE_NUMBER,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot where instance_number=1 and snap_id=37981;

----查询会话被那个会话阻塞
select session_id,sql_id,event,SESSION_STATE,blocking_session,xid,current_obj#,PROGRAM,MODULE from mash where event='enq: TX - row lock contention' and current_obj#='399472';

SESSION_ID SQL_ID EVENT SESSION BLOCKING_SESSION XID CURRENT_OBJ#
---------- ------------- ----------------------------------- ------- ---------------- ---------------- ------------
13064 7q34hfjqm2wqq enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
13064 7q34hfjqm2wqq enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
13064 7q34hfjqm2wqq enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
13064 7q34hfjqm2wqq enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472
12732 7xq5su92gkw35 enq: TX - row lock contention WAITING 12337 399472

Note: 从查询结果看所有的enq: TX - row lock contention都被会话12337阻塞。

----查会话12337对应的sql_id都是如下语句
SQL> !ora plan for 2s85nz7wyrqz6

=============Fri May 30 20:45:45 CST 2014===================

Session altered.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID: for cannot be found

SQL> !ora plan 2s85nz7wyrqz6

=============Fri May 30 20:45:51 CST 2014===================

Session altered.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2s85nz7wyrqz6, child number 0
-------------------------------------
insert into UR_USERRESORD_MID(LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RES_ST
ATE,OP_TIME) select LOGIN_NO,OP_NOTE,RESOURCE_TYPE,RESOURCE_NO,LOGIN_ACCEPT,OP_CODE,RES_STATE,OP_TIM
E from UR_USERRESORD_MID_HIS where UPDATE_ACCEPT=:OLD_ACCEPT and UPDATE_TYPE in ('D', 'U')

Plan hash value: 3344594510

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 14 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID| UR_USERRESORD_MID_HIS | 26 | 3016 | 14 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_UR_USERRESORD_HIS_ACCEPT | 51 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(("UPDATE_TYPE"='D' OR "UPDATE_TYPE"='U'))
2 - access("UPDATE_ACCEPT"=:OLD_ACCEPT)

22 rows selected.

SQL> !ora plan fwtczd409cgcb

=============Fri May 30 20:45:58 CST 2014===================

Session altered.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fwtczd409cgcb, child number 0
-------------------------------------
delete from PD_USERRES_INFO where LOGIN_ACCEPT=:OLD_ACCEPT

Plan hash value: 3459678098

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | | | 2903 (100)| |
| 1 | DELETE | PD_USERRES_INFO | | | | |
|* 2 | TABLE ACCESS FULL| PD_USERRES_INFO | 1 | 76 | 2903 (1)| 00:00:35 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("LOGIN_ACCEPT"=:OLD_ACCEPT)

19 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1173542/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1173542/