enq: TM - contention 等待事件说明

今天在客户现成遇到enq: TM - contention 等待事件，做个测试学习下：

1.先模拟重现等待事件

SQL> create table type_parent as select distinct object_type from dba_objects;

Table created.

create table info_child
  2     as
  3     select object_id, object_type, object_name,owner,data_object_id
  4     from all_objects;

Table created.

SQL> alter table type_parent add constraint pk_type_parent primary key (object_type);

Table altered.

SQL>
SQL> alter table info_child add constraint pk_info_child primary key (object_id);

Table altered.

SQL> alter table info_child add constraint fk_child_type_parent
  2     foreign key (object_type) references type_parent on delete cascade;

Table altered.

SQL>
SQL>
SQL> select count(*), object_type  from info_child group by object_type;

  COUNT(*) OBJECT_TYPE
---------- -------------------
         1 EDITION
       106 INDEX PARTITION
        32 TABLE SUBPARTITION
         2 CONSUMER GROUP
       223 SEQUENCE
       107 TABLE PARTITION
         3 SCHEDULE
         1 RULE
       309 JAVA DATA
       160 PROCEDURE
        55 OPERATOR
         2 DESTINATION
         9 WINDOW
         4 SCHEDULER GROUP
      1320 PACKAGE
      1259 PACKAGE BODY
       186 LIBRARY
        19 PROGRAM
        19 RULE SET
         7 CONTEXT
       237 TYPE BODY
       837 JAVA RESOURCE
        53 XML SCHEMA
       621 TRIGGER
        14 JOB CLASS
         3 DIRECTORY
         1 MATERIALIZED VIEW
      2863 TABLE
      3843 INDEX
     28110 SYNONYM
      5143 VIEW
       303 FUNCTION
     23165 JAVA CLASS
         2 JAVA SOURCE
         9 INDEXTYPE
        10 CLUSTER
      2872 TYPE
        14 JOB
        12 EVALUATION CONTEXT

39 rows selected.


session 1
SQL> select sid from v$mystat where rownum < 2;

       SID
----------
       125

SQL>
SQL> delete from type_parent where object_type = 'SYNONYM';

1 row deleted.


session 2 --hang
SQL> delete from type_parent where object_type = 'OPERATOR';


session 3 --hang
SQL> INSERT INTO type_parent VALUES ('dbseek');   

检查等待事件：
SQL> @event_numbers
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
asynch descriptor resize                                                  1
SQL*Net message from client                                               1
pmon timer                                                                1
rdbms ipc message                                                        12
smon timer                                                                1
enq: TM - contention                                                      2
Streams AQ: qmn slave idle wait                                           1
Space Manager: slave idle wait                                            5
VKTM Logical Idle Wait                                                    1
Streams AQ: qmn coordinator idle wait                                     1
Streams AQ: waiting for time management or cleanup tasks                  1
DIAG idle wait                                                            2

12 rows selected.

有2个enq: TM - contention 等待事件

2.检查和处理思路

观察等待事件和锁的情况
SID    BLOCKER EVENT                          TYPE            LMODE    REQUEST OBJECT_NAME                    OBJECT_TYPE
---- ---------- ------------------------------ ---------- ---------- ---------- ------------------------------ -------------------
 125            SQL*Net message from client    AE                  4          0 ORA$BASE                       EDITION
 125            SQL*Net message from client    TM                  3          0 type_parent                     TABLE
 125            SQL*Net message from client    TM                  3          0 info_child                      TABLE
 125            SQL*Net message from client    TO                  3          0 SDO_GEOR_DDL__TABLE$$          TABLE
 125            SQL*Net message from client    TX                  6          0
 142        125 enq: TM - contention           AE                  4          0 ORA$BASE                       EDITION
 142        125 enq: TM - contention           TM                  3          0 type_parent                     TABLE
 142        125 enq: TM - contention           TM                  0          5 info_child                      TABLE
 145        142 enq: TM - contention           AE                  4          0 ORA$BASE                       EDITION
 145        142 enq: TM - contention           TM                  3          0 type_parent                     TABLE
 145        142 enq: TM - contention           TM                  0          3 info_child                      TABLE

11 rows selected.

观察lock的情况

 SID SPID       TY      LMODE    REQUEST      CTIME      BLOCK  SUM MODE_HELD       MODE_REQUESTED  OBJECT_NAME       OWNER       USERNAME
---- ------------------------ -- ---------- ---------- ---------- ---------- ---- --------------- --------------- ----------------------------
 125 2758       TM          3          0        273          1    1 Row-X (SX)      S/Row-X (SSX)   info_child         TEST        TEST
 125 2758       TM          3          0        273          1    1 Row-X (SX)      Row-X (SX)      info_child         TEST        TEST

SQL>@locker
lock                      lock
holder                  holder            lock                                lock   request               blocked
username            session id    SERIAL# type           id1         id2      mode      mode      BLOCK session id
------------------ ----------- ---------- ------ ----------- ----------- --------- --------- ---------- ----------
SYS                        139         11 AE             100           0         4         0          0
TEST                       125          7 AE             100           0         4         0          0
TEST                       125          7 TM           75662           0         3         0          0
TEST                       125          7 TM           75663           0         3         0          1        145
TEST                       125          7 TM           75663           0         3         0          1        142
TEST                       125          7 TO           68045           1         3         0          0
TEST                       125          7 TX          524319         693         6         0          0
TEST                       142         17 AE             100           0         4         0          0
TEST                       142         17 TM           75662           0         3         0          0
TEST                       145         55 AE             100           0         4         0          0


检查锁相关语句阻塞和等待
@locksql
BLOCKERS                                      WAITERS
--------------------------------------------- ---------------------------------------------
bloackers (125:7-TEST)-delete from type_parent waiters (145:55-TEST)-INSERT INTO type_parent
 where object_type = 'SYNONYM'                VALUES ('dbseek')

bloackers (125:7-TEST)-delete from type_parent waiters (142:17-TEST)-delete from type_parent
 where object_type = 'SYNONYM'                where object_type = 'OPERATOR'

检查表，发现表info_child有外键，但是没有外键索引

CONSTRAINT_NAME                C TABLE_NAME                     R_OWNER    R_CONSTRAINT_NAME
------------------------------ - ------------------------------ ---------- --------------------
FK_CHILD_type_parent            R info_child                      TEST       PK_type_parent
PK_info_child                   P info_child

检查索引

SQL>  select index_name,table_name,index_type from user_indexes where table_name in('type_parent','info_child');

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
SUPPLIER_PK                    SUPPLIER                       NORMAL
FK_SUPPLIER                    PRODUCT                        NORMAL
PK_type_parent                  type_parent                     NORMAL
PK_info_child                   info_child                      NORMAL


3.解决办法---对子表外键创建索引
SQL> create index fk_child_type on info_child(object_type)

By DBA老菜
=========================================================================
-- The End --