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

最新推荐文章于 2021-04-22 14:40:30 发布

bamuta

最新推荐文章于 2021-04-22 14:40:30 发布

阅读量1.8k

点赞数

分类专栏： Oracle Oracle 优化

本文链接：https://blog.csdn.net/bamuta/article/details/19542397

版权

本文探讨了在Oracle 10g中，由于recyclebin导致dba_free_space查询性能下降的问题。通过展示优化前的执行计划，并详细说明了解决这一问题的方法。

摘要由CSDN通过智能技术生成

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 - ProductionSQL如下：
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: