oracle 频繁 tm tx,oracle enq: TM & enq: TX 分析

最新推荐文章于 2021-04-12 21:04:27 发布

无小无

最新推荐文章于 2021-04-12 21:04:27 发布

阅读量180

点赞数

文章标签： oracle 频繁 tm tx

oracle enq: TM & enq: TX 分析

作者简介：

----------------------------------------------------

@ 孙显鹏，海天起点oracle技术专家，十年从业经验

@ 精通oracle内部原理，擅长调优和解决疑难问题

@ 致力于帮助客户解决生产中的问题，提高生产效率。

@ 爱好：书法，周易，中医。微信：sunyunyi_sun

@ 易曰：精义入神，以致用也！

@ 调优乃燮理阴阳何其难也！

-----------------------------------------------------

现象：

TOP 等待：

1：第一位：enq: TM - contention

2：第三位：enq: TX - row lock contention

查询ASH：

set lines 1200 pages 1200

col evnet for a30

col p1 for a23

col block_sess for a9

select h.session_id,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,h.p2,

blocking_inst_id||blocking_session block_sess,time_waited/100000 wait

from dba_hist_active_sess_history h

where h.sample_time>=to_date('2018-07-04:16:00','yyyy-mm-dd-hh24:mi:ss')

and h.sample_time<=to_date('2018-07-04:16:30','yyyy-mm-dd-hh24:mi:ss')

and h.event like 'enq%'

6524 dw8sgchczzj16 enq: TX - row lock contention name|mode= 458779 16656 0

54580006

---大量的 dw8sgchczzj16 enq: TX - row lock contention --这里不显示了，TX 如何处理后面附件有说明

下面是TM 锁信息：

note： BLOCK_SES 列 16364 前面的1代表实例号，应该写成1-6364，着急忘记标记！

SESSION_ID SQL_ID EVENT P1 P2 BLOCK_SES WAIT

---------- ------------- ---------------------------------------------------------------- ----------------------- ---------- --------- ----------

5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

3641 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

5244 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

5932 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

3641 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

1284 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

6798 4j9ya06f6f55d enq: TM - contention name|mode= 364840 0

544D0003

908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

908 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

5297 c6qn2qxs46qfz enq: TM - contention name|mode= 364840 16364 0

544D0004

3169 atqmkk25d9kpg enq: TX - contention name|mode= 3670034 13337 0

54580004

5976 7w586m0dhr2cd enq: TX - contention name|mode= 5308448 15896 .10316

54580004

1460 2nububwzrnc9k enq: IV - contention type|mode= 1280262987 .09024

49560005

3906 34n2zhhqmsunb enq: IV - contention type|mode= 1280262987 .01777

49560005

2608 enq: IV - contention type|mode= 1398361667 .01438

49560003

3906 34n2zhhqmsunb enq: IV - contention type|mode= 1280262987 .0097

49560005

2608 enq: IV - contention type|mode= 1398361667 .00714

49560003

425 rows selected.

TM 分析：

相关SQL：

TM-03 ----sx--ROW EXCLUSIVE WAIT

Schema Name: ACCT

SQL ID : 4j9ya06f6f55d

SQL Text : INSERT INTO ACCT_BALANCE ( ACCT_BALANCE_ID, BALANCE_TYPE_ID,

EFF_DATE, EXP_DATE, BALANCE, CYCLE_UPPER, CYCLE_LOWER,

CYCLE_UPPER_TYPE, CYCLE_LOWER_TYPE, BANK_ACCT, STATE,

STATE_DATE, ACCT_ID, SERV_ID, ITEM_GROUP_ID, OBJECT_TYPE_ID

) VALUES ( :lAcctBalanceID, :iBalanceTypeID,

TRUNC(TO_DATE((DECODE(:sEffDate, :"SYS_B_00", NULL,

:"SYS_B_01",NULL, :sEffDate)), :"SYS_B_02")),

TRUNC(TO_DATE((DECODE(:sExpDate, :"SYS_B_03", NULL,

:"SYS_B_04",NULL, :sExpDate)), :"SYS_B_05")), :lBalance,

DECODE(:lCycleUpper, -:"SYS_B_06", NULL, :lCycleUpper),

DECODE(:lCycleLower, -:"SYS_B_07", NULL, :lCycleLower),

DECODE(:sCycleUpperType, :"SYS_B_08", NULL, :sCycleUpperType),

DECODE(:sCycleLowerType, :"SYS_B_09", NULL, :sCycleLowerType),

DECODE(:sBankAcct, :"SYS_B_10", NULL, :sBankAcct), :sState,

TO_DATE(:sStateDate, :"SYS_B_11"), :lAcctID, DECODE(:lServID,

-:"SYS_B_12", NULL, :lServID), DECODE(:iAcctItemGroupID,

-:"SYS_B_13", NULL, :lAcctItemGroupID), :lObjectTypeID )

