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

DBA_LMT_USED_EXTENTS视图访问缓慢

上一篇 / 下一篇  2008-03-30 23:56:16 / 个人分类:Bug

又一个访问数据字典视图缓慢的bug

 

 

访问DBA_LMT_USED_EXTENTS视图时出现长时间等待:

SQL> SET AUTOT TRACE
bNrqV.Je7a'S0SQL> SET TIMING ON                    ITPUB个人空间{ K H3Gz3L poS
SQL> SELECT * FROM DBA_LMT_USED_EXTENTS;

已选择68686行。

已用时间:  00: 00: 56.48

执行计划
{ Aj5Y"m5al"G0----------------------------------------------------------
v[zXO|T3\H}0Plan hash value: 3481512806

----------------------------------------------------------------------------------ITPUB个人空间v D9g9H'R uS
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
!`'Olg4\7@Z0----------------------------------------------------------------------------------
S GU*E w|0|   0 | SELECT STATEMENT   |             |   100 | 10200 |     4  (25)| 00:00:01 |
I:WX*}G*?3W9h8Qy2D$H N6^0|*  1 |  HASH JOIN ANTI    |             |   100 | 10200 |     4  (25)| 00:00:01 |
%b$Ox2r%sb3m0|   2 |   FIXED TABLE FULL | X$KTFBUE    |   100 |  9100 |     0   (0)| 00:00:01 |
u7VX oEU x*C0|   3 |   TABLE ACCESS FULL| RECYCLEBIN$ |   264 |  2904 |     3   (0)| 00:00:01 |
HD!q.IQ,|#J0----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
zoe$a*Q0G4Gx2`0---------------------------------------------------

   1 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND
C } IZ:o:l)~&K@0              "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")

ITPUB个人空间:l%q dIx6Ybi
统计信息
#tEt.KM+sf,N0----------------------------------------------------------
a'M j6m#J%W*Ms0      10412  recursive callsITPUB个人空间O*B,[G:o
          4  db block getsITPUB个人空间AEbu(?? sem
      25035  consistent gets
!|l'L5a\D0       9961  physical reads
Dc V&h,st~O_0          0  redo size
\'Y#J;N @/K0    2017076  bytes sent via SQL*Net to client
*\XO SqS0      50861  bytes received via SQL*Net from client
]6j:zC!E~N0       4581  SQL*Net roundtrips to/from client
4Vx(~!wK&c8R0          0  sorts (memory)ITPUB个人空间#G2o}lHB-u W
          0  sorts (disk)ITPUB个人空间JW)}c d(U&I H-n
      68686  rows processed

如果采用RBO方式,则速度要提高很多:

SQL> SELECT /*+ RULE */ * FROM DBA_LMT_USED_EXTENTS;

已选择68686行。

已用时间:  00: 00: 11.32

执行计划
B!`#o/XtYWlY0----------------------------------------------------------ITPUB个人空间] wQ3U5s7v
Plan hash value: 839902850

-------------------------------------------------------
(D"_{ Y)i)vr`%VV0| Id  | Operation                    | Name           |
pZWo%Nc!k!O)s,[7oBv0-------------------------------------------------------ITPUB个人空间&iWA4k,VU9a1Y s?
|   0 | SELECT STATEMENT             |                |
6b `)bN8?xGF0|*  1 |  FILTER                      |                |ITPUB个人空间"r@Y|i8a
|   2 |   FIXED TABLE FULL           | X$KTFBUE       |
0dfD6U-b i/k0F^0|*  3 |   TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$    |
~!b+An\:{ f0|*  4 |    INDEX RANGE SCAN          | RECYCLEBIN$_TS |ITPUB个人空间X`N3QBdP_
-------------------------------------------------------

Predicate Information (identified by operation id):
.L?1TC~0---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "SYS"."RECYCLEBIN$" "RB" WHEREITPUB个人空间%k JY:\'z+J#_
              "RB"."TS#"=:B1 AND "RB"."BLOCK#"=:B2 AND "RB"."FILE#"=:B3))ITPUB个人空间n DA!LM8[-B
   3 - filter("RB"."BLOCK#"=:B1 AND "RB"."FILE#"=:B2)ITPUB个人空间 uobAF$]{
   4 - access("RB"."TS#"=:B1)

NoteITPUB个人空间7C,x5S0`ml2N
-----
^"n5T'OC8g6qk0   - rule based optimizer used (consider using cbo)


9Q~5qAI&]YG0
统计信息
?"u2g EzN0----------------------------------------------------------
3d:T)Pek0      10412  recursive callsITPUB个人空间yUR_,pP"u4c
          4  db block getsITPUB个人空间2Y#f+p6? l-I w9W;y w6b
      43753  consistent getsITPUB个人空间~y8eo2G2Pm?&C
       9900  physical reads
B];Exv ipDY7u h0          0  redo sizeITPUB个人空间7_\` w8i(@I*Bj
    1927939  bytes sent via SQL*Net to client
2p_;~(^$^S0      50861  bytes received via SQL*Net from clientITPUB个人空间l/gk:W|4V3_3qbw
       4581  SQL*Net roundtrips to/from client
^O9gqz/_0          0  sorts (memory)
,_ eQDmJ:j0          0  sorts (disk)
!Y_\p@-_"B*Z0      68686  rows processed

10秒绝大部分还是数据传递到客户端的时间。

Metalink上,描述这个bug的内容很少,只要这篇文章Bug No. 6460895里面描述了这个bug

目前Oracle的版本还没有解决这个问题,Oracle计划将在11.1.0.7中解决这个bug

 


TAG:

 

评分:0

我来说两句

显示全部

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

Open Toolbar