DBA_LMT_USED_EXTENTS视图访问缓慢

又一个访问数据字典视图缓慢的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*Ew|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%s
b3m0|   2 |   FIXED TABLE FULL | X$KTFBUE    |   100 |  9100 |     0   (0)| 00:00:01 |
u7VXoEUx*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%qdIx6Ybi
统计信息
#tEt.KM+sf,N0----------------------------------------------------------
a'M j6m#J%W*Ms0      10412  recursive callsITPUB个人空间O*B,[G:o
          4  db block getsITPUB个人空间AEb
u(?? se
m
      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
*\XOSqS0      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?xG
F0|*  1 |  FILTER                      |                |ITPUB个人空间"r
@Y|i8a
|   2 |   FIXED TABLE FULL           | X$KTFBUE       |
0d
fD6U-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'OC8g6q
k0   - rule based optimizer used (consider using cbo)

9Q~5qAI&]YG0统计信息
?"u2gEzN0----------------------------------------------------------
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 ipDY7uh0          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
^O9g
qz/_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。