11G訪問DBA_OBJECTS和V$LOCK檢視時HANG住
11g也存在訪問資料字典出現長時間等待的問題。
今天在檢查11g被鎖物件時，發現了這個問題。資料庫版本Oracle rac 11.1.0.6 for Solaris sparc64。
SQL> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Solaris: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production
下面看看造成問題的SQL：
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS
2 WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS ORA$BASE
TEST T_PARALLEL
已用時間: 00: 26: 49.82
執行計劃
----------------------------------------------------------
Plan hash value: 444070136
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 660 | 63360 | 181 (5)| 00:00:03 |
|* 1 | FILTER | | | | | |
| 2 | VIEW | DBA_OBJECTS | 65980 | 6185K| 181 (5)| 00:00:03 |
| 3 | UNION-ALL | | | | | |
|* 4 | FILTER | | | | | |
|* 5 | HASH JOIN | | 71138 | 8475K| 178 (5)| 00:00:03 |
| 6 | TABLE ACCESS FULL | USER$ | 87 | 1479 | 3 (0)| 00:00:01 |
|* 7 | HASH JOIN | | 71138 | 7294K| 174 (5)| 00:00:03 |
| 8 | INDEX FULL SCAN | I_USER2 | 87 | 2001 | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | OBJ$ | 71138 | 5696K| 172 (4)| 00:00:03 |
|* 10 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
|* 13 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | I_OBJ4 | 1 | 8 | 1 (0)| 00:00:01 |
| 15 | NESTED LOOPS | | 2 | 86 | 3 (0)| 00:00:01 |
| 16 | INDEX FULL SCAN | I_LINK1 | 2 | 52 | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 76 | 1 (100)| 00:00:01 |
|* 20 | HASH JOIN | | 1 | 57 | 1 (100)| 00:00:01 |
|* 21 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 22 | VIEW | GV$_LOCK | 10 | 250 | 0 (0)| 00:00:01 |
| 23 | UNION-ALL | | | | | |
|* 24 | FILTER | | | | | |
| 25 | VIEW | GV$_LOCK1 | 2 | 178 | 0 (0)| 00:00:01 |
| 26 | UNION-ALL | | | | | |
|* 27 | FIXED TABLE FULL | X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 28 | FIXED TABLE FULL | X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 29 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 30 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 31 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 32 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 33 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 34 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 35 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 36 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
|* 37 | FIXED TABLE FIXED INDEX | X$KSQRS (ind:1) | 1 | 19 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( EXISTS (SELECT 0 FROM SYS."X$KSQRS" "R",SYS."X$KSUSE" "S", ( (SELECT
USERENV('INSTANCE') "INST_ID","LADDR" "LADDR","KADDR" "KADDR","SADDR" "SADDR","RADDR"
"RADDR","LMODE" "LMODE","REQUEST" "REQUEST","CTIME" "CTIME","BLOCK" "BLOCK" FROM (
(SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
"SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
"CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KDNSSF" "X$KDNSSF" WHERE ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0) UNION ALL
(SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
"SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
"CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KSQEQ" "X$KSQEQ" WHERE ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) "GV$_LOCK1"
WHERE USERENV('INSTANCE') IS NOT NULL) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR"
"LADDR","KSQLKADR" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
"LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTADM"
"X$KTADM" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFIL" "X$KTATRFIL" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFSL" "X$KTATRFSL" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATL" "X$KTATL" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSC" "X$KTSTUSC" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSS" "X$KTSTUSS" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
"KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
"REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSG" "X$KTSTUSG" WHERE
("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0) UNION ALL (SELECT "INST_ID" "INST_ID","KTCXBXBA"
"LADDR","KTCXBLKP" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
"LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTCXB"
"X$KTCXB" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSPAFLG",1)<>0)) "GV$_LOCK" WHERE "SADDR"="S"."ADDR" AND "S"."KSUSENUM"=305 AND
"S"."INST_ID"=USERENV('INSTANCE') AND "R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR"))
4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND (SELECT 1 FROM
"SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
"O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
"O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
"O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
"O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
"O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
"U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
5 - access("O"."SPARE3"="U"."USER#")
7 - access("O"."OWNER#"="U"."USER#")
9 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
"O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
10 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
"I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
11 - access("I"."OBJ#"=:B1)
13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
tion_id')))
filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
tion_id')))
14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
18 - access("L"."OWNER#"="U"."USER#")
20 - access("SADDR"="S"."ADDR")
21 - filter("S"."KSUSENUM"=305 AND "S"."INST_ID"=USERENV('INSTANCE'))
24 - filter(USERENV('INSTANCE') IS NOT NULL)
27 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
28 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
29 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
30 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
31 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
32 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
33 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
34 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
35 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
36 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSPAFLG",1)<>0)
37 - filter("R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR")
統計資訊
----------------------------------------------------------
15 recursive calls
70632 db block gets
982 consistent gets
3 physical reads
0 redo size
667 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
感覺似乎並不是常見的Oracle將VIEW進行MERGE導致的執行計劃效率低的問題，從現有的執行計劃上看，兩個檢視並沒有被MERGE。
不過從統計資訊上看，就存在很大問題了，這麼簡單的一個查詢怎麼會導致了7萬多的db block gets呢。
而且在這個會話的執行過程中，檢查了會話的等待時間，發現也比較有意思，前後出現了gc cr request、latch free、db file sequential read等多種等待事件。
而如果改變一下SQL語句的寫法，直接寫成關聯的方式：
SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS, V$LOCK
2 WHERE OBJECT_ID = ID1
3 AND SID = 305;
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS ORA$BASE
TEST T_PARALLEL
已用時間: 00: 00: 00.44
執行計劃
----------------------------------------------------------
Plan hash value: 3416262628
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 159 | 182 (5)| 00:00:03 |
|* 1 | HASH JOIN | | 1 | 159 | 182 (5)| 00:00:03 |
| 2 | NESTED LOOPS | | 1 | 63 | 1 (100)| 00:00:01 |
|* 3 | HASH JOIN | | 1 | 44 | 1 (100)| 00:00:01 |
|* 4 | FIXED TABLE FULL | X$KSUSE | 1 | 32 | 0 (0)| 00:00:01 |
| 5 | VIEW | GV$_LOCK | 10 | 120 | 0 (0)| 00:00:01 |
| 6 | UNION-ALL | | | | | |
|* 7 | FILTER | | | | | |
| 8 | VIEW | GV$_LOCK1 | 2 | 152 | 0 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | |
|* 10 | FIXED TABLE FULL | X$KDNSSF | 1 | 102 | 0 (0)| 00:00:01 |
|* 11 | FIXED TABLE FULL | X$KSQEQ | 1 | 102 | 0 (0)| 00:00:01 |
|* 12 | FIXED TABLE FULL | X$KTADM | 1 | 102 | 0 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KTATRFIL | 1 | 102 | 0 (0)| 00:00:01 |
|* 14 | FIXED TABLE FULL | X$KTATRFSL | 1 | 102 | 0 (0)| 00:00:01 |
|* 15 | FIXED TABLE FULL | X$KTATL | 1 | 102 | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FULL | X$KTSTUSC | 1 | 102 | 0 (0)| 00:00:01 |
|* 17 | FIXED TABLE FULL | X$KTSTUSS | 1 | 102 | 0 (0)| 00:00:01 |
|* 18 | FIXED TABLE FULL | X$KTSTUSG | 1 | 102 | 0 (0)| 00:00:01 |
|* 19 | FIXED TABLE FULL | X$KTCXB | 1 | 102 | 0 (0)| 00:00:01 |
|* 20 | FIXED TABLE FIXED INDEX | X$KSQRS (ind:1) | 1 | 19 | 0 (0)| 00:00:01 |
| 21 | VIEW | DBA_OBJECTS | 65980 | 6185K| 181 (5)| 00:00:03 |
| 22 | UNION-ALL | | | | | |
|* 23 | FILTER | | | | | |
|* 24 | HASH JOIN | | 71138 | 8475K| 178 (5)| 00:00:03 |
| 25 | TABLE ACCESS FULL | USER$ | 87 | 1479 | 3 (0)| 00:00:01 |
|* 26 | HASH JOIN | | 71138 | 7294K| 174 (5)| 00:00:03 |
| 27 | INDEX FULL SCAN | I_USER2 | 87 | 2001 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | OBJ$ | 71138 | 5696K| 172 (4)| 00:00:03 |
|* 29 | TABLE ACCESS BY INDEX ROWID| IND$ | 1 | 8 | 2 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | I_IND1 | 1 | | 1 (0)| 00:00:01 |
| 31 | NESTED LOOPS | | 1 | 28 | 2 (0)| 00:00:01 |
|* 32 | INDEX FULL SCAN | I_USER2 | 1 | 20 | 1 (0)| 00:00:01 |
|* 33 | INDEX RANGE SCAN | I_OBJ4 | 1 | 8 | 1 (0)| 00:00:01 |
| 34 | NESTED LOOPS | | 2 | 86 | 3 (0)| 00:00:01 |
| 35 | INDEX FULL SCAN | I_LINK1 | 2 | 52 | 1 (0)| 00:00:01 |
| 36 | TABLE ACCESS CLUSTER | USER$ | 1 | 17 | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | I_USER# | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_ID"="R"."KSQRSID1")
3 - access("SADDR"="S"."ADDR")
.
.
.
37 - access("L"."OWNER#"="U"."USER#")
統計資訊
----------------------------------------------------------
15 recursive calls
3 db block gets
977 consistent gets
0 physical reads
0 redo size
667 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
這個執行時間和統計資訊是正常的，透過對比也可以發現，第一個查詢的執行計劃確實存在問題。
SQL> SELECT /*+ RULE */ OWNER, OBJECT_NAME FROM DBA_OBJECTS
2 WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);
OWNER OBJECT_NAME
------------------------------ ------------------------------
SYS ORA$BASE
TEST T_PARALLEL
已用時間: 00: 00: 01.31
執行計劃
----------------------------------------------------------
Plan hash value: 2735497195
----------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | MERGE JOIN | |
| 2 | SORT JOIN | |
| 3 | VIEW | DBA_OBJECTS |
| 4 | UNION-ALL | |
|* 5 | FILTER | |
| 6 | NESTED LOOPS | |
| 7 | NESTED LOOPS | |
| 8 | TABLE ACCESS FULL | USER$ |
|* 9 | TABLE ACCESS BY INDEX ROWID| OBJ$ |
|* 10 | INDEX RANGE SCAN | I_OBJ5 |
| 11 | TABLE ACCESS CLUSTER | USER$ |
|* 12 | INDEX UNIQUE SCAN | I_USER# |
|* 13 | TABLE ACCESS BY INDEX ROWID | IND$ |
|* 14 | INDEX UNIQUE SCAN | I_IND1 |
| 15 | NESTED LOOPS | |
|* 16 | INDEX RANGE SCAN | I_OBJ4 |
|* 17 | TABLE ACCESS CLUSTER | USER$ |
|* 18 | INDEX UNIQUE SCAN | I_USER# |
| 19 | NESTED LOOPS | |
| 20 | TABLE ACCESS FULL | USER$ |
|* 21 | INDEX RANGE SCAN | I_LINK1 |
|* 22 | SORT JOIN | |
| 23 | VIEW | VW_NSO_1 |
| 24 | SORT UNIQUE | |
| 25 | MERGE JOIN | |
| 26 | SORT JOIN | |
| 27 | MERGE JOIN | |
| 28 | SORT JOIN | |
| 29 | FIXED TABLE FULL | X$KSQRS |
|* 30 | SORT JOIN | |
| 31 | VIEW | GV$_LOCK |
| 32 | UNION-ALL | |
|* 33 | FILTER | |
| 34 | VIEW | GV$_LOCK1 |
| 35 | UNION-ALL | |
|* 36 | FIXED TABLE FULL | X$KDNSSF |
|* 37 | FIXED TABLE FULL | X$KSQEQ |
|* 38 | FIXED TABLE FULL | X$KTADM |
|* 39 | FIXED TABLE FULL | X$KTATRFIL |
|* 40 | FIXED TABLE FULL | X$KTATRFSL |
|* 41 | FIXED TABLE FULL | X$KTATL |
|* 42 | FIXED TABLE FULL | X$KTSTUSC |
|* 43 | FIXED TABLE FULL | X$KTSTUSS |
|* 44 | FIXED TABLE FULL | X$KTSTUSG |
|* 45 | FIXED TABLE FULL | X$KTCXB |
|* 46 | SORT JOIN | |
|* 47 | FIXED TABLE FULL | X$KSUSE |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND
(SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1
OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
.
.
.
47 - filter("S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSUSENUM"=305)
Note
-----
- rule based optimizer used (consider using cbo)
統計資訊
----------------------------------------------------------
15 recursive calls
3 db block gets
124231 consistent gets
0 physical reads
0 redo size
667 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
2 rows processed
和其他類似情況一樣，新增RULE的hint能避免問題的產生。
問題在10.2.0.3上也可以再現，但是在9204上則不會出現。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-438592/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- EXP Or EXPDP時hang住問題，MOS解決方案
- asm例項查詢asm相關檢視hang住解決方法ASM
- 導數時資料庫hang住分析資料庫
- “rebuild index online hang住" 問題解析RebuildIndex
- Websphere Web訪問埠號檢視和修改Web
- v$sql檢視和v$sqlarea檢視的構建SQL
- 利用nid工具修改db_name時hang住
- oracle hang住的時候怎麼登陸呢？Oracle
- MySQL：kill和show命令hang住一列MySql
- 11g檢視dba_objects中增加了1個有用的欄位namespaceObjectnamespace
- Oracle動態效能檢視學習之v$lock & v$locked_objectOracleObject
- 授權某使用者，檢視動態效能檢視的許可權（如v$latch,v$lock,v$sqlarea,v$sql,v$sysstat）SQL
- AIX定時刪除歸檔日誌時系統HANG住AI
- 透過v$wait_chains檢視診斷資料庫hang和ContentionAI資料庫
- shutdown命令被job程式hang住
- Oracle 19.3資料庫impdp匯入view時hang住Oracle資料庫View
- 19c資料庫impdp匯入view時hang住資料庫View
- 資料泵匯入分割槽表長時間HANG住
- v$sesstat,v$mystat,v$statname和v$sysstat檢視簡介
- 【檢視】oracle 資料字典檢視之 DBA_OBJECTS / ALL_OBJECTS / USER_OBJECTS（OBJ）OracleObject
- 資料庫hang住，分析處理資料庫
- shutdown命令被job程式hang住(續)
- 中止程式導致系統HANG住
- nginx 檢視每秒有多少訪問量Nginx
- ORACLE DBA必須記住的常用SQL命令和檢視OracleSQL
- nginx檢視實時日誌並設簡單的訪問驗證Nginx
- oracle僵死會話鎖住buffer，導致資料庫hang住Oracle會話資料庫
- Oracle的V$檢視和DBA_檢視的參考提示Oracle
- 檢視硬碟IO訪問負荷的方法硬碟
- 如何檢視Linux 當前訪問ipLinux
- ORACLE V$lock檢視TYPE,ID1,ID2取值的含義Oracle
- 資料庫異常hang住解決資料庫
- v$session 檢視Session
- Oracle 11g中v$session檢視server列的含義OracleSessionServer
- v$session_wait和v$session_event檢視SessionAI
- v$sesstat檢視和自開發工具
- 11g新動態效能檢視V$SQL_MONITOR,V$SQL_PLAN_MONITORSQL
- “no_merge”hints優化檢視訪問低效問題優化