oracle 10g recyclebin引起的dba_free_space性能问题

oracle 10g recyclebin中数据太多引起的dba_free_space执行非常缓慢。最后通过清理recyclebin进行解决。SQL> select * from V$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64biPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0ProductionTNS for 64-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production

SQL如下：

select tablespace_name,sum(bytes) from dba_free_space group by tablespace_name;

原来执行大概需要23s，

优化前的执行计划

详细的执行计划如下：

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  HASH GROUP BY                   |                  |      1 |    187 |     44 |00:00:22.63 |   20269 |   4693 |       |       |          |
|   2 |   VIEW                           | DBA_FREE_SPACE   |      1 |    187 |   9464 |00:00:22.59 |   20269 |   4693 |       |       |          |
|   3 |    UNION-ALL                     |                  |      1 |        |   9464 |00:00:22.56 |   20269 |   4693 |       |       |          |
|   4 |     NESTED LOOPS                 |                  |      1 |      1 |      0 |00:00:00.01 |      55 |      0 |       |       |          |
|   5 |      NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |      55 |      0 |       |       |          |
|   6 |       TABLE ACCESS FULL          | FET$             |      1 |      1 |      0 |00:00:00.01 |      55 |      0 |       |       |          |
|*  7 |       INDEX UNIQUE SCAN          | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  8 |      TABLE ACCESS CLUSTER        | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|   9 |     NESTED LOOPS                 |                  |      1 |     90 |    362 |00:00:00.02 |     320 |      0 |       |       |          |
|  10 |      NESTED LOOPS                |                  |      1 |     90 |    362 |00:00:00.01 |     318 |      0 |       |       |          |
|* 11 |       TABLE ACCESS FULL          | TS$              |      1 |     44 |     44 |00:00:00.01 |      55 |      0 |       |       |          |
|* 12 |       FIXED TABLE FIXED INDEX    | X$KTFBFE (ind:1) |     44 |      2 |    362 |00:00: