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

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

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

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

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

 

 

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

SQL> SELECT * FROM V$VERSION;

BANNERITPUB个人空间W1K:K;l ~ k:E$uV
--------------------------------------------------------------------------------
Lm] HY7ra T0Oracle Database11gEnterprise Edition Release 11.1.0.6.0 - 64bit Production
S`|7F8B^ ^o u0PL/SQL Release 11.1.0.6.0 - Production
Lkx5B&` h3a0CORE    11.1.0.6.0      ProductionITPUB个人空间1Q1@qg4M&@r
TNS for Solaris: Version 11.1.0.6.0 - Production
V@+b-E I}kFk0NLSRTL Version 11.1.0.6.0 - Production

下面看看造成问题的SQL：

SQL> SET TIMING ONITPUB个人空间^iQw+I"_C5~Pk
SQL> SET AUTOT ON
(GRa$Lm0SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTSITPUB个人空间,RYrC@Mq8QG
  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAMEITPUB个人空间"`(r-R,us:?
------------------------------ ------------------------------
PV(p#|[G0SYS                            ORA$BASEITPUB个人空间R4^.u&@A.uIVN{(U%n`
TEST                           T_PARALLEL

已用时间:  00: 26: 49.82

执行计划
P%\h6z}0----------------------------------------------------------ITPUB个人空间Gk9Ey2^4L
Plan hash value: 444070136

---------------------------------------------------------------------------------------------------ITPUB个人空间7yz,OLB1r*A*HQ?
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |ITPUB个人空间LB w'ys
---------------------------------------------------------------------------------------------------
N;f5Y(]R"H0|   0 | SELECT STATEMENT                |                 |   660 | 63360 |   181   (5)| 00:00:03 |
7f5A0A+u0OfGYI)P0|*  1 |  FILTER                         |                 |       |       |            |          |
P&eL:Jq l4{0|   2 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |
-v"[P-\XY$@Xd0|   3 |    UNION-ALL                    |                 |       |       |            |          |ITPUB个人空间 eJM| IOx'`^
|*  4 |     FILTER                      |                 |       |       |            |          |
:|V"ux^rJ0|*  5 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |
6C*a(w']J0C5A*\j#i0|   6 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |ITPUB个人空间(RB!|5E6xH7D
|*  7 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |ITPUB个人空间x&[ s@8ufq3?(ZS4Z
|   8 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |
/]\ wna)l4@0|*  9 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |ITPUB个人空间{2Rr!gOJ/G
|* 10 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |
0ac(t,wK]Aomu0|* 11 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |
NHe%]+@0|  12 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |
"S-V#y2zx OK'O7S!}F0|* 13 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |ITPUB个人空间I#n6G*nN4~yb n
|* 14 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |ITPUB个人空间$T-G3oqb$_
|  15 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
:~ Wg/hH@0|  16 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |ITPUB个人空间fF~&G~1OB-y
|  17 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |ITPUB个人空间x%UaNT6w6z w
|* 18 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |
E f0JDx(R G.xCV [0|  19 |   NESTED LOOPS                  |                 |     1 |    76 |     1 (100)| 00:00:01 |
H yy oA'n+s M(Q Of0|* 20 |    HASH JOIN                    |                 |     1 |    57 |     1 (100)| 00:00:01 |
^:]t9g3nSBY+rJ0|* 21 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |
axX,WJg!]3jTx!M0|  22 |     VIEW                        | GV$_LOCK        |    10 |   250 |     0   (0)| 00:00:01 |ITPUB个人空间 pljaPN9h a
|  23 |      UNION-ALL                  |                 |       |       |            |          |ITPUB个人空间3_{R!v.FE
|* 24 |       FILTER                    |                 |       |       |            |          |
tU c:g+{9C)H0|  25 |        VIEW                     | GV$_LOCK1       |     2 |   178 |     0   (0)| 00:00:01 |
Tn0[)pl0|  26 |         UNION-ALL               |                 |       |       |            |          |
5d2sF)Qv"{/V8H9]0|* 27 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
I ti-B l }d0|* 28 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间7@'Dkc8U2T"i
|* 29 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |
#y;g5K3s,S.Fa0|* 30 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间%z+kOX6upO
|* 31 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间%o4b|*K+NGp/Z$G
|* 32 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
(G%E7C"nRjBtje'B0|* 33 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
IO;ms Bhmd0|* 34 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间i Y KE5jJ Cz
|* 35 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |
2p(fz[]3sV&Gkj0|* 36 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间d n6cN@*{1V
|* 37 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |ITPUB个人空间9NR;OZ-T
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间&E5UpkK4~6bd
---------------------------------------------------

   1 - filter( EXISTS (SELECT 0 FROM SYS."X$KSQRS" "R",SYS."X$KSUSE" "S", ( (SELECT
W]q#j-~2q+C8cZ0              USERENV('INSTANCE') "INST_ID","LADDR" "LADDR","KADDR" "KADDR","SADDR" "SADDR","RADDR"ITPUB个人空间\ p%?zv2|D
              "RADDR","LMODE" "LMODE","REQUEST" "REQUEST","CTIME" "CTIME","BLOCK" "BLOCK" FROM  (
qP#s"|p0              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
x%Id9|zs0              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
s0rphs&rcL\0              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KDNSSF" "X$KDNSSF" WHERE ("KSQLKMOD"<>0 OR
2bZE3VsIN0              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0) UNION ALLITPUB个人空间3Tb1Z ]s Ol*} K
              (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR" "KADDR","KSQLKSES"
)O-g [^dm0              "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM"
W:n:m*@$uo4`/_0              "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KSQEQ" "X$KSQEQ" WHERE ("KSQLKMOD"<>0 ORITPUB个人空间8Je]c:py2a7[0d*D
              "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) "GV$_LOCK1"
8~"JO-h8z2xxY0              WHERE USERENV('INSTANCE') IS NOT NULL) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR"ITPUB个人空间2MLh z9t8t1lj*v H Z:jM
              "LADDR","KSQLKADR" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
8z KW{WS u)h%K0              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTADM"ITPUB个人空间 bqG$Lcicnct$Y(h v
              "X$KTADM" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
.B'Dm-}F&] b0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间#Q Cd7kL\i t'_
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间h.xbV2`7?y
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFIL" "X$KTATRFIL" WHERE
BCvELN0              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
+?OML|0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
6\8y g.f |v xuD)q0              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
n vEBh0              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATRFSL" "X$KTATRFSL" WHEREITPUB个人空间1Or?#Ve RYP'}'X ?u
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间(g&K8A$Umq
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间C&iPd/S+\
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
q;?v)H,AfT,_0              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTATL" "X$KTATL" WHEREITPUB个人空间JVr;~#j3v`\
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
"H:j |l@[C8\x0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间_"v[-Am$o)WL
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间9C} Sd5k,Tl.J
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSC" "X$KTSTUSC" WHEREITPUB个人空间#]q)Q SU9Q Sj
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
0OU?vV0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"
%v[dow'{X0              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"
aa0D]R c(A0              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSS" "X$KTSTUSS" WHEREITPUB个人空间o`F h8mW%np-UG5Io
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
|Kv Md H d0              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","ADDR" "LADDR","KSQLKADR"ITPUB个人空间Aql'sg9z*Hg
              "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD" "LMODE","KSQLKREQ"ITPUB个人空间 f T+XW:\^'pb
              "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTSTUSG" "X$KTSTUSG" WHEREITPUB个人空间 km$blV~?q2`3S
              ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间/J5R dH D \
              BITAND("KSSOBFLG",1)<>0) UNION ALL  (SELECT "INST_ID" "INST_ID","KTCXBXBA"ITPUB个人空间+E+cKQb"m x@
              "LADDR","KTCXBLKP" "KADDR","KSQLKSES" "SADDR","KSQLKRES" "RADDR","KSQLKMOD"
-w/_F] M)Itq#S4t0              "LMODE","KSQLKREQ" "REQUEST","KSQLKCTIM" "CTIME","KSQLKLBLK" "BLOCK" FROM SYS."X$KTCXB"
#D5z7TV7V2e Vw6t0              "X$KTCXB" WHERE ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间,T0f i&w.Vw3g3Eso
              BITAND("KSSPAFLG",1)<>0)) "GV$_LOCK" WHERE "SADDR"="S"."ADDR" AND "S"."KSUSENUM"=305 AND
,t"Ob6{~!P;QP gs0              "S"."INST_ID"=USERENV('INSTANCE') AND "R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR"))ITPUB个人空间'F\nUN1x
   4 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 AND  (SELECT 1 FROMITPUB个人空间%x Q*l \ pq+]GO.]
              "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3
z5hebf4y+a0              OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9))=1) AND
H7xS2N_`#Ys\0              ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7 AND "O"."TYPE#"<>8 AND
uiB8v%y/Qr?O8E:u G9}/r0              "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND "O"."TYPE#"<>12 AND
1Pg-Slb"@0              "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND "O"."TYPE#"<>87 AND
I7}z;sOjhw;_.j0              "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR "O"."TYPE#"=5 OR
,Y5F`kP l(i0              "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR "O"."TYPE#"=11 OR
gX4kqq&L#VW+Z k$KY0              "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR "O"."TYPE#"=87)
9S5b:s$]8a a*zw0              AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND "U"."TYPE#"<>2 OR
k,P&|]XnA{0              "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')) OR
2ID7EIlO3B|a0              EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."OWNER#"="U2"."USER#"
vFAm/VU&yS+u0              AND "O2"."TYPE#"=88 AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 ANDITPUB个人空间KW:AG.x8R
              "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
{M-V N2R0   5 - access("O"."SPARE3"="U"."USER#")
%b7m J8X'M3{:@0   7 - access("O"."OWNER#"="U"."USER#")ITPUB个人空间H)l g\gS,E~
   9 - filter("O"."NAME"<>'_NEXT_OBJECT' AND "O"."NAME"<>'_default_auditing_options_' ANDITPUB个人空间"@[ F5G T6a'q
              "O"."LINKNAME" IS NULL AND BITAND("O"."FLAGS",128)=0)
~!@0VF7L8Gz0  10 - filter("I"."TYPE#"=1 OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR
gG5U3Se0              "I"."TYPE#"=6 OR "I"."TYPE#"=7 OR "I"."TYPE#"=9)
q&g3n4cV Iq4d0  11 - access("I"."OBJ#"=:B1)
g[fJImU0  13 - access("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_ediITPUB个人空间qA7Hl.j,N+y:q'j
              tion_id')))ITPUB个人空间`F'Z%Cr? Y:t#d4E"x
       filter("U2"."TYPE#"=2 AND "U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edi
[+F _PrN&T0              tion_id')))
\Q-J3U6n9t8s0  14 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88 AND "O2"."OWNER#"="U2"."USER#")ITPUB个人空间,Hj6Wt"YrgAT%|"l`S
  18 - access("L"."OWNER#"="U"."USER#")ITPUB个人空间:C/g3j"?3e(@ss2y
  20 - access("SADDR"="S"."ADDR")ITPUB个人空间t1IT5WQy)L|Q
  21 - filter("S"."KSUSENUM"=305 AND "S"."INST_ID"=USERENV('INSTANCE'))ITPUB个人空间Im ^7{m5R
  24 - filter(USERENV('INSTANCE') IS NOT NULL)
*R)OT L@ {0  27 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
U6\1Z!Dop`|E~0              BITAND("KSSOBFLG",1)<>0)
^&R}"J5y v*nu0  28 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
b'G*p4W$[0              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间 gq9?-K'gnz7~
  29 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间O f Fn/Js,K
              BITAND("KSSOBFLG",1)<>0)
UdZ1uh v:ERR0  30 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
{*g_uza$W }0              BITAND("KSSOBFLG",1)<>0)
/B7q&Be6i{+PB0  31 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间a2N}.}i B,U:b+w
              BITAND("KSSOBFLG",1)<>0)
