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")
4 - filter("S"."KSUSENUM"=305 AND "S"."INST_ID"=USERENV('INSTANCE'))
7 - filter(USERENV('INSTANCE') IS NOT NULL)
10 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
11 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
12 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
13 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
14 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
15 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
16 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
17 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
18 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSOBFLG",1)<>0)
19 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("KSSPAFLG",1)<>0)
20 - filter("RADDR"="R"."ADDR")
23 - 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'))))))
24 - access("O"."SPARE3"="U"."USER#")
26 - access("O"."OWNER#"="U"."USER#")
28 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
"O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
29 - 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)
30 - access("I"."OBJ#"=:B1)
32 - 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')))
33 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")
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
"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 ("U"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition_name')='O
RA$BASE' OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('useren
v','current_edition_id')) OR EXISTS (SELECT 0 FROM SYS."USER$"
"U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND
"O2"."OWNER#"="U2"."USER#" AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('user
env','current_edition_id')) AND "U2"."TYPE#"=2))))
9 - filter(BITAND("O"."FLAGS",128)=0)
10 - access("O"."SPARE3"="U"."USER#" AND "O"."LINKNAME" IS NULL)
filter("O"."LINKNAME" IS NULL AND
"O"."NAME"<>'_default_auditing_options_' AND "O"."NAME"<>'_NEXT_OBJECT')
12 - access("O"."OWNER#"="U"."USER#")
13 - 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)
14 - access("I"."OBJ#"=:B1)
16 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
17 - filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
tion_id')) AND "U2"."TYPE#"=2)
18 - access("O2"."OWNER#"="U2"."USER#")
21 - access("L"."OWNER#"="U"."USER#")
22 - access("OBJECT_ID"="ID1")
filter("OBJECT_ID"="ID1")
30 - access("RADDR"="R"."ADDR")
filter("RADDR"="R"."ADDR")
33 - filter(USERENV('INSTANCE')=USERENV('INSTANCE'))
36 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
37 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
38 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
39 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
40 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
41 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
42 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
43 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
44 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0)
45 - filter("INST_ID"=USERENV('INSTANCE') AND ("KSQLKMOD"<>0 OR
"KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0)
46 - access("SADDR"="S"."ADDR")
filter("SADDR"="S"."ADDR")
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上则不会出现。

是个bug，在metalink有几篇文章的情况和这个问题类似
cbo才处理数据字典视图的时候得到了错误的执行计划