查询系统大量锁等待,是对_NEXT_OBJE的Exclusive锁,分析如下:
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/,如需转载,请注明出处,否则将追究法律责任。