@c8[#qbO/A0  32 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
+uv]+R F0              BITAND("KSSOBFLG",1)<>0)
halw7l0  33 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
*o kFCWS^0              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间/j4^t-J1D2T/?
  34 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间^#[:R'Ev.t,R
              BITAND("KSSOBFLG",1)<>0)
:t6_(o TU6U(Uc"wC0  35 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') ANDITPUB个人空间 ]m$KK.p4? y
              BITAND("KSSOBFLG",1)<>0)ITPUB个人空间1X,Q7s QW*R
  36 - filter(("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND
d Jr)tfjWDv0              BITAND("KSSPAFLG",1)<>0)ITPUB个人空间 AD-S p g ne
  37 - filter("R"."KSQRSID1"=:B1 AND "RADDR"="R"."ADDR")

ITPUB个人空间In{Jw A
统计信息ITPUB个人空间_{1yV"sU/@-@1X!o
----------------------------------------------------------ITPUB个人空间5wh|s3w Q
         15  recursive calls
B.Wk(@'p L/D{1A0      70632  db block getsITPUB个人空间Z)T.h bP B
        982  consistent gets
Y/BSY5z6rH'u-_\0          3  physical readsITPUB个人空间 Q|+gc7l"iT8A1w
          0  redo sizeITPUB个人空间#SU9YHe1k @}i+fd
        667  bytes sent via SQL*Net to clientITPUB个人空间5B(h,w.F!W/I
        520  bytes received via SQL*Net from client
Tkw@r'C7SP0          2  SQL*Net roundtrips to/from clientITPUB个人空间,v!jN,[FM9cW
          0  sorts (memory)ITPUB个人空间WXr/UM#M
          0  sorts (disk)ITPUB个人空间^;{6`a)}g
          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$LOCKITPUB个人空间 Kn,X(R#f$e6`a
  2  WHERE OBJECT_ID = ID1ITPUB个人空间:cyRec~*X
  3  AND SID = 305;

OWNER                          OBJECT_NAME
Vu)N5w/JL_N^0------------------------------ ------------------------------
.mQw"}}7mc0SYS                            ORA$BASE
R@ F"s4E vO Q9r0TEST                           T_PARALLEL

已用时间:  00: 00: 00.44

执行计划ITPUB个人空间:P;crU\}p
----------------------------------------------------------
n3_0e5? G)d|4H p0Plan hash value: 3416262628

---------------------------------------------------------------------------------------------------ITPUB个人空间]5R4|c+Ou#Z7j6|
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
wy-OG6^^0---------------------------------------------------------------------------------------------------
8S"m'?N1u[0|   0 | SELECT STATEMENT                |                 |     1 |   159 |   182   (5)| 00:00:03 |ITPUB个人空间zU4x~H3p X ocg%R:|
|*  1 |  HASH JOIN                      |                 |     1 |   159 |   182   (5)| 00:00:03 |ITPUB个人空间$W`3n*uX(z
|   2 |   NESTED LOOPS                  |                 |     1 |    63 |     1 (100)| 00:00:01 |
&L Vt-So O!O;LS0|*  3 |    HASH JOIN                    |                 |     1 |    44 |     1 (100)| 00:00:01 |ITPUB个人空间|Zrx1s:X&SC
|*  4 |     FIXED TABLE FULL            | X$KSUSE         |     1 |    32 |     0   (0)| 00:00:01 |ITPUB个人空间1F(ENMz2\
|   5 |     VIEW                        | GV$_LOCK        |    10 |   120 |     0   (0)| 00:00:01 |
0n'M9CjK D:E2A0|   6 |      UNION-ALL                  |                 |       |       |            |          |
l R0x$Ai0|*  7 |       FILTER                    |                 |       |       |            |          |
tz SG~;M1Bk0|   8 |        VIEW                     | GV$_LOCK1       |     2 |   152 |     0   (0)| 00:00:01 |
L2W S+].`&gC0|   9 |         UNION-ALL               |                 |       |       |            |          |
v#mK/L W S];ACqc0|* 10 |          FIXED TABLE FULL       | X$KDNSSF        |     1 |   102 |     0   (0)| 00:00:01 |
LTi] }T0|* 11 |          FIXED TABLE FULL       | X$KSQEQ         |     1 |   102 |     0   (0)| 00:00:01 |
#t&I~O-NT0|* 12 |       FIXED TABLE FULL          | X$KTADM         |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间2OMR f.v'N7q
|* 13 |       FIXED TABLE FULL          | X$KTATRFIL      |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间 \SU6D? uN
|* 14 |       FIXED TABLE FULL          | X$KTATRFSL      |     1 |   102 |     0   (0)| 00:00:01 |
+^!{j8__7q0|* 15 |       FIXED TABLE FULL          | X$KTATL         |     1 |   102 |     0   (0)| 00:00:01 |
utnom0H(X$~T0|* 16 |       FIXED TABLE FULL          | X$KTSTUSC       |     1 |   102 |     0   (0)| 00:00:01 |
5|]!z2C)M%H2D0|* 17 |       FIXED TABLE FULL          | X$KTSTUSS       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间gC&D }/I
|* 18 |       FIXED TABLE FULL          | X$KTSTUSG       |     1 |   102 |     0   (0)| 00:00:01 |ITPUB个人空间 {\4js Q j%{{J#Da%{2}n
|* 19 |       FIXED TABLE FULL          | X$KTCXB         |     1 |   102 |     0   (0)| 00:00:01 |
7S'Z6j:X$T-^0|* 20 |    FIXED TABLE FIXED INDEX      | X$KSQRS (ind:1) |     1 |    19 |     0   (0)| 00:00:01 |
3r9@pog}xYl0|  21 |   VIEW                          | DBA_OBJECTS     | 65980 |  6185K|   181   (5)| 00:00:03 |
j.E4V+tUB Lw'U4go0|  22 |    UNION-ALL                    |                 |       |       |            |          |
,e t9Am/E5?[ D0|* 23 |     FILTER                      |                 |       |       |            |          |
(R"y|!L4Rm5S]0|* 24 |      HASH JOIN                  |                 | 71138 |  8475K|   178   (5)| 00:00:03 |ITPUB个人空间.sR am&Rl2js
|  25 |       TABLE ACCESS FULL         | USER$           |    87 |  1479 |     3   (0)| 00:00:01 |ITPUB个人空间(Q@W%q{-P/z5|0I'zh0C
|* 26 |       HASH JOIN                 |                 | 71138 |  7294K|   174   (5)| 00:00:03 |
:eX6h#^'Vlz7?a0|  27 |        INDEX FULL SCAN          | I_USER2         |    87 |  2001 |     1   (0)| 00:00:01 |ITPUB个人空间p^8q8x:exJy1B
|* 28 |        TABLE ACCESS FULL        | OBJ$            | 71138 |  5696K|   172   (4)| 00:00:03 |
/T-EH.]!u'k%Z0|* 29 |      TABLE ACCESS BY INDEX ROWID| IND$            |     1 |     8 |     2   (0)| 00:00:01 |ITPUB个人空间J'X(}5L$Cr d6m
|* 30 |       INDEX UNIQUE SCAN         | I_IND1          |     1 |       |     1   (0)| 00:00:01 |
|/l&]5o4Cd0|  31 |      NESTED LOOPS               |                 |     1 |    28 |     2   (0)| 00:00:01 |ITPUB个人空间2ASd8lB"[
|* 32 |       INDEX FULL SCAN           | I_USER2         |     1 |    20 |     1   (0)| 00:00:01 |
?p E-X'G Z^0|* 33 |       INDEX RANGE SCAN          | I_OBJ4          |     1 |     8 |     1   (0)| 00:00:01 |
i/v]c7^U"^8o0|  34 |     NESTED LOOPS                |                 |     2 |    86 |     3   (0)| 00:00:01 |
'X-OI0zhZ0|  35 |      INDEX FULL SCAN            | I_LINK1         |     2 |    52 |     1   (0)| 00:00:01 |ITPUB个人空间PRim!]Xz
|  36 |      TABLE ACCESS CLUSTER       | USER$           |     1 |    17 |     1   (0)| 00:00:01 |
T0Ob(yJqO0|* 37 |       INDEX UNIQUE SCAN         | I_USER#         |     1 |       |     0   (0)| 00:00:01 |
vpKL!LK'z:R ]JY0---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):ITPUB个人空间m4u#D&LYF
---------------------------------------------------

   1 - access("OBJECT_ID"="R"."KSQRSID1")
;y TXtB:pG"j3zJV;?0   3 - access("SADDR"="S"."ADDR")
;sV:WV ]0.
Le0U}`)p7Q(ks0.
4to?N#v0.
n9lp?s&D0  37 - access("L"."OWNER#"="U"."USER#")

ITPUB个人空间O5M,DO(sfh
统计信息
sq @#]7S @0----------------------------------------------------------ITPUB个人空间c0S3`l9` dD&^3p&B't0B?
         15  recursive callsITPUB个人空间 ~l/d/tWw
          3  db block getsITPUB个人空间/QF!O;`$KU
        977  consistent gets
L~Pl'Jh0          0  physical reads
O H;|P!e0          0  redo sizeITPUB个人空间rz2mm RN+Y$w M J q
        667  bytes sent via SQL*Net to client
Q ?%CX?d0        520  bytes received via SQL*Net from clientITPUB个人空间j$G7puJ c%b*I
          2  SQL*Net roundtrips to/from client
5Y;])h y Fw8w0          0  sorts (memory)
,K a V6]Q0          0  sorts (disk)
*[XFO4R+`0          2  rows processed

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

SQL> SELECT /*+ RULE */ OWNER, OBJECT_NAME FROM DBA_OBJECTS
m ?{ rgQB0L9{0  2  WHERE OBJECT_ID IN (SELECT ID1 FROM V$LOCK WHERE SID = 305);

OWNER                          OBJECT_NAMEITPUB个人空间HC1tq(M%rs|E5P
------------------------------ ------------------------------
~%\aZU6jA0SYS                            ORA$BASE
A^'C S y0TEST                           T_PARALLEL

已用时间:  00: 00: 01.31

执行计划ITPUB个人空间#lIo2mx2I'?@H
----------------------------------------------------------
"I/vZ O#C8~7fj0Plan hash value: 2735497195

----------------------------------------------------------
B@ h!v0J8h!|0| Id  | Operation                          | Name        |ITPUB个人空间,o T'Lfd G
----------------------------------------------------------
u1};}.P_d F:I0|   0 | SELECT STATEMENT                   |             |
|!w1G0K o'd(U.r8w Yc0|   1 |  MERGE JOIN                        |             |
d lOn_9Ivtl0u|0|   2 |   SORT JOIN                        |             |
)Gc3| n3Y/U0|   3 |    VIEW                            | DBA_OBJECTS |ITPUB个人空间^m-R8k/d0j{X zG
|   4 |     UNION-ALL                      |             |
$UY F~t t%U7x'C$B&D0|*  5 |      FILTER                        |             |ITPUB个人空间E Fw,znJ7s2e
|   6 |       NESTED LOOPS                 |             |ITPUB个人空间nh DFM3C"~
|   7 |        NESTED LOOPS                |             |
$X5|3h9j[4P&p X|~)s*W0|   8 |         TABLE ACCESS FULL          | USER$       |ITPUB个人空间 Tfe P:]Z"s[7oa
|*  9 |         TABLE ACCESS BY INDEX ROWID| OBJ$        |
#a*gi._o`#z]0|* 10 |          INDEX RANGE SCAN          | I_OBJ5      |ITPUB个人空间/L9X1w Ql
|  11 |        TABLE ACCESS CLUSTER        | USER$       |ITPUB个人空间2Hw m?7G \
|* 12 |         INDEX UNIQUE SCAN          | I_USER#     |ITPUB个人空间p9uh\-p$ZU'RC
|* 13 |       TABLE ACCESS BY INDEX ROWID  | IND$        |ITPUB个人空间0l"~I&b a0]*L.q
|* 14 |        INDEX UNIQUE SCAN           | I_IND1      |ITPUB个人空间s5}%mp]@@3v,G
|  15 |       NESTED LOOPS                 |             |
6y v NC7Iq0|* 16 |        INDEX RANGE SCAN            | I_OBJ4      |ITPUB个人空间"\W J(n6Yu%EPgC
|* 17 |        TABLE ACCESS CLUSTER        | USER$       |
qkC R3^&Vy0|* 18 |         INDEX UNIQUE SCAN          | I_USER#     |
!Xx^ \ p^L0|  19 |      NESTED LOOPS                  |             |ITPUB个人空间l a6U5k4xK s._@2e
|  20 |       TABLE ACCESS FULL            | USER$       |ITPUB个人空间 ~4De vmIHz
|* 21 |       INDEX RANGE SCAN             | I_LINK1     |ITPUB个人空间v0F:a ws!w N&WW
|* 22 |   SORT JOIN                        |             |ITPUB个人空间?F|Lyk
|  23 |    VIEW                            | VW_NSO_1    |
S&]0]c#[5G O2U0|  24 |     SORT UNIQUE                    |             |ITPUB个人空间R)W|0X0zNP+X4w
|  25 |      MERGE JOIN                    |             |
X,T"e2R?v*MWn0|  26 |       SORT JOIN                    |             |ITPUB个人空间4X+wFt5}cx4GjO
|  27 |        MERGE JOIN                  |             |ITPUB个人空间e| t3bb ]#gO
|  28 |         SORT JOIN                  |             |
o[D|OP*D0|  29 |          FIXED TABLE FULL          | X$KSQRS     |
q(qM |)gf6@0|* 30 |         SORT JOIN                  |             |ITPUB个人空间 c.S#~S(l6Jd
|  31 |          VIEW                      | GV$_LOCK    |ITPUB个人空间-RuI"B-qTWj(C
|  32 |           UNION-ALL                |             |
:l(U? y5rM"]\0|* 33 |            FILTER                  |             |
`5U$P;xOb;p5adMG0|  34 |             VIEW                   | GV$_LOCK1   |ITPUB个人空间.c2d@#j-_V0L1w$[
|  35 |              UNION-ALL             |             |ITPUB个人空间&f"s @-v k+k5`e6A
|* 36 |               FIXED TABLE FULL     | X$KDNSSF    |
F{5Tz*C.n0|* 37 |               FIXED TABLE FULL     | X$KSQEQ     |
)d,s'`&Dt%__D0|* 38 |            FIXED TABLE FULL        | X$KTADM     |
4l:`]|mGu@0|* 39 |            FIXED TABLE FULL        | X$KTATRFIL  |ITPUB个人空间 i8T d1`'NM W9[ p
|* 40 |            FIXED TABLE FULL        | X$KTATRFSL  |ITPUB个人空间b2k8j#MAA
|* 41 |            FIXED TABLE FULL        | X$KTATL     |ITPUB个人空间a9Jx;z|S
|* 42 |            FIXED TABLE FULL        | X$KTSTUSC   |ITPUB个人空间Pf/F#T(h['[
|* 43 |            FIXED TABLE FULL        | X$KTSTUSS   |ITPUB个人空间Jz6MI2C
|* 44 |            FIXED TABLE FULL        | X$KTSTUSG   |
QG(bb:pNAH0|* 45 |            FIXED TABLE FULL        | X$KTCXB     |ITPUB个人空间4{c qU;e7{rg
|* 46 |       SORT JOIN                    |             |ITPUB个人空间` Lk{SHq-b
|* 47 |        FIXED TABLE FULL            | X$KSUSE     |
)Hp)aFX:`7Qe o0I0----------------------------------------------------------

Predicate Information (identified by operation id):
%W#D~2Qc0---------------------------------------------------

   5 - filter(("O"."TYPE#"<>1 AND "O"."TYPE#"<>10 OR "O"."TYPE#"=1 ANDITPUB个人空间 ~qa?2e:Uk|
              (SELECT 1 FROM "SYS"."IND$" "I" WHERE "I"."OBJ#"=:B1 AND ("I"."TYPE#"=1
8}kvm1j0              OR "I"."TYPE#"=2 OR "I"."TYPE#"=3 OR "I"."TYPE#"=4 OR "I"."TYPE#"=6 ORITPUB个人空间?rZ.F Z
.ITPUB个人空间@6GODUI
.
6M ~ W e"mH0.
)\'ni8H:I"b%|.|0  47 - filter("S"."INST_ID"=USERENV('INSTANCE') AND "S"."KSUSENUM"=305)

Note
F``6[r#_(R ^0-----
~3[U1qPe1_i@ V(j0   - rule based optimizer used (consider using cbo)


+H_n:?G0统计信息ITPUB个人空间p?h2? hL,l q iL p
----------------------------------------------------------
u3F)Q~~&g0         15  recursive calls
3i0FDe M m0          3  db block getsITPUB个人空间z?$r| @!D!t Z)i
     124231  consistent getsITPUB个人空间{"hU;B({ Zu(g
          0  physical readsITPUB个人空间-MZU W gRwQ6A
          0  redo sizeITPUB个人空间c W ile6W M
        667  bytes sent via SQL*Net to client
%G2{"@7Kn3y2f9Q^ E^0        520  bytes received via SQL*Net from clientITPUB个人空间I:I,O-M0{7^ I
          2  SQL*Net roundtrips to/from client
+b;ppnt#x_0          7  sorts (memory)
5H|P cz4a0          0  sorts (disk)ITPUB个人空间,P#g[ _)LP [Tg\w
          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