我申请这个blog是为了督促自己，把自己平时的一些想法和思考结果保留下来。 本博客所有内容均为原创，如有转载请注明作者和出处

11G访问DBA_OBJECTS和V$LOCK视图时HANG住

上一篇 / 下一篇  2008-08-27 23:56:26 / 个人分类：Bug

查看( 288 ) / 评论( 1 ) / 评分( 0 / 0 )

11g也存在访问数据字典出现长时间等待的问题。

 

 

今天在检查11g被锁对象时，发现了这个问题。数据库版本Oracle rac 11.1.0.6 for Solaris sparc64。

SQL> SELECT * FROM V$VERSION;

BANNER
X6O2C j^-hH8Fy8\4a0--------------------------------------------------------------------------------
d*Ihgf!^/FE0Oracle Database11gEnterprise Edition Release 11.1.0.6.0 - 64bit Production
S*?9gQ3VD#a0PL/SQL Release 11.1.0.6.0 - ProductionITPUB个人空间+} oh1{nj.@
CORE    11.1.0.6.0      Production
5Sx!`-GJ0TNS for Solaris: Version 11.1.0.6.0 - Production
[9h"h_2_:q$]5d0{ z0NLSRTL Version 11.1.0.6.0 - Production

下面看看造成问题的SQL：

SQL> SET TIMING ONITPUB个人空间fl7hu$])B
SQL> SET AUTOT ON
(ejC/Gla}5~0SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTSITPUB个人空间$A;?F gR@0N
  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAMEITPUB个人空间;q0_@b8X n
------------------------------ ------------------------------ITPUB个人空间^B [Ys f F$n
SYS                            ORA$BASEITPUB个人空间zL;dLeeu,I
TEST                           T_PARALLEL

已用时间:  00: 26: 49.82

执行计划
ci(DU F.q S2x0----------------------------------------------------------
[$L)fNk4cJ;I8y'k0Plan hash value: 444070136

---------------------------------------------------------------------------------------------------ITPUB个人空间"GF#v swGf$c Q@
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间SM#sBV|:d
---------------------------------------------------------------------------------------------------ITPUB个人空间 SC/]e \\
|   0 | SELECT STATEMENT                |                 |   660 | 63360 |   181   (5)| 00:00:03 |ITPUB个人空间~$N Z+sL?9Zdn
|*  1 |  FILTER                         |                 |       |       |            |          |
tqgv k-p G0|   2 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |
R[,w J$L9s8m0|   3 |    UNION-ALL                    |                 |       |       |            |          |
cU m:z.Wb"Ldg:X0|*  4 |     FILTER                      |                 |       |       |            |          |
j6IA/UI5p'Fsr9uv0|*  5 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |ITPUB个人空间NYs"~5E1~q s
|   6 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |
5_+o/b[w/y O~U0|*  7 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |ITPUB个人空间/C3C&|4gXw
|   8 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |
xo1`A"?5Z0|*  9 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |
4n C'x-Ft*Q0|* 10 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |ITPUB个人空间"kcIZo&TY^Y
|* 11 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |
GX'Sg'@ ~^0|  12 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |
5f-rT8]#|%^ ^,p0|* 13 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |
cd{z%w5SM0|* 14 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |
n)^1v#R-cP(a0|  15 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
1S D aB0MN0|  16 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |
XG{fzj7q ^0|  17 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |
0Ih(^wS|g#T0|* 18 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |ITPUB个人空间5`{3Y$M {cs| C7@
|  19 |   NESTED LOOPS                  |                 |     1 |    76 |     1 (100)| 00:00:01 |ITPUB个人空间 }%ljb DXNnu#`3Q
|* 20 |    HASH JOIN                    |                 |     1 |    57 |     1 (100)| 00:00:01 |ITPUB个人空间+Q*m-H;MO+I
|* 21 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |ITPUB个人空间?&B \]XY
|  22 |     VIEW                        | GV$_LOCK        |    10 |   250 |     0   (0)| 00:00:01 |ITPUB个人空间SH yVh5q
|  23 |      UNION-ALL                  |                 |       |       |            |          |
x%l'R/w;nNl]L0|* 24 |       FILTER                    |                 |       |       |            |          |ITPUB个人空间lHA,l.`9il q
|  25 |        VIEW                     | GV$_LOCK1       |     2 |   178 |     0   (0)| 00:00:01 |ITPUB个人空间F7bH JA sj
|  26 |         UNION-ALL               |                 |       |       |            |          |ITPUB个人空间*s PS+H;o,W4O
|* 27 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
!tT9q'U1~0`&Jn0|* 28 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
M5eE |%o{2L0|* 29 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间 }s~s$D!buL
|* 30 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间.N-_ f8Cc"Hu
|* 31 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
:[u~(LcR)~f]6g0|* 32 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间+?x2^'Q?8C i \
|* 33 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间c(j-S7jg(p Y
|* 34 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间 iz G0t7dB+fYk,^
|* 35 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间uj])s+AOB{ j
|* 36 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
_]eI S%R3m9ur0|* 37 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |
(hUH `*M X,h.\t0---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间az-JRs#} g(B
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM SYS."X$KSQRS" "R",SYS."X$KSUSE" "S", ( (SELECT
)Bd4P4Wl9B^3V,F?-`0              USERENV('INSTANCE') "INST_ID","LADDR" "LADDR","KADDR" "KADDR","SADDR" "SADDR","RADDR"
t,b(^d f,^0              "RADDR","LMODE" "LMODE","REQUEST" "REQUEST","CTIME" "CTIME","BLOCK" "BLOCK" FROM  (
w0r%hR3E,Zm0              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"ITPUB个人空间5NyKlhg!g
              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
+n(L j$\mJ'{3Kn&@_0              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KDNSSF" "X$KDNSSF" WHERE ("KSQLKMOD"<>0 OR
._*BW o*v-Q6C!?G0              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0) UNION ALL
%Y7i['Xt\"a!Tl5O0              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
F0XFIn3E-Rd0              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"ITPUB个人空间.\6x/m2aF3@}
              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KSQEQ" "X$KSQEQ" WHERE ("KSQLKMOD"<>0 OR
;p"F S*_1{$U+w}@.I ~0              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) "GV$_LOCK1"ITPUB个人空间]?B*C N3J
              WHERE USERENV('INSTANCE') IS NOT NULL) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR"
.xLFlT"^;_;w0              "LADDR","KSQLKADR" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"ITPUB个人空间#WYDqh ENM)@ dW
              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTADM"ITPUB个人空间(l2uK)o-T'{-q
              "X$KTADM" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
$G'G`)Rai0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间+p Gc-`4ywT
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间aO?xaP_&G/FA
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFIL" "X$KTATRFIL" WHEREITPUB个人空间[uW5L^/V
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间D"aX CW h%@V9e
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
x!v1d5?k'B,YPp0              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
;{\nK'Of:E0              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFSL" "X$KTATRFSL" WHEREITPUB个人空间'},wrD9HT
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间Mn$^6I${
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间 a,gNG RV9]EE
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间7?0Y*~&??b-iT
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATL" "X$KTATL" WHEREITPUB个人空间-Za^"B M)uVSi
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间3^/`5E7N&\M_E
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间qyba0~"_1W4M'V
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间w8w&CE"G2yPF0]
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSC" "X$KTSTUSC" WHEREITPUB个人空间 }3Nm!Hw k)I
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间0^9ki0GE\!V
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间H6|b@2RW
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间Q nyYiy9PT
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSS" "X$KTSTUSS" WHEREITPUB个人空间?g6O$J!@q
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间 ^i@|^b
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
Gn&tNh2e1d"F$\0              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
L4tphLp5]0              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSG" "X$KTSTUSG" WHERE
"CZ:T lG(C;os~K"z$q0              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
+[#D'q(r#H[0l0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","KTCXBXBA"ITPUB个人空间h/W[ j\3B%{*t"O
              "LADDR","KTCXBLKP" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
h+?/NJJ,hO[Pn'I0              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTCXB"
Yj ^~X?/n0              "X$KTCXB" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
@B e N#D#Q i;y{0              BITAND("KSSPAFLG",1)<>0)) "GV$_LOCK" WHERE "SADDR"="S"."ADDR" AND "S"."KSUSENUM"=305 AND
2Mvz;`}{y*ZYn0              "S"."INST_ID"=USERENV('INSTANCE') AND "R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR"))ITPUB个人空间S8W0d$^*mO w @n?9e5W
   4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROM
