目前数据库查询表空间使用率非常慢,甚至要半个小时才会出结果。查询语句的执行计划如下:

SQL>explain plan for select t.*
    from (select d.tablespace_name,
                              space "sum_space(m)",
                              blocks sum_blocks,
                              space - nvl(free_space, 0) "used_space(m)",
                              round((1 - nvl(free_space, 0) / space) * 100, 2) "used_rate(%)",
                              free_space "free_space(m)"
                    from (select tablespace_name,
                                              round(sum(bytes) / (1048576), 2) space,
                                              sum(blocks) blocks
                                    from dba_data_files
                                  group by tablespace_name) d,
                              (select tablespace_name,
                                              round(sum(bytes) / (1048576), 2) free_space
                                    from dba_free_space
                                  group by tablespace_name) f
                  where d.tablespace_name = f.tablespace_name(+)
                union all --if have tempfile
                select d.tablespace_name,
                              space "sum_space(m)",
                              blocks sum_blocks,
                              used_space "used_space(m)",
                              round(nvl(used_space, 0) / space * 100, 2) "used_rate(%)",
                              space - used_space "free_space(m)"
                    from (select tablespace_name,
                                              round(sum(bytes) / (1048576), 2) space,
                                              sum(blocks) blocks
                                    from dba_temp_files
                                  group by tablespace_name) d,
                              (select tablespace,
                                              round(sum(blocks * 32768) / (1048576), 2) used_space
                                    from v$sort_usage
                                  group by tablespace) f
                  where d.tablespace_name = f.tablespace(+)) t
order by "used_rate(%)" desc;
SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
Plan hash value: 1739573977
-----------------------------------------------------------------------------------------------------------
  
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  
-----------------------------------------------------------------------------------------------------------
  
| 0 | SELECT STATEMENT | | 3 | 246 | 4386 (5)| 00:00:53 |
  
| 1 | SORT ORDER BY | | 3 | 246 | 4386 (5)| 00:00:53 |
  
| 2 | VIEW | | 3 | 246 | 4385 (5)| 00:00:53 |
  
| 3 | UNION-ALL | | | | | |
  
|* 4 | HASH JOIN OUTER | | 2 | 146 | 4382 (5)| 00:00:53 |
  
| 5 | VIEW | | 2 | 86 | 5 (20)| 00:00:01 |
  
| 6 | HASH GROUP BY | | 2 | 70 | 5 (20)| 00:00:01 |
  
| 7 | VIEW | DBA_DATA_FILES | 2 | 70 | 4 (0)| 00:00:01 |
  
| 8 | UNION-ALL | | | | | |
  
| 9 | NESTED LOOPS | | 1 | 359 | 2 (0)| 00:00:01 |
  
| 10 | NESTED LOOPS | | 1 | 343 | 1 (0)| 00:00:01 |
  
| 11 | NESTED LOOPS | | 1 | 330 | 1 (0)| 00:00:01 |
  
|* 12 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 |
  
|* 13 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | 1 (0)| 00:00:01 |
  
|* 14 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
  
|* 15 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | 0 (0)| 00:00:01 |
  
| 16 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 |
  
|* 17 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  
| 18 | NESTED LOOPS | | 1 | 402 | 2 (0)| 00:00:01 |
  
| 19 | NESTED LOOPS | | 1 | 386 | 1 (0)| 00:00:01 |
  
| 20 | NESTED LOOPS | | 1 | 373 | 1 (0)| 00:00:01 |
  
| 21 | NESTED LOOPS | | 1 | 362 | 0 (0)| 00:00:01 |
  
|* 22 | FIXED TABLE FULL | X$KCCFN | 1 | 310 | 0 (0)| 00:00:01 |
  
|* 23 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
  
|* 24 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | 1 (0)| 00:00:01 |
  
|* 25 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | 0 (0)| 00:00:01 |
  
|* 26 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | 0 (0)| 00:00:01 |
  
| 27 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 |
  
|* 28 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  
| 29 | VIEW | | 51 | 1530 | 4377 (5)| 00:00:53 |
  
| 30 | HASH GROUP BY | | 51 | 1122 | 4377 (5)| 00:00:53 |
  
| 31 | VIEW | DBA_FREE_SPACE | 18911 | 406K| 4376 (5)| 00:00:53 |
  
| 32 | UNION-ALL | | | | | |
  
| 33 | NESTED LOOPS | | 1 | 66 | 4 (0)| 00:00:01 |
  
| 34 | NESTED LOOPS | | 1 | 59 | 4 (0)| 00:00:01 |
  
| 35 | TABLE ACCESS FULL | FET$ | 1 | 39 | 4 (0)| 00:00:01 |
  
|* 36 | TABLE ACCESS CLUSTER | TS$ | 1 | 20 | 0 (0)| 00:00:01 |
  
|* 37 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  
|* 38 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 |
  
| 39 | NESTED LOOPS | | 67 | 4824 | 22 (0)| 00:00:01 |
  
| 40 | NESTED LOOPS | | 67 | 4355 | 22 (0)| 00:00:01 |
  
|* 41 | TABLE ACCESS FULL | TS$ | 51 | 1326 | 22 (0)| 00:00:01 |
  
|* 42 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 1 | 39 | 0 (0)| 00:00:01 |
  
|* 43 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 |
  
|* 44 | HASH JOIN | | 18842 | 1214K| 3127 (6)| 00:00:38 |
  
|* 45 | TABLE ACCESS FULL | TS$ | 51 | 1326 | 22 (0)| 00:00:01 |
  
|* 46 | HASH JOIN | | 18985 | 741K| 3105 (6)| 00:00:38 |
  
| 47 | INDEX FULL SCAN | I_FILE2 | 146 | 1022 | 1 (0)| 00:00:01 |
  
| 48 | NESTED LOOPS | | 887K| 27M| 3100 (6)| 00:00:38 |
  
| 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 887K| 10M| 2940 (1)| 00:00:36 |
  
|* 50 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 21 | 0 (0)| 00:00:01 |
  
| 51 | NESTED LOOPS | | 1 | 91 | 1223 (1)| 00:00:15 |
  
| 52 | NESTED LOOPS | | 49300 | 91 | 1223 (1)| 00:00:15 |
  
| 53 | NESTED LOOPS | | 1 | 79 | 23 (0)| 00:00:01 |
  
| 54 | NESTED LOOPS | | 1 | 72 | 23 (0)| 00:00:01 |
  
| 55 | TABLE ACCESS FULL | UET$ | 1 | 52 | 23 (0)| 00:00:01 |
  
|* 56 | TABLE ACCESS CLUSTER | TS$ | 1 | 20 | 0 (0)| 00:00:01 |
  
|* 57 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | 0 (0)| 00:00:01 |
  
|* 59 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 49300 | | 336 (0)| 00:00:05 |
  
|* 60 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 2 | 24 | 1200 (1)| 00:00:15 |
  
|* 61 | HASH JOIN OUTER | | 1 | 73 | 3 (67)| 00:00:01 |
  
| 62 | VIEW | | 1 | 43 | 2 (50)| 00:00:01 |
  
| 63 | HASH GROUP BY | | 1 | 417 | 2 (50)| 00:00:01 |
  
| 64 | NESTED LOOPS | | 1 | 417 | 1 (0)| 00:00:01 |
  
| 65 | NESTED LOOPS | | 1 | 365 | 1 (0)| 00:00:01 |
  
| 66 | NESTED LOOPS | | 1 | 349 | 0 (0)| 00:00:01 |
  
|* 67 | FIXED TABLE FULL | X$KCCFN | 1 | 297 | 0 (0)| 00:00:01 |
  
