Chinaunix

标题: 遇到过在CREATE MATERIALIZED VIEW LOG时产生大量的wait for table lock事件，求助 [打印本页]

作者: p60017518 时间: 2008-12-05 10:11
标题: 遇到过在CREATE MATERIALIZED VIEW LOG时产生大量的wait for table lock事件，求助
如题，在create mv log on table时产生大量的wait for table lock，查看trace文件上报
bind 3: dty=1 mxl=128(46) mal=00 scl=00 pre=00 oacflg=18 oacfl2=1 size=128 offset=0
bfp=70000050e6340f0 bln=128 avl=46 flg=09
value="snapshot log for master table HLR.C_SUB_COMMON"
WAIT #13: nam='db file sequential read' ela= 5759 p1=1 p2=11314 p3=1
WAIT #13: nam='db file sequential read' ela= 3242 p1=1 p2=325 p3=1
WAIT #13: nam='db file sequential read' ela= 5633 p1=1 p2=4746 p3=1
EXEC #13:c=0,e=15283,p=3,cr=1,cu=3,mis=0,r=1,dep=2,og=4,tim=3459835055612
EXEC #2:c=0,e=22061,p=4,cr=42,cu=3,mis=0,r=0,dep=1,og=4,tim=3459835055712
WAIT #1: nam='Wait for Table Lock' ela= 976576 p1=0 p2=0 p3=0
WAIT #1: nam='Wait for Table Lock' ela= 976575 p1=0 p2=0 p3=0
WAIT #1: nam='Wait for Table Lock' ela= 976580 p1=0 p2=0 p3=0
........
如上

作者: dingning239 时间: 2008-12-05 13:49
v$lock
v$locked_object

用这两个视图查查你所操作的表

作者: p60017518 时间: 2008-12-05 16:11
sid为14阻塞了sid为15的session，但是在sid为15中并没有看到request
SQL> select SID,type,ID1,ID2,LMODE,request from v$lock where type in('TM','TX');

   SID TY       ID1       ID2    LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
      14 TX    917544       771       6       0
      14 TM    110301       0       3       0
      15 TX    327777    2513       6       0
      15 TM       102       0       3       0
      15 TM       21       0       3       0
      15 TM       4       0       3       0
      15 TM       18       0       3       0

SQL> select l.session_id,l.XIDUSN,l.XIDSLOT,l.OBJECT_ID,l.LOCKED_MODE,o.object_name  from V$LOCKED_OBJECT l, dba_objects o where l.object_id= o.object_id;

SESSION_ID    XIDUSN XIDSLOT  OBJECT_ID LOCKED_MODE
---------- ---------- ---------- ---------- -----------
OBJECT_NAME
--------------------------------------------------------------------------------
      14       14       40    110301          3
TEST_11

      15       5       97       4          3
TAB$

      15       5       97       18          3
OBJ$

SESSION_ID    XIDUSN XIDSLOT  OBJECT_ID LOCKED_MODE
---------- ---------- ---------- ---------- -----------
OBJECT_NAME
--------------------------------------------------------------------------------
      15       5       97       102          3
COM$

      15       5       97       21          3
COL$

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)