ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【故障-ORACLE】_NEXT_OBJE Exclusive锁问题分析

【故障-ORACLE】_NEXT_OBJE Exclusive锁问题分析

原创 Linux操作系统 作者:浪漫双鱼 时间:2012-01-09 17:15:21 0 删除 编辑

   查询系统大量锁等待,是对_NEXT_OBJEExclusive锁,分析如下:

 
SID SERIAL# USERNAME TERMINAL RES TAB OWNER ID1 ID2 LMODE REQUEST CTIME
158 1 UNKNOWN� XRSystem TAB$ SYS 4 0 No Lock 2500766
158 1 UNKNOWN� RSSystem PROXY_ROLE SYS 25 1 Row Share 2500746
159 1 UNKNOWN� RTSystem _NEXT_OBJE SYS 1 0 Exclusive 2500746
160 1 UNKNOWN� DMSystem _NEXT_OBJE SYS 1 0 Share 2500744
159 1 UNKNOWN� RTSystem _NEXT_OBJE SYS 1 1 Exclusive 2500746
160 1 UNKNOWN� RTSystem _NEXT_OBJE SYS 1 2 Exclusive 2500746
160 1 UNKNOWN� MRSystem _NEXT_OBJE SYS 1 0 Share 2500746
160 1 UNKNOWN� MRSystem C_OBJ# SYS 2 0 Share 2500746
160 1 UNKNOWN� MRSystem I_OBJ# SYS 3 0 Share 2500746
160 1 UNKNOWN� MRSystem TAB$ SYS 4 0 Share 2500746
160 1 UNKNOWN� MRSystem CLU$ SYS 5 0 Share 2500746
160 1 UNKNOWN� MRSystem C_TS# SYS 6 0 Share 2500746
160 1 UNKNOWN� MRSystem I_TS# SYS 7 0 Share 2500746
160 1 UNKNOWN� MRSystem C_FILE#_BL SYS 8 0 Share 2500746
160 1 UNKNOWN� MRSystem I_FILE#_BL SYS 9 0 Share 2500746
160 1 UNKNOWN� MRSystem I_NTAB3 SYS 201 0 Share 2500746
157 1 UNKNOWN� TSSystem I_OBJ# SYS 3 1 Row Exclusive 2500743
100 26292 CASKWMES pts/31 TM - DML Enqueue RATION_REM CASKWMES 106686 0 Row Exclusive 19
100 26292 CASKWMES pts/31 TM - DML Enqueue RATION_DAT CASKWMES 106658 0 Row Exclusive 10

OTN寻找到些解释:

LGWR is always holds RT lock. RT is a redo thread and it always be presented since database is up. This is a mark of healthy database.

DBWR also holds MR locks upon every datafile of database. It is also a mark of healthy database.

--from the database without big user activity
oratest> select * from v$lock;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK


--------


--


----------


----------


----------
6831B244 6831B254 2 MR 201 0 4 0 27567 0
6831B1F8 6831B208 2 MR 12 0 4 0 27567 0
6831B1AC 6831B1BC 2 MR 11 0 4 0 27567 0
6831B160 6831B170 2 MR 10 0 4 0 27567 0
6831B114 6831B124 2 MR 9 0 4 0 27567 0
6831B0C8 6831B0D8 2 MR 8 0 4 0 27567 0
6831B07C 6831B08C 2 MR 7 0 4 0 27567 0
6831B030 6831B040 2 MR 6 0 4 0 27567 0
6831AFE4 6831AFF4 2 MR 5 0 4 0 27567 0
6831AF98 6831AFA8 2 MR 4 0 4 0 27567 0
6831AF4C 6831AF5C 2 MR 3 0 4 0 27567 0
6831AF00 6831AF10 2 MR 2 0 4 0 27567 0
6831AEB4 6831AEC4 2 MR 1 0 4 0 27567 0
6831ADD0 6831ADE0 3 RT 1 0 6 0 27576 0
6831ACA0 6831ACB0 4 XR 4 0 1 0 27582 0
6831AE68 6831AE78 5 TS 2 1 3 0 27555 0

_NEXT_OBJECT is the hidden objects which generates the next object_id or data_object_id every time when new object is created or truncate is issued on existing tables. Waits on that object is the criteria of repeated object creations and/or truncates.

oratest> select object_id from user_objects where object_name = 'A1';

OBJECT_ID


26302

oratest> truncate table a1;

Table truncated.

oratest> create table a11(c1 int);

Table created.

oratest> select object_id from user_objects where object_name = 'A11';

OBJECT_ID


26304

oratest> create table a111(c1 int);

Table created.

oratest> select object_id from user_objects where object_name = 'A111';

OBJECT_ID


26305

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11088128/viewspace-714625/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-10

  • 博文量
    87
  • 访问量
    223462