DBA_LMT_USED_EXTENTS视图访问缓慢

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

 

 

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

SQL> SET AUTOT TRACE
SQL> SET TIMING ON                    
SQL> SELECT * FROM DBA_LMT_USED_EXTENTS;

已选择68686行。

已用时间:  00: 00: 56.48

执行计划
----------------------------------------------------------
Plan hash value: 3481512806

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   100 | 10200 |     4  (25)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |             |   100 | 10200 |     4  (25)| 00:00:01 |
|   2 |   FIXED TABLE FULL | X$KTFBUE    |   100 |  9100 |     0   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| RECYCLEBIN$ |   264 |  2904 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND
              "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")

统计信息
----------------------------------------------------------
      10412  recursive calls
          4  db block gets
      25035  consistent gets
       9961  physical reads
          0  redo size
    2017076  bytes sent via SQL*Net to client
      50861  bytes received via SQL*Net from client
       4581  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68686  rows processed

如果采用RBO方式，则速度要提高很多：

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

已选择68686行。

已用时间:  00: 00: 11.32

执行计划
----------------------------------------------------------
Plan hash value: 839902850

-------------------------------------------------------
| Id  | Operation                    | Name           |
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                |
|*  1 |  FILTER                      |                |
|   2 |   FIXED TABLE FULL           | X$KTFBUE       |
|*  3 |   TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$    |
|*  4 |    INDEX RANGE SCAN          | RECYCLEBIN$_TS |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "SYS"."RECYCLEBIN$" "RB" WHERE
              "RB"."TS#"=:B1 AND "RB"."BLOCK#"=:B2 AND "RB"."FILE#"=:B3))
   3 - filter("RB"."BLOCK#"=:B1 AND "RB"."FILE#"=:B2)
   4 - access("RB"."TS#"=:B1)

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
      10412  recursive calls
          4  db block gets
      43753  consistent gets
       9900  physical reads
          0  redo size
    1927939  bytes sent via SQL*Net to client
      50861  bytes received via SQL*Net from client
       4581  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      68686  rows processed

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

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

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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-220312/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-220312/