|* 68 | FIXED TABLE FIXED INDEX | X$KTFTHC (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
  
| 69 | TABLE ACCESS CLUSTER | TS$ | 1 | 16 | 1 (0)| 00:00:01 |
  
|* 70 | INDEX UNIQUE SCAN | I_TS# | 1 | | 0 (0)| 00:00:01 |
  
|* 71 | FIXED TABLE FIXED INDEX | X$KCCTF (ind:1) | 1 | 52 | 0 (0)| 00:00:01 |
  
| 72 | VIEW | | 1 | 30 | 1 (100)| 00:00:01 |
  
| 73 | HASH GROUP BY | | 1 | 172 | 1 (100)| 00:00:01 |
  
| 74 | NESTED LOOPS | | 1 | 172 | 0 (0)| 00:00:01 |
  
| 75 | NESTED LOOPS | | 1 | 159 | 0 (0)| 00:00:01 |
  
| 76 | MERGE JOIN CARTESIAN | | 100 | 8800 | 0 (0)| 00:00:01 |
  
|* 77 | FIXED TABLE FULL | X$KTSSO | 1 | 62 | 0 (0)| 00:00:01 |
  
| 78 | BUFFER SORT | | 100 | 2600 | 0 (0)| 00:00:01 |
  
| 79 | FIXED TABLE FULL | X$KSLWT | 100 | 2600 | 0 (0)| 00:00:01 |
  
|* 80 | FIXED TABLE FIXED INDEX | X$KSUSE (ind:1) | 1 | 71 | 0 (0)| 00:00:01 |
  
|* 81 | FIXED TABLE FIXED INDEX | X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
  
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
      4 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE_NAME"(+))
  
    12 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
  
                            BITAND("FNFLG",4)<>4)
  
    13 - filter("F"."SPARE1" IS NULL)
  
    14 - access("FNFNO"="F"."FILE#")
  
    15 - filter("FE"."FENUM"="F"."FILE#")
  
    17 - access("F"."TS#"="TS"."TS#")
  
    22 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
  
                            BITAND("FNFLG",4)<>4)
  
    23 - filter("FNFNO"="HC"."KTFBHCAFNO")
  
    24 - filter("F"."SPARE1" IS NOT NULL)
  
    25 - access("FNFNO"="F"."FILE#")
  
    26 - filter("FE"."FENUM"="F"."FILE#")
  
    28 - access("HC"."KTFBHCTSN"="TS"."TS#")
  
    36 - filter("TS"."BITMAPPED"=0)
  
    37 - access("TS"."TS#"="F"."TS#")
  
    38 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  
    41 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
  
                            "TS"."ONLINE$"=4))
  
    42 - filter("TS"."TS#"="F"."KTFBFETSN")
  
    43 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  
    44 - access("TS"."TS#"="RB"."TS#")
  
    45 - filter("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND ("TS"."ONLINE$"=1 OR
  
                            "TS"."ONLINE$"=4))
  
    46 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
  
    50 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
  
                            "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  
    56 - filter("TS"."BITMAPPED"=0)
  
    57 - access("TS"."TS#"="U"."TS#")
  
    58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
  
    59 - access("U"."TS#"="RB"."TS#")
  
    60 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
  
    61 - access("D"."TABLESPACE_NAME"="F"."TABLESPACE"(+))
  
    67 - filter("V"."FNNAM" IS NOT NULL AND "V"."FNTYP"=7)
  
    68 - filter("V"."FNFNO"="HC"."KTFTHCTFNO")
  
    70 - access("HC"."KTFTHCTSN"="TS"."TS#")
  
    71 - filter("TF"."TFDUP"<>0 AND BITAND("TF"."TFSTA",32)<>32 AND "V"."FNFNO"="TF"."TFNUM" AND
  
                            "TF"."TFFNH"="V"."FNNUM")
  
    77 - filter("X$KTSSO"."INST_ID"=USERENV('INSTANCE'))
  
    80 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
  
                            BITAND("S"."KSUSEFLG",1)<>0 AND "KTSSOSES"="S"."ADDR" AND "KTSSOSNO"="S"."KSUSESER" AND
  
                            "S"."INDX"="W"."KSLWTSID")
  
    81 - filter("W"."KSLWTEVT"="E"."INDX")

分析执行计划发现DBA_FREE_SPACE消耗了大量资源,DBA_FREE_SPACE是个视图,通过查看其创建视图的语句发现该视图关联了RECYCLEBIN$表。

RECYCLEBIN$是Oracle回收站,用来存放DROP删除的对象信息,查询发现该表有八十多万条记录,因此判断是回收站太大引起了表空间查询缓慢,建议对回收站进行定期清理。

purge dba_recylebin;