c6qn2qxs46qfz tm-04-S -请求S mode，等待，阻塞者 6364

-------------------------------------------------------------------------------

Schema Name: ACCT

SQL ID : c6qn2qxs46qfz

SQL Text : DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID

=:ACCT_BALANCE_ID

Bind Variables :

1 - (NUMBER):46471271

对象分析：

desc ACCT.ACCT_BALANCE

CREATE TABLE "ACCT"."ACCT_BALANCE"

( "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,

"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,

"EFF_DATE" DATE,

"EXP_DATE" DATE,

"BALANCE" NUMBER(16,5) NOT NULL ENABLE,

"CYCLE_UPPER" NUMBER(16,5),

"CYCLE_LOWER" NUMBER(16,5),

"CYCLE_UPPER_TYPE" VARCHAR2(3),

"CYCLE_LOWER_TYPE" VARCHAR2(3),

"BANK_ACCT" VARCHAR2(30),

"STATE" VARCHAR2(3) NOT NULL ENABLE,

"STATE_DATE" DATE NOT NULL ENABLE,

"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,

"SERV_ID" NUMBER(12,0),

"ITEM_GROUP_ID" NUMBER(9,0),

"OBJECT_TYPE_ID" NUMBER(5,0),

"UPDATE_DATE" DATE,

CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")

USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "ACCT_INDEX01" ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "ACCT_DATA01"

PARTITION BY HASH ("ACCT_ID")

(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE

TABLESPACE "ACCT_DATA01"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE

TABLESPACE "ACCT_DATA02"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE

TABLESPACE "ACCT_DATA03"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE

TABLESPACE "ACCT_DATA04"

NOCOMPRESS )

疑惑不解：

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID =:ACCT_BALANCE_ID

这个DELETE 操作怎么请求TM-4-Smode 锁呢？

一般DML操作请求TM-SX锁，SX和SX是兼容的，SX和S是不兼容的，这种情况是外键没有索引的情况下，DELETE主表会给子表添加TM-S锁，

由INSRRT 语句P2和delete语句P2相同，那么DELETE操作就直接请求该表的TM-S锁，这个P2=364840是什么对象？存在外键无索引？

今天让客户查询了对象364840：

sys@ACCTDB>select object_name,subobject_name from dba_objects where object_id=364840;

OBJECT_NAME SUBOBJECT_NAME

------------------------------ ------------------------------

BALANCE_SHARE_RULE

经查BALANCE_SHARE_RULE 是ACCT_BALANCE 表的子表。--好吧还是外键无索引导致！！

在自己本机测试：

CREATE TABLE "WOLF"."ACCT_BALANCE"

( "ACCT_BALANCE_ID" NUMBER(9,0) NOT NULL ENABLE,

"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,

"EFF_DATE" DATE,

"EXP_DATE" DATE,

"BALANCE" NUMBER(16,5) NOT NULL ENABLE,

"CYCLE_UPPER" NUMBER(16,5),

"CYCLE_LOWER" NUMBER(16,5),

"CYCLE_UPPER_TYPE" VARCHAR2(3),

"CYCLE_LOWER_TYPE" VARCHAR2(3),

"BANK_ACCT" VARCHAR2(30),

"STATE" VARCHAR2(3) NOT NULL ENABLE,

"STATE_DATE" DATE NOT NULL ENABLE,

"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,

"SERV_ID" NUMBER(12,0),

"ITEM_GROUP_ID" NUMBER(9,0),

"OBJECT_TYPE_ID" NUMBER(5,0),

"UPDATE_DATE" DATE,

CONSTRAINT "PK_P_ACCT_BALANCE" PRIMARY KEY ("ACCT_BALANCE_ID")

USING INDEX PCTFREE 10 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING

STORAGE(INITIAL 31457280 NEXT 2097152 MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TEST" ENABLE

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING

STORAGE(

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "TEST"

PARTITION BY HASH ("ACCT_ID")

(PARTITION "P_ACCT_BALANCE_1" SEGMENT CREATION IMMEDIATE

TABLESPACE "TEST"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_2" SEGMENT CREATION IMMEDIATE

TABLESPACE "TEST"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_3" SEGMENT CREATION IMMEDIATE

TABLESPACE "TEST"

NOCOMPRESS ,

PARTITION "P_ACCT_BALANCE_4" SEGMENT CREATION IMMEDIATE

TABLESPACE "TEST"

NOCOMPRESS )

INSERT INTO ACCT_BALANCE values (46471271,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)

commit;

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271

SQL> select sid,type,id1,id2,lmode,request from v$lock where type='TM';

SID TYPE ID1 ID2 LMODE REQUEST

------- -------- ---------- ---------- ---------- ----------

25 TM 86358 0 3 0

25 TM 86360 0 3 0 --TM-SX 3 级锁

添加一个外键表，且无索引：

CREATE TABLE "WOLF"."ACCT_BALANCE_REF"

( "ACCT_BALANCE_ID" NUMBER(9,0) NULL references ACCT_BALANCE(ACCT_BALANCE_ID),

"BALANCE_TYPE_ID" NUMBER(9,0) NOT NULL ENABLE,

"EFF_DATE" DATE,

"EXP_DATE" DATE,

"BALANCE" NUMBER(16,5) NOT NULL ENABLE,

"CYCLE_UPPER" NUMBER(16,5),

"CYCLE_LOWER" NUMBER(16,5),

"CYCLE_UPPER_TYPE" VARCHAR2(3),

"CYCLE_LOWER_TYPE" VARCHAR2(3),

"BANK_ACCT" VARCHAR2(30),

"STATE" VARCHAR2(3) NOT NULL ENABLE,

"STATE_DATE" DATE NOT NULL ENABLE,

"ACCT_ID" NUMBER(12,0) NOT NULL ENABLE,

"SERV_ID" NUMBER(12,0),

"ITEM_GROUP_ID" NUMBER(9,0),

"OBJECT_TYPE_ID" NUMBER(5,0),

"UPDATE_DATE" DATE )

TABLESPACE "TEST"

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271

ERROR at line 1:

ORA-02292: integrity constraint (WOLF.SYS_C0013150) violated - child record found

这种情况不不允许的，因为外键存在！

先删除外键，再删除主键：

session 1:

DELETE FROM ACCT_BALANCE_REF WHERE ACCT_BALANCE_ID=46471271

session 2:

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471271

SID TYPE ID1 ID2 LMODE REQUEST

------- -------- ---------- ---------- ---------- ----------

1 TM 86358 0 2 0

25 TM 86358 0 3 0

1 TM 86365 0 3 0

25 TM 86365 0 0 4 ---session 2 请求TM-S 4级锁

col object_name for a20

col subobject_name for a20

select l.sid,l.type,o.object_name,l.id1,l.id2,lmode,l.request from v$lock l,dba_objects o where l.id1=o.object_id and l.type='TM';

SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST

------- -------- -------------------- ---------- ---------- ---------- ----------

1 TM ACCT_BALANCE 86358 0 2 0

25 TM ACCT_BALANCE 86358 0 3 0

1 TM ACCT_BALANCE_REF 86365 0 3 0

25 TM ACCT_BALANCE_REF 86365 0 0 4

删除主表的时候需要给子表添加4级锁，此时子表由于delete已经持有3级锁，4级和3级锁不兼容

USERNAME SID SQL_ID SQL_TEXT EVENT MODULE STATE

------------ ------ --------------- ---------------------------- ---------------------------- ---------- ------------

SYS 67 517qpp3b76vrc select /*+ all_rows*/ SQL*Net message to client sqlplus@se WAITED SHORT

WOLF 25 5q8rsb0an7j4t DELETE FROM ACCT_BALANCE WHE enq: TM - contention SQL*Plus WAITING

等待事件：

enq: TM - contention

set lines 1200 pages 1200

col evnet for a30

col p1 for a23

col block_sess for a9

select h.sid,h.sql_id,h.event,h.p1text||'='||to_char(h.p1,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') p1,

BLOCKING_INSTANCE||blocking_session block_sess,SECONDS_IN_WAIT

from v$session h

where h.event like 'enq%'

SID SQL_ID EVENT P1 BLOCK_SES SECONDS_IN_WAIT

---------- --------------------------------------- -------------------- ------------------------------ --------- ---------------

25 5q8rsb0an7j4t enq: TM - contention name|mode= 11 837

544D0004

删除没有记录呢？

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=0

SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST

------- -------- -------------------- ---------- ---------- ---------- ----------

25 TM ACCT_BALANCE 86358 0 3 0

删除正在插入的数据呢？

sess1:

INSERT INTO ACCT_BALANCE values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);

INSERT INTO ACCT_BALANCE_REF values (46471273,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate);

sess2:

DELETE FROM ACCT_BALANCE WHERE ACCT_BALANCE_ID=46471273

SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST

------- -------- -------------------- ---------- ---------- ---------- ----------

1 TM ACCT_BALANCE 86360 0 3 0

1 TM ACCT_BALANCE 86358 0 3 0

25 TM ACCT_BALANCE 86358 0 3 0

1 TM ACCT_BALANCE_REF 86365 0 3 0

25 TM ACCT_BALANCE_REF 86365 0 0 4 -- 一样的

关于TM-S四级锁就明白了。

还有一个问题，为什么insert 主表给子表却添加了TM-SX 3级锁，应该TM 2才对呀？如上面ASH信息，

那么只有一种可能就是现在主表插入，继而在子表插入锁从TM2级升级为TM3级：

测试如下：

session 1：

SQL> INSERT INTO ACCT_BALANCE values (46471274,46471272,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)

2 /

1 row created.

SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST

------- -------- -------------------- ---------- ---------- ---------- ----------

24 TM ACCT_BALANCE 86360 0 3 0

24 TM ACCT_BALANCE 86358 0 3 0

24 TM ACCT_BALANCE_REF 86365 0 2 0 --子表持有TM 2级锁

session 2：

SQL> INSERT INTO ACCT_BALANCE_REF values (46471274,46471271,sysdate,sysdate,46471271,46471271,46471271,'sun','un','sun','s',sysdate,12,12,12,12,sysdate)

SQL> /

1 row created.

SID TYPE OBJECT_NAME ID1 ID2 LMODE REQUEST

------- -------- -------------------- ---------- ---------- ---------- ----------

24 TM ACCT_BALANCE 86360 0 3 0

24 TM ACCT_BALANCE 86358 0 3 0

24 TM ACCT_BALANCE_REF 86365 0 3 0 ---子表持有TM 3级锁

到此问题全部搞明白！

处理方案：

1：dump 进程6364，分析6364为什么阻塞了后面的进程，由于事故发生后处理没有得到dump信息

2：kill 6364 进程，释放资源

3：给存在外键的子表BALANCE_SHARE_RULE 外键添加索引，添加索引后delete主表会请求子表的TM SX 3级锁，SX和SX是兼容的就不会出现TM 锁等待。

为了防止系统存在外键无索引问题，可执行下面脚本进行检查: (建议执行第一个脚本)

a:执行oracle 提供脚本FKCHLK.SQL可从MOS下载---脚本太长，这里不贴了

b:执行下面脚本：

SET ECHO off

REM NAME: CHKFK.SQL

REM USAGE:"@path/chkfk"

REM --------------------------------------------------------------------------

REM This file checks the ALL users Foreign Keys no index col

REM -------------------------------------------------------------------------

REM Main text of script follows:

set lines 1200 pages 1200

col owner for a30

col table_name for a30

col CONSTRAINT_NAME for a30

col COLUMNS for a30

SELECT OWNER,

TABLE_NAME,

CONSTRAINT_NAME,

CNAME1 ||

NVL2(CNAME2, ',' || CNAME2, NULL) ||

NVL2(CNAME3, ',' || CNAME3, NULL) ||

NVL2(CNAME4, ',' || CNAME4, NULL) ||

NVL2(CNAME5, ',' || CNAME5, NULL) ||

NVL2(CNAME6, ',' || CNAME6, NULL) ||

NVL2(CNAME7, ',' || CNAME7, NULL) ||

NVL2(CNAME8, ',' || CNAME8, NULL) COLUMNS

FROM (SELECT B.OWNER,

B.TABLE_NAME,

B.CONSTRAINT_NAME,

MAX(DECODE(POSITION, 1, COLUMN_NAME, NULL)) CNAME1,

MAX(DECODE(POSITION, 2, COLUMN_NAME, NULL)) CNAME2,

MAX(DECODE(POSITION, 3, COLUMN_NAME, NULL)) CNAME3,

MAX(DECODE(POSITION, 4, COLUMN_NAME, NULL)) CNAME4,

MAX(DECODE(POSITION, 5, COLUMN_NAME, NULL)) CNAME5,

MAX(DECODE(POSITION, 6, COLUMN_NAME, NULL)) CNAME6,

MAX(DECODE(POSITION, 7, COLUMN_NAME, NULL)) CNAME7,

MAX(DECODE(POSITION, 8, COLUMN_NAME, NULL)) CNAME8,

COUNT(*) COL_CNT

FROM (SELECT OWNER,

SUBSTR(TABLE_NAME, 1, 30) TABLE_NAME,

SUBSTR(CONSTRAINT_NAME, 1, 30) CONSTRAINT_NAME,

SUBSTR(COLUMN_NAME, 1, 30) COLUMN_NAME,

POSITION

FROM DBA_CONS_COLUMNS) A,

DBA_CONSTRAINTS B

WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.OWNER=B.OWNER

AND A.OWNER NOT IN ('SYS','SYSTEM','SYSMAN','OLAPSYS','MDSYS','ORDDATA','EXFSYS','SCOTT','DBSNMP')

AND B.CONSTRAINT_TYPE = 'R'

GROUP BY B.OWNER,B.TABLE_NAME, B.CONSTRAINT_NAME) CONS

WHERE COL_CNT > ALL

(SELECT COUNT(*)

FROM DBA_IND_COLUMNS I

WHERE I.TABLE_NAME = CONS.TABLE_NAME and I.INDEX_OWNER =CONS.OWNER

AND I.COLUMN_NAME IN (CNAME1, CNAME2, CNAME3, CNAME4, CNAME5,

CNAME6, CNAME7, CNAME8)

AND I.COLUMN_POSITION <= CONS.COL_CNT

GROUP BY I.INDEX_NAME)

order by 1,2

附：

锁的介绍：

1：enq: TM - contention

关于 enq: TM - contention 锁结合下面信息：

This wait indicates time spent waiting for a TM lock. TM locks are used to coordinate activities for many base table / partition operations.

Individual Waits:

Parameters:

P1 = name|mode

P2 = object #

P3 = table/partition

name|mode

The lock name and requested mode are encoded into P1 as "name<<16 | mode". This is best seen in P1RAW (or you can convert P1 to hexadecimal).

For this wait:

name is always the ASCII for "TM" = 0x544d. --结合上面查询ASH信息对照

mode is the mode that the TM lock is being requested in.

TM locks may be requested in several different modes:

"2" = RS = Row Share wait - also known as sub-share table lock (SS)

"3" = RX = Row Exclusive wait - also known as sub-exclusive table lock (SX)

"4" = S = Share mode wait.

"5" = SRX = Share Row Exclusive wait - also known as share-subexclusive table lock (SSX)

"6" = X = eXclusive mode wait

eg: P1RAW of 544D0006 = eXclusive mode TM wait, P1RAW of 544D0004 = Shared mode TM wait.

object # (dictionary object id)

This is the dictionary object id of the object that the TM lock protects.

ie: The dictionary object with OBJECT_ID = P2

P2RAW corresponds to the ID1 part of the TM lock itself.

table/partition

Indicates ID2 of the TM lock being waited on:

"0" = wait for the normal table / partition lock

"1" = wait for the partition-wait lock

This wait thus indicates a wait for the lock "TM--"

2：enq: TX - row lock contention

关于 enq: TX - row lock contention 是由于应用设计问题导致，但是我们需要检查：

1：redo 日志写和切换正常，不要由于redo 写问题导致前台事务时间经历较长时间，加重TX锁

2：enq: TX - row lock contention 等待时间不能太长，太长说明SQL可能需要优化提高SQL执行效率

3：enq: TX - row lock contention 等待时间非常短，但是存在大量enq: TX - row lock contention说明系统事务频繁操作，需要应用控制事务！

无小无

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 频繁 tm tx,oracle enq: TM & enq: TX 分析

oracle enq: TM & enq: TX 分析作者简介：----------------------------------------------------@ 孙显鹏，海天起点oracle技术专家，十年从业经验@ 精通oracle内部原理，擅长调优和解决疑难问题@ 致力于帮助客户解决生产中的问题，提高生产效率。@ 爱好：书法，周易，中医。微信：sunyunyi_sun@ 易曰：...
复制链接

扫一扫