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

柔光的暖阳◎ 2022-08-22 01:25 233阅读 0赞

现场统计表空间很慢。  
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\#"))