Oracle 11g统计表空间使用率很慢

现场统计表空间很慢。 SELECT T1.TABLESPACE_NAME, TOTAL_SPACE, TOTAL_SPACE - FREE_SPACE USED_SPACE, FREE_SPACE FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) T1, (SELECT

  现场统计表空间很慢。
SELECT T1.TABLESPACE_NAME,
       TOTAL_SPACE,
       TOTAL_SPACE - FREE_SPACE USED_SPACE,
       FREE_SPACE
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 TOTAL_SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) T1,
       (SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) T2

 WHERE T1.TABLESPACE_NAME = T2.TABLESPACE_NAME;

主要是这条SQL慢，花了五分半钟，看是和垃圾回收站相关的表。

 SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 FREE_SPACE
     FROM DBA_FREE_SPACE          GROUP BY TABLESPACE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |     12 |00:05:29.43 |     561K|    102K|       |       |          |
|   1 |  HASH GROUP BY                  |                  |      1 |      7 |     12 |00:05:29.43 |     561K|    102K|  3532K|  1450K| 1099K (0)|
|   2 |   VIEW                          | DBA_FREE_SPACE   |      1 |  13208 |  35897 |00:05:29.42 |     561K|    102K|       |       |          |
|   3 |    UNION-ALL                    |                  |      1 |        |  35897 |00:05:29.41 |     561K|    102K|       |       |          |
|   4 |     NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|   5 |      NESTED LOOPS               |                  |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|   6 |       TABLE ACCESS CLUSTER      | FET$             |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|   7 |        INDEX FULL SCAN          | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |      0 |       |       |          |
|*  8 |       TABLE ACCESS CLUSTER      | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN        | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN          | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|  11 |     NESTED LOOPS                |                  |      1 |  13126 |  13608 |00:00:00.18 |    1864 |      0 |       |       |          |
|  12 |      NESTED LOOPS               |                  |      1 |  13126 |  13608 |00:00:00.16 |    1860 |      0 |       |       |          |
|* 13 |       TABLE ACCESS CLUSTER      | TS$              |      1 |     12 |     12 |00:00:00.01 |      19 |      0 |       |       |          |
|  14 |        INDEX FULL SCAN          | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |      0 |       |       |          |
|* 15 |       FIXED TABLE FIXED INDEX   | X$KTFBFE (ind:1) |     12 |   1059 |  13608 |00:00:00.15 |    1841 |      0 |       |       |          |
|* 16 |      INDEX UNIQUE SCAN          | I_FILE2          |  13608 |      1 |  13608 |00:00:00.01 |       4 |      0 |       |       |          |
|* 17 |     HASH JOIN                   |                  |      1 |     80 |  22289 |00:05:29.22 |     559K|    102K|  2461K|  2461K| 2209K (0)|
|  18 |      NESTED LOOPS               |                  |      1 |     84 |  22289 |00:05:29.20 |     559K|    102K|       |       |          |
|* 19 |       HASH JOIN                 |                  |      1 |   5807 |  22289 |00:05:29.14 |     559K|    102K|  1557K|  1557K| 1673K (0)|
|  20 |        TABLE ACCESS FULL        | RECYCLEBIN$      |      1 |   5807 |   5807 |00:00:00.01 |     529 |      0 |       |       |          |
|  21 |        FIXED TABLE FULL         | X$KTFBUE         |      1 |    100K|    990K|00:05:28.61 |     558K|    102K|       |       |          |
|* 22 |       INDEX UNIQUE SCAN         | I_FILE2          |  22289 |      1 |  22289 |00:00:00.04 |       4 |      0 |       |       |          |
|* 23 |      TABLE ACCESS CLUSTER       | TS$              |      1 |     12 |     12 |00:00:00.01 |      19 |      0 |       |       |          |
|  24 |       INDEX FULL SCAN           | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |      0 |       |       |          |
|  25 |     NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|  26 |      NESTED LOOPS               |                  |      1 |    968 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|  27 |       NESTED LOOPS              |                  |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|  28 |        NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|* 29 |         TABLE ACCESS CLUSTER    | TS$              |      1 |      1 |      0 |00:00:00.01 |      19 |      0 |       |       |          |
|  30 |          INDEX FULL SCAN        | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |      0 |       |       |          |
|  31 |         TABLE ACCESS CLUSTER    | UET$             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 32 |          INDEX RANGE SCAN       | I_FILE#_BLOCK#   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 33 |        INDEX UNIQUE SCAN        | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 34 |       INDEX RANGE SCAN          | RECYCLEBIN$_TS   |      0 |    968 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
|* 35 |      TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |      0 |      2 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - filter("TS"."BITMAPPED"=0)
   9 - access("TS"."TS#"="F"."TS#")
  10 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  13 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"0))
  15 - filter("TS"."TS#"="F"."KTFBFETSN")
  16 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  17 - access("TS"."TS#"="RB"."TS#")
  19 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  22 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
  23 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"0))
  29 - filter("TS"."BITMAPPED"=0)
  32 - access("TS"."TS#"="U"."TS#")
  33 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
  34 - access("U"."TS#"="RB"."TS#")
  35 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))


