一生產庫最近一個月內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.

無關的表引起的enq: TX - row lock contention

相關文章