0pi[Bf6@1Y0              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3ITPUB个人空间0|^ I'@-B-Y q
              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
o;sc x)UAS0              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
^FO(BI#X0              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 ANDITPUB个人空间9s S| Z)D$_
              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 ANDITPUB个人空间)j?uzdFx-?z7U
              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 ORITPUB个人空间$D B%`3eJf k
              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 ORITPUB个人空间ME'hW#M+Sq^Gml
              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
1hp&La a@ Px0              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 ORITPUB个人空间&D4T#_kt$oN$n
              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) ORITPUB个人空间XW o ec"M;k%]
              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
L*G1A'q'a0              AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 ANDITPUB个人空间g/m.el8cDk tu+OX5L
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))ITPUB个人空间.f#O3j|;s9y
   5 - access("O"."SPARE3"="U"."USER#")ITPUB个人空间:Z y}t7QA(rNj p
   7 - access("O"."OWNER#"="U"."USER#")ITPUB个人空间C/s'jEUY4R+\[
   9 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' AND
n{1gW+K!Rz0              "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
\\,i6|9g0  10 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
C-K)\b&uK$j0              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)ITPUB个人空间 I U4z0L;s2g#i
  11 - access("I"."OBJ#"=:B1)
E'_0C Lky o0  13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_ediITPUB个人空间~W3TG"d
              tion_id')))
1r3d$aW&O$k&W$w0       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
f[H%h:l;]0              tion_id')))ITPUB个人空间h#OSA c$GZ:i
  14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")ITPUB个人空间K;T{ecL1@
  18 - access("L"."OWNER#"="U"."USER#")
6\:l'i_O|U5j \6[0  20 - access("SADDR"="S"."ADDR")ITPUB个人空间-C/o&X+g)`.UU({ j
  21 - filter("S"."KSUSENUM"=305 AND "S"."INST_ID"=USERENV('INSTANCE'))ITPUB个人空间&T/Td)Iuzhi
  24 - filter(USERENV('INSTANCE') IS NOT NULL)
5?T!@] n,s"|0  27 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
j y:u"V,yY0              BITAND("KSSOBFLG",1)<>0)
i1Io{3Y sA0  28 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间*D0Y'lPL}V]-lgd
              BITAND("KSSOBFLG",1)<>0)
