近一个客户反映数据库在执行远程INSERT的时候应用反映很慢,在做AWR后发现enq: TM - contention等待事件很高,应该是外键约束上没有建立索引的问题。下面我来做个实验还原一下当时的现象。
CREATE TABLE supplier ( supplier_id number(10) not null, supplier_name varchar2(50) not null, contact_name varchar2(50), CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) );
INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1');INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2');COMMIT;
CREATE TABLE product ( product_id number(10) not null, product_name varchar2(50) not null, supplier_id number(10) not null, CONSTRAINT fk_supplier FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id) ON DELETE CASCADE );
INSERT INTO product VALUES (1, 'Product 1', 1);INSERT INTO product VALUES (2, 'Product 2', 1);INSERT INTO product VALUES (3, 'Product 3', 2);COMMIT;
然后去执行几条相关的语句User 1: DELETE supplier WHERE supplier_id = 1;User 2: DELETE supplier WHERE supplier_id = 2;(现象HANG住)User 3: INSERT INTO supplier VALUES (5, 'Supplier 5', 'Contact 5');(现象HANG住)
现在检查锁的情况
col event format a20col type format a10col object_name a15col object_type a15SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, l.request, o.object_name, o.object_typeFROM v$lock l, dba_objects o, v$session sWHERE UPPER(s.username) = UPPER('&User')AND l.id1 = o.object_id (+)AND l.sid = s.sidORDER BY sid, type;输入 user 的值: test原值 3: WHERE UPPER(s.username) = UPPER('&User')新值 3: WHERE UPPER(s.username) = UPPER('test')
SID BLOCKER EVENT TYPE LMODE REQUEST---------- ---------- -------------------- ---------- ---------- ----------OBJECT_NAME OBJECT_TYPE--------------- --------------- 144 158 enq: TM - contention TM 3 0SUPPLIER TABLE
144 158 enq: TM - contention TM 0 2PRODUCT TABLE
153 SQL*Net message from TM 3 0 clientSUPPLIER TABLE
SID BLOCKER EVENT TYPE LMODE REQUEST---------- ---------- -------------------- ---------- ---------- ----------OBJECT_NAME OBJECT_TYPE--------------- ---------------
153 SQL*Net message from TM 3 0 clientPRODUCT TABLE
153 SQL*Net message from TX 6 0 client
SID BLOCKER EVENT TYPE LMODE REQUEST---------- ---------- -------------------- ---------- ---------- ----------OBJECT_NAME OBJECT_TYPE--------------- --------------- 158 153 enq: TM - contention TM 0 5PRODUCT TABLE
158 153 enq: TM - contention TM 3 0SUPPLIER TABLE
已选择7行。
SQL>
能看到enq: TM - contention TM 等待事件
检查没有索引的外键
SELECT * FROM ( SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' MINUS SELECT i.table_name, ic.column_name, ic.column_position FROM user_indexes i, user_ind_columns ic WHERE i.index_name = ic.index_name )ORDER BY table_name, column_position;TABLE_NAME------------------------------COLUMN_NAME--------------------------------------------------------------------------------
COLUMN_POSITION---------------PRODUCTSUPPLIER_ID 1
建立索引后:
CREATE INDEX fk_supplier ON product (supplier_id);
INSERT INTO supplier VALUES (6, 'Supplier 6', 'Contact 6');
INSERT INTO supplier VALUES (7, 'Supplier 7', 'Contact 7');
User 1: DELETE supplier WHERE supplier_id = 6;User 2: DELETE supplier WHERE supplier_id = 7;User 3: INSERT INTO supplier VALUES (8, 'Supplier 8', 'Contact 8');
相关的等待事件消失。