SQL>  purge dba_recyclebin;
SQL>  SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 FREE_SPACE
              FROM DBA_FREE_SPACE
             GROUP BY TABLESPACE_NAME;

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));    

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  81s4p9cv3060h, child number 0
-------------------------------------
 SELECT TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024/ 1024 FREE_SPACE
     FROM DBA_FREE_SPACE          GROUP BY TABLESPACE_NAME
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |     12 |00:00:00.18 |    2431 |       |       |          |
|   1 |  HASH GROUP BY                  |                  |      1 |      7 |     12 |00:00:00.18 |    2431 |  1903K|  1450K| 1103K (0)|
|   2 |   VIEW                          | DBA_FREE_SPACE   |      1 |  13208 |  13971 |00:00:00.18 |    2431 |       |       |          |
|   3 |    UNION-ALL                    |                  |      1 |        |  13971 |00:00:00.18 |    2431 |       |       |          |
|   4 |     NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|   5 |      NESTED LOOPS               |                  |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|   6 |       TABLE ACCESS CLUSTER      | FET$             |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|   7 |        INDEX FULL SCAN          | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |       |       |          |
|*  8 |       TABLE ACCESS CLUSTER      | TS$              |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |        INDEX UNIQUE SCAN        | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 10 |      INDEX UNIQUE SCAN          | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |     NESTED LOOPS                |                  |      1 |  13126 |  13971 |00:00:00.17 |    1864 |       |       |          |
|  12 |      NESTED LOOPS               |                  |      1 |  13126 |  13971 |00:00:00.15 |    1860 |       |       |          |
|* 13 |       TABLE ACCESS CLUSTER      | TS$              |      1 |     12 |     12 |00:00:00.01 |      19 |       |       |          |
|  14 |        INDEX FULL SCAN          | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |       |       |          |
|* 15 |       FIXED TABLE FIXED INDEX   | X$KTFBFE (ind:1) |     12 |   1059 |  13971 |00:00:00.15 |    1841 |       |       |          |
|* 16 |      INDEX UNIQUE SCAN          | I_FILE2          |  13971 |      1 |  13971 |00:00:00.01 |       4 |       |       |          |
|* 17 |     HASH JOIN                   |                  |      1 |     80 |      0 |00:00:00.01 |     529 |  1969K|  1969K|  360K (0)|
|  18 |      NESTED LOOPS               |                  |      1 |     84 |      0 |00:00:00.01 |     529 |       |       |          |
|* 19 |       HASH JOIN                 |                  |      1 |   5807 |      0 |00:00:00.01 |     529 |  1557K|  1557K|  496K (0)|
|  20 |        TABLE ACCESS FULL        | RECYCLEBIN$      |      1 |   5807 |      0 |00:00:00.01 |     529 |       |       |          |
|  21 |        FIXED TABLE FULL         | X$KTFBUE         |      0 |    100K|      0 |00:00:00.01 |       0 |       |       |          |
|* 22 |       INDEX UNIQUE SCAN         | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 23 |      TABLE ACCESS CLUSTER       | TS$              |      0 |     12 |      0 |00:00:00.01 |       0 |       |       |          |
|  24 |       INDEX FULL SCAN           | I_TS#            |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|  25 |     NESTED LOOPS                |                  |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|  26 |      NESTED LOOPS               |                  |      1 |    968 |      0 |00:00:00.01 |      19 |       |       |          |
|  27 |       NESTED LOOPS              |                  |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|  28 |        NESTED LOOPS             |                  |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|* 29 |         TABLE ACCESS CLUSTER    | TS$              |      1 |      1 |      0 |00:00:00.01 |      19 |       |       |          |
|  30 |          INDEX FULL SCAN        | I_TS#            |      1 |      1 |     18 |00:00:00.01 |       1 |       |       |          |
|  31 |         TABLE ACCESS CLUSTER    | UET$             |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 32 |          INDEX RANGE SCAN       | I_FILE#_BLOCK#   |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 33 |        INDEX UNIQUE SCAN        | I_FILE2          |      0 |      1 |      0 |00:00:00.01 |       0 |       |       |          |
|* 34 |       INDEX RANGE SCAN          | RECYCLEBIN$_TS   |      0 |    968 |      0 |00:00:00.01 |       0 |       |       |          |
|* 35 |      TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$      |      0 |      2 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   8 - filter("TS"."BITMAPPED"=0)
   9 - access("TS"."TS#"="F"."TS#")
  10 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
  13 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"0))
  15 - filter("TS"."TS#"="F"."KTFBFETSN")
  16 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
  17 - access("TS"."TS#"="RB"."TS#")
  19 - access("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND "U"."KTFBUESEGBNO"="RB"."BLOCK#")
  22 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
  23 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"0))
  29 - filter("TS"."BITMAPPED"=0)
  32 - access("TS"."TS#"="U"."TS#")
  33 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
  34 - access("U"."TS#"="RB"."TS#")
  35 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))