e}qp0B0Xrn8{0  29 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间G y k6i7]TmO1^
              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间FPY/kxuy\
  30 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
S7Sx5k~K*K0e0              BITAND("KSSOBFLG",1)<>0)
"A0Oma*I1k~L xwG0  31 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间DZq(B5@9VX}r
              BITAND("KSSOBFLG",1)<>0)
6{4l(n!k{9Kt0  32 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
S]TO-^0              BITAND("KSSOBFLG",1)<>0)
m,| Z(e6jU0  33 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
8H$HZ7Y!Na6L0              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间V%\ lEY1g?
  34 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
8n'q{dl8n E H0              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间P.~2GFO`"saQ%@+G^
  35 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
e'~E ^t0              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间rrG.L?L7_*r
  36 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
n1Br#K z0              BITAND("KSSPAFLG",1)<>0)
Q"\8BFTp4dV0  37 - filter("R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR")


.b}J2l#lSFo H0统计信息
hUF#o0[0----------------------------------------------------------ITPUB个人空间ee"J["J1o@4J:S
         15  recursive calls
~sZ!Jmj0      70632  db block gets
5K0].@\N`B*Rz;N0        982  consistent gets
9B {$t4GlaG] L+x0          3  physical reads
+R2G;h3RkE P0          0  redo size
2x+w} V3D0        667  bytes sent via SQL*Net to clientITPUB个人空间;J fRzV7Eo
        520  bytes received via SQL*Net from clientITPUB个人空间gRg4I/yP
          2  SQL*Net roundtrips to/from client
;nX.a+^9N)C_0          0  sorts (memory)
!q\r3R#Dl0          0  sorts (disk)
y;U+BB Z6~A-k D fS0          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
y,TR"z*CU`)s|0  2  WHERE OBJECT_ID = ID1
e+ZoyIi0  3  AND SID = 305;

OWNER                          OBJECT_NAME
+M;S U~-w+~H9pl^}0------------------------------ ------------------------------
.I)h DEGa0SYS                            ORA$BASE
W(b:v8}:N0TEST                           T_PARALLEL

