Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

DbWatcher

一个守望数据库的老菜鸟

  • 博客访问: 49235
  • 博文数量: 60
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 639
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
  • 认证徽章:
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(60)

文章存档

2017年(36)

2016年(24)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
enq: TM - contention 等待事件 2017-12-03 18:03:41

分类: Oracle

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址:http://blog.chinaunix.net/uid-31396856-id-5780538.html


Oracle中的enq: TM - contention 等待事件,是事务中锁表,属于表级锁的争用。如果数据库发生,是很严重的事情。一般引起TM-contention事件的情况如下:
 (1)索引外键(foreign key)使用不当会导致TM锁争用等。
 (2)DML与DDL之间的TM争用。
 (3)LOCK TABLE 引起的TM锁争用。
 (4)执行Direct LOAD/ insert/*+*append/ into /Parallel Load等操作时引起的TM锁争用。
(5)分析表统计信息或validate structure时获取TM锁可能会阻塞事务。

1.重现等待事件

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

Table created.

SQL>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 ('dbwatcher');   

检查等待事件:
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
DIAG idle wait                                                             2

12 rows selected.

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


2.检查和处理思路

找到TM争用的数据库对象,id1作为数据库对象object_id来确认数据库争用的到底是什么对象,之后我们可以确定具体如何减少这些争用,具体到SQL。

观察等待事件和锁的情况
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)

--The end!

阅读(61) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册