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

来源：互联网发布：网络上hz缩写什么意思编辑：程序博客网时间：2024/05/16 10:33

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

0 0