已用时间:  00: 00: 00.44

执行计划
;Xo&z)|T8d,n&}0----------------------------------------------------------ITPUB个人空间Va+i5T:z b^
Plan hash value: 3416262628

---------------------------------------------------------------------------------------------------ITPUB个人空间*S:X@'GZ |7G F
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间O9OyY.F L@*U@S
---------------------------------------------------------------------------------------------------
F{2A0q p:H9UO? d0|   0 | SELECT STATEMENT                |                 |     1 |   159 |   182   (5)| 00:00:03 |
!Mz]F3c'F8L0|*  1 |  HASH JOIN                      |                 |     1 |   159 |   182   (5)| 00:00:03 |
l3SZ!Ksy0|   2 |   NESTED LOOPS                  |                 |     1 |    63 |     1 (100)| 00:00:01 |
k0l7scN;|C*|S KL0|*  3 |    HASH JOIN                    |                 |     1 |    44 |     1 (100)| 00:00:01 |
`9Zl'A!~"{hg$o r&C3D0|*  4 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |ITPUB个人空间+T^4\#L$bzOz
|   5 |     VIEW                        | GV$_LOCK        |    10 |   120 |     0   (0)| 00:00:01 |
8\2U4owVA X v(S(j0|   6 |      UNION-ALL                  |                 |       |       |            |          |ITPUB个人空间ZWFx'oI-n
|*  7 |       FILTER                    |                 |       |       |            |          |ITPUB个人空间c;z.\:XV
|   8 |        VIEW                     | GV$_LOCK1       |     2 |   152 |     0   (0)| 00:00:01 |ITPUB个人空间vk9e HjV ~
|   9 |         UNION-ALL               |                 |       |       |            |          |
ri hCl0|* 10 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
*kUW{6xl%z3P(^0|* 11 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间TP(R/gL:B
|* 12 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
&JgY)w.h,h2c u0|* 13 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |
*yr~ k0{6o&_ w0|* 14 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间+S,R G)c,[
|* 15 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
8i ]'as)w dm@t0|* 16 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间/E5F QsC0g
|* 17 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间 ~HKBWr uH
|* 18 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间;VOyNuB
|* 19 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间j0V;]MRG7K
|* 20 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |
8n\ i\9Nl0|  21 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |ITPUB个人空间(ZV dHY%p
|  22 |    UNION-ALL                    |                 |       |       |            |          |
~mAu'K0|* 23 |     FILTER                      |                 |       |       |            |          |
1q8S7b+\FljU8E+~0|* 24 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |
#e+JE#Zn&d0|  25 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |
j t5F*h x\0|* 26 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |
1^l7@u I(Sb`i ?0|  27 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |
)Ai5_ AOHp dx0|* 28 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |ITPUB个人空间 Ci c8` D1Z
|* 29 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |
W]%A{0lf[:Q0|* 30 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |ITPUB个人空间 P't(I)@![O
|  31 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |
,ey$iAWh0|* 32 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |
},dz D"kzH0|* 33 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |ITPUB个人空间D]-D/G!\3Td_
|  34 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
w#kweG9`~3B0|  35 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |ITPUB个人空间7h4w.lK9G2T`2VCy
|  36 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |
K8RM!P-B;FUb0|* 37 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |ITPUB个人空间wZMt2Sv0peB
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间A%KE@k}3RY
---------------------------------------------------

   1 - access("OBJECT_ID"="R"."KSQRSID1")
AvE]W0   3 - access("SADDR"="S"."ADDR")ITPUB个人空间dn4oDxX1LB
.
w],~7X3]6]v0.
G[7p+L`N0.ITPUB个人空间*t?Y:D]MW
  37 - access("L"."OWNER#"="U"."USER#")


|I&LZ(Pp1LM0统计信息
!A6HFiEj g0----------------------------------------------------------ITPUB个人空间7So+u,BP-f8r,o
         15  recursive callsITPUB个人空间u gxSAz
          3  db block gets
u{K4sz N0        977  consistent gets
,N4r_5\O6SF0          0  physical readsITPUB个人空间#PW1G])I|3R
          0  redo sizeITPUB个人空间]Q[%Yv)Z^ X
        667  bytes sent via SQL*Net to clientITPUB个人空间v9\$PlI#N S
        520  bytes received via SQL*Net from clientITPUB个人空间4C i9T:@*fc;oj
          2  SQL*Net roundtrips to/from clientITPUB个人空间U _;_b.^8A o [
          0  sorts (memory)ITPUB个人空间 h.P(h+`!z"F1l-Wg'Z
          0  sorts (disk)
'\&`"Uh5n){tQ._0          2  rows processed

这个执行时间和统计信息是正常的，通过对比也可以发现，第一个查询的执行计划确实存在问题。

SQL> SELECT /*+ RULE */ OWNER, OBJECT_NAME FROM DBA_OBJECTS
pU-Qu+Zr,}.w!D0  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAMEITPUB个人空间"k)] h)|P&a*_
------------------------------ ------------------------------ITPUB个人空间v,aQ!i;?0S0F c y
SYS                            ORA$BASEITPUB个人空间Q+@%Ex|
TEST                           T_PARALLEL

已用时间:  00: 00: 01.31

执行计划
%AS,d)Kt0----------------------------------------------------------ITPUB个人空间"\U8H [ FKo
Plan hash value: 2735497195

----------------------------------------------------------
!F%R3zeu fw:n Q0| Id  | Operation                          | Name        |ITPUB个人空间 y3D:DG_t _
----------------------------------------------------------ITPUB个人空间!GH&o4h&fN
|   0 | SELECT STATEMENT                   |             |
:Zc1U(gWs2])G0|   1 |  MERGE JOIN                        |             |
.pR6S\3tlYwRkZ0|   2 |   SORT JOIN                        |             |
.^4C)fU[N,p;T3J0|   3 |    VIEW                            | DBA_OBJECTS |ITPUB个人空间 ?:?:Rtq/d3XY
|   4 |     UNION-ALL                      |             |
.Ot1aTAg%OE's0|*  5 |      FILTER                        |             |ITPUB个人空间'm1_%@WO `O:?
|   6 |       NESTED LOOPS                 |             |ITPUB个人空间4iXh s _W5D*o/r
|   7 |        NESTED LOOPS                |             |
os Np ^2Hu0|   8 |         TABLE ACCESS FULL          | USER$       |
N [.jg6b#N0|*  9 |         TABLE ACCESS BY INDEX ROWID| OBJ$        |
'|0r'Aj'Tf+c0|* 10 |          INDEX RANGE SCAN          | I_OBJ5      |
nk(u3FzF+K]:m0|  11 |        TABLE ACCESS CLUSTER        | USER$       |
m?*G;jg;qCz0|* 12 |         INDEX UNIQUE SCAN          | I_USER#     |ITPUB个人空间9O0w$u XV,^&X-u(F @
|* 13 |       TABLE ACCESS BY INDEX ROWID  | IND$        |ITPUB个人空间"b P$n*J:v$ZS
|* 14 |        INDEX UNIQUE SCAN           | I_IND1      |
SlAveR4d\0|  15 |       NESTED LOOPS                 |             |ITPUB个人空间 jr(A gh d
|* 16 |        INDEX RANGE SCAN            | I_OBJ4      |
v~b\.a0|* 17 |        TABLE ACCESS CLUSTER        | USER$       |
7S I5{H1H A(X0|* 18 |         INDEX UNIQUE SCAN          | I_USER#     |
-?Q&@ c}d0|  19 |      NESTED LOOPS                  |             |ITPUB个人空间6J ^%a4uNu&hc
|  20 |       TABLE ACCESS FULL            | USER$       |
2v-a ?%X"D(UD A"P0|* 21 |       INDEX RANGE SCAN             | I_LINK1     |ITPUB个人空间bK']J6E5J3Gn+@
|* 22 |   SORT JOIN                        |             |
;n(HQ)w6IEv#zw0x DI V0|  23 |    VIEW                            | VW_NSO_1    |ITPUB个人空间J | Vt F)I~ r!F-G ~
|  24 |     SORT UNIQUE                    |             |ITPUB个人空间I t(O4\f#wm r
|  25 |      MERGE JOIN                    |             |
1F^E_"A0|  26 |       SORT JOIN                    |             |
B,ns)`d8s0|  27 |        MERGE JOIN                  |             |
{j^p6CZ^2X4mqf0|  28 |         SORT JOIN                  |             |
znn%h)B$GeCf0|  29 |          FIXED TABLE FULL          | X$KSQRS     |
@%yXx6xs7v,A c0|* 30 |         SORT JOIN                  |             |
pvj|+i+m/A1[2[0|  31 |          VIEW                      | GV$_LOCK    |
kZ b3W'zxX0|  32 |           UNION-ALL                |             |
RIm$Zu Y.k0|* 33 |            FILTER                  |             |ITPUB个人空间meKs+\th
|  34 |             VIEW                   | GV$_LOCK1   |
lMSB tbV O"VG X0|  35 |              UNION-ALL             |             |ITPUB个人空间Y3aQWC!Ay'z
|* 36 |               FIXED TABLE FULL     | X$KDNSSF    |ITPUB个人空间 DqS:B8`/PEF.O
|* 37 |               FIXED TABLE FULL     | X$KSQEQ     |
cm@Y'],C0Y8oB0|* 38 |            FIXED TABLE FULL        | X$KTADM     |ITPUB个人空间3h(M'^\eP}]e
|* 39 |            FIXED TABLE FULL        | X$KTATRFIL  |ITPUB个人空间%D+Ko+f} Yy.tK
|* 40 |            FIXED TABLE FULL        | X$KTATRFSL  |
8q#n P5t2`0L0|* 41 |            FIXED TABLE FULL        | X$KTATL     |
+@oKN@T0|* 42 |            FIXED TABLE FULL        | X$KTSTUSC   |
:|+a0Y#x `_y$n0@3[0|* 43 |            FIXED TABLE FULL        | X$KTSTUSS   |ITPUB个人空间6V0?-b cj T
|* 44 |            FIXED TABLE FULL        | X$KTSTUSG   |ITPUB个人空间._'RL,DsU~
|* 45 |            FIXED TABLE FULL        | X$KTCXB     |ITPUB个人空间py)}GM'|/D x*`
|* 46 |       SORT JOIN                    |             |
-?GP(FZA#rO;ju0|* 47 |        FIXED TABLE FULL            | X$KSUSE     |ITPUB个人空间%s {jw gm
----------------------------------------------------------

Predicate Information (identified by operation id):
`2|M9Wg2a5Ts0---------------------------------------------------

   5 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 ANDITPUB个人空间N2P-h0e*al!B
              (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1ITPUB个人空间(x5V qYV!qkZHNU
              OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR
X{/D6[^O/NN0.ITPUB个人空间 hi6v&kCFI
.
5u\ lfc!y*h6j0.ITPUB个人空间8L Y.l(hm%UQ+L
  47 - filter("S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSUSENUM"=305)

Note
QD.ACl0Z3Y@c ]-z T0-----
{fVTcg;W2MTW.}7r0   - rule based optimizer used (consider using cbo)


#^6\J)WHxB$? G0统计信息
L!\no8Q1b}0----------------------------------------------------------ITPUB个人空间%e0Fb\4n
         15  recursive callsITPUB个人空间:cx4oG|k+T5Bo
          3  db block getsITPUB个人空间#wW7g1_rj8r
     124231  consistent getsITPUB个人空间;Kqo#~(N
          0  physical reads
T2Y4Y'iU%N.x0          0  redo sizeITPUB个人空间 ? t?YD
        667  bytes sent via SQL*Net to clientITPUB个人空间;Bx'E S\"m8VoC
        520  bytes received via SQL*Net from clientITPUB个人空间d*h3pc%]a(q
          2  SQL*Net roundtrips to/from clientITPUB个人空间 F7[3j'o)V3K9k
          7  sorts (memory)
` O+qe%e d.f;c&\0          0  sorts (disk)ITPUB个人空间-y[6S)o.T~ K'D
          2  rows processed

和其他类似情况一样，添加RULE的hint能避免问题的产生。

问题在10.2.0.3上也可以再现，但是在9204上则不会出现。

 


导入论坛 引用链接 收藏 分享给好友 推荐到圈子 管理 举报

TAG:

lcmlsj的个人空间 引用 删除 lcmlsj   /   2008-08-29 07:28:32
在单实例上也存在类似问题！11g bug确实遇到了一些，如：执行
rman dulicate也出问题。
 

评分：0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar