回收站引发ORACLE查询表空间使用缓慢
一个哥们问我 ，他们查询 表空间使用率 跑了一个多小时，这个太坑爹了，让我 帮忙优化一下。
SQL语句如下
select * from
(
select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,
datafiles,total_gb "TOTAL_SIZE_GB",
(total_gb-free_gb) "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round((100-free_gb/total_gb*100),2) "USED_PCT",
round(free_gb/total_gb*100,2) "FREE_PCT"
from dba_tablespaces ts,
(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb
from dba_free_space group by tablespace_name) fr1,
(select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles
from dba_data_files group by tablespace_name) df1
where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
)
union all
select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,
total_gb "TOTAL_SIZE_GB",
round(total_gb-free_gb,2) "USED_SIZE_GB",
free_gb "FREE_SIZE_GB",
round((100-free_gb/total_gb*100),2) "USED_PCT",
round(free_gb/total_gb*100,2) "FREE_PCT"
from (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,
(select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2
union all
select null,null,null,null,null,null,null,null,null from dual;
这个SQL挺简单的， 就是访问几个数据字典而已，执行计划如下
SQL> set autotrace traceonly;
SQL> select * from
2 (
3 select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
4 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,
5 datafiles,total_gb "TOTAL_SIZE_GB",
6 (total_gb-free_gb) "USED_SIZE_GB",
7 free_gb "FREE_SIZE_GB",
8 round((100-free_gb/total_gb*100),2) "USED_PCT",
9 round(free_gb/total_gb*100,2) "FREE_PCT"
from dba_tablespaces ts,
10 11 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb
12 from dba_free_space group by tablespace_name) fr1,
13 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles
14 from dba_data_files group by tablespace_name) df1
15 where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
16 )
17 union all
18 select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,
19 total_gb "TOTAL_SIZE_GB",
20 round(total_gb-free_gb,2) "USED_SIZE_GB",
21 free_gb "FREE_SIZE_GB",
22 round((100-free_gb/total_gb*100),2) "USED_PCT",
23 round(free_gb/total_gb*100,2) "FREE_PCT"
24 from (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,
25 (select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2
26 union all
27 select null,null,null,null,null,null,null,null,null from dual;
31 rows selected.
Elapsed: 00:50:32.18
Execution Plan
----------------------------------------------------------
Plan hash value: 3463738489
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 140 | 15495 | | 5104K(100)| 17:00:53 |
| 1 | UNION-ALL | | | | | | |
| 2 | VIEW | | 138 | 15456 | | 2552K(100)| 08:30:27 |
| 3 | SORT ORDER BY | | 138 | 13248 | | 2552K(100)| 08:30:27 |
|* 4 | HASH JOIN | | 138 | 13248 | | 2552K(100)| 08:30:27 |
| 5 | NESTED LOOPS | | 2 | 132 | | 7 (15)| 00:00:01 |
| 6 | VIEW | | 2 | 86 | | 5 (20)| 00:00:01 |
| 7 | HASH GROUP BY | | 2 | 60 | | 5 (20)| 00:00:01 |
| 8 | VIEW | DBA_DATA_FILES | 2 | 60 | | 4 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | | |
| 10 | NESTED LOOPS | | 1 | 236 | | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|* 14 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|* 16 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 17 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 19 | NESTED LOOPS | | 1 | 279 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 234 | | 0 (0)| 00:00:01 |
|* 23 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|* 24 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| 00:00:01 |
|* 25 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|* 27 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 28 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 30 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 23 | | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_TS1 | 1 | | | 0 (0)| 00:00:01 |
| 32 | VIEW | | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 33 | HASH GROUP BY | | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 34 | VIEW | DBA_FREE_SPACE | 3507 | 102K| | 2552K(100)| 08:30:27 |
| 35 | UNION-ALL | | | | | | |
| 36 | NESTED LOOPS | | 1 | 71 | | 4 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 40 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 70 | 5390 | | 11 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 70 | 4900 | | 11 (0)| 00:00:01 |
|* 43 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 44 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| 00:00:01 |
|* 45 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
| 46 | NESTED LOOPS | | 3435 | 385K| | 2552K(100)| 08:30:26 |
| 47 | NESTED LOOPS | | 96156 | 9M| | 2552K(100)| 08:30:26 |
|* 48 | HASH JOIN | | 354K| 14M| 8632K| 1538 (1)| 00:00:19 |
| 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 (1)| 00:00:14 |
|* 50 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 51 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 53 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 54 | NESTED LOOPS | | 1 | 96 | | 17 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 84 | | 15 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 57 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 59 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 61 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 (0)| 00:00:01 |
| 62 | NESTED LOOPS | | 1 | 39 | | 2552K(100)| 08:30:27 |
| 63 | VIEW | | 1 | 13 | | 2552K(100)| 08:30:27 |
| 64 | SORT AGGREGATE | | 1 | 13 | | | |
| 65 | VIEW | DBA_FREE_SPACE | 3507 | 45591 | | 2552K(100)| 08:30:27 |
| 66 | UNION-ALL | | | | | | |
| 67 | NESTED LOOPS | | 1 | 57 | | 4 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 69 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 71 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
| 72 | NESTED LOOPS | | 70 | 4410 | | 11 (0)| 00:00:01 |
| 73 | NESTED LOOPS | | 70 | 3920 | | 11 (0)| 00:00:01 |
|* 74 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 75 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| 00:00:01 |
|* 76 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
| 77 | NESTED LOOPS | | 3435 | 338K| | 2552K(100)| 08:30:26 |
| 78 | NESTED LOOPS | | 96156 | 8826K| | 2552K(100)| 08:30:26 |
|* 79 | HASH JOIN | | 354K| 9M| 8632K| 1538 (1)| 00:00:19 |
| 80 | TABLE ACCESS FULL | RECYCLEBIN$ | 368K| 4314K| | 1103 (1)| 00:00:14 |
|* 81 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 82 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 84 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 85 | NESTED LOOPS | | 1 | 82 | | 17 (0)| 00:00:01 |
| 86 | NESTED LOOPS | | 1 | 70 | | 15 (0)| 00:00:01 |
| 87 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 88 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| 00:00:01 |
|* 90 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
|* 92 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 61368 | | | 2 (0)| 00:00:01 |
| 93 | VIEW | | 1 | 26 | | 4 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 13 | | | |
| 95 | VIEW | DBA_DATA_FILES | 2 | 26 | | 4 (0)| 00:00:01 |
| 96 | UNION-ALL | | | | | | |
| 97 | NESTED LOOPS | | 1 | 222 | | 2 (0)| 00:00:01 |
| 98 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 99 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|*100 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|*101 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|*103 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 104 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 106 | NESTED LOOPS | | 1 | 265 | | 2 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 108 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 109 | NESTED LOOPS | | 1 | 234 | | 0 (0)| 00:00:01 |
|*110 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| 00:00:01 |
|*111 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| 00:00:01 |
|*112 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|*113 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| 00:00:01 |
|*114 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| 00:00:01 |
| 115 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| 00:00:01 |
| 117 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TS"."NAME"="FR1"."TABLESPACE_NAME")
13 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
14 - filter("F"."SPARE1" IS NULL)
15 - access("FNFNO"="F"."FILE#")
16 - filter("FE"."FENUM"="F"."FILE#")
18 - access("F"."TS#"="TS"."TS#")
23 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
24 - filter("FNFNO"="HC"."KTFBHCAFNO")
25 - filter("F"."SPARE1" IS NOT NULL)
26 - access("FNFNO"="F"."FILE#")
27 - filter("FE"."FENUM"="F"."FILE#")
29 - access("HC"."KTFBHCTSN"="TS"."TS#")
30 - filter("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048)
31 - access("TS"."NAME"="DF1"."TABLESPACE_NAME")
39 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
43 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
44 - filter("TS"."TS#"="F"."KTFBFETSN")
45 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
48 - access("TS"."TS#"="RB"."TS#")
50 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
51 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
52 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
53 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
59 - filter("TS"."BITMAPPED"=0)
60 - access("TS"."TS#"="U"."TS#")
61 - access("U"."TS#"="RB"."TS#")
70 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
71 - filter("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#")
74 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
75 - filter("TS"."TS#"="F"."KTFBFETSN")
76 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
79 - access("TS"."TS#"="RB"."TS#")
81 - filter(("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4) AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0)
82 - filter("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#")
83 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
84 - filter("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
89 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
90 - filter("TS"."BITMAPPED"=0)
91 - access("TS"."TS#"="U"."TS#")
92 - access("U"."TS#"="RB"."TS#")
100 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
101 - filter("F"."SPARE1" IS NULL)
102 - access("FNFNO"="F"."FILE#")
103 - filter("FE"."FENUM"="F"."FILE#")
105 - access("F"."TS#"="TS"."TS#")
110 - filter("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND BITAND("FNFLG",4)<>4)
111 - filter("FNFNO"="HC"."KTFBHCAFNO")
112 - filter("F"."SPARE1" IS NOT NULL)
113 - access("FNFNO"="F"."FILE#")
114 - filter("FE"."FENUM"="F"."FILE#")
116 - access("HC"."KTFBHCTSN"="TS"."TS#")
Statistics
----------------------------------------------------------
18098002 recursive calls 1800W 的递归调用
7676 db block gets
3986596 consistent gets 398W 逻辑读
790235 physical reads 79W 物理读
0 redo size
2730 bytes sent via SQL*Net to client
514 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
31 rows processed
这一次他跑了 50分钟，不过这个也太坑爹了，查询一个表空间使用率居然都这么久 ，抓狂去吧
我们来看一下执行计划， 看到 关键东西没， RECYCLEBIN$ ----这玩儿368K
于是让他 purge dba_recyclebin --这个操作执行了13个小时左右 ，可见他们系统 回收站表只多。。。。
清理完之后，执行SQL
SQL> set timing on
SQL> col tablespace_name format a30;
SQL> col monitor_date format a20;
SQL> select * from
2 (
3 select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
4 to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,
5 datafiles,total_gb "TOTAL_SIZE_GB",
6 (total_gb-free_gb) "USED_SIZE_GB",
7 free_gb "FREE_SIZE_GB",
8 round((100-free_gb/total_gb*100),2) "USED_PCT",
9 round(free_gb/total_gb*100,2) "FREE_PCT"
10 from dba_tablespaces ts,
11 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb
12 from dba_free_space group by tablespace_name) fr1,
13 (select tablespace_name,round(sum(bytes)/1024/1024/1024,2) total_gb,count(*) datafiles
14 from dba_data_files group by tablespace_name) df1
15 where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by "FREE_PCT"
16 )
17 union all
18 select 'TOTAL SUMMARY:','ALL',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date,datafiles,
19 total_gb "TOTAL_SIZE_GB",
20 round(total_gb-free_gb,2) "USED_SIZE_GB",
21 free_gb "FREE_SIZE_GB",
22 round((100-free_gb/total_gb*100),2) "USED_PCT",
23 round(free_gb/total_gb*100,2) "FREE_PCT"
24 from (select round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space) fr2,
25 (select count(*) datafiles,round(sum(bytes)/1024/1024/1024,2) total_gb from dba_data_files) df2
26 union all
27 select null,null,null,null,null,null,null,null,null from dual;
......
31 rows selected.
Elapsed: 00:00:14.28 --14.28S 神啊！！
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 22d5kxqss44ws, child number 0
-------------------------------------
select * from ( select ts.tablespace_name,ts.contents "TABLESPACE_TYPE",
to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') monitor_date, datafiles,total_gb
"TOTAL_SIZE_GB", (total_gb-free_gb) "USED_SIZE_GB", free_gb
"FREE_SIZE_GB", round((100-free_gb/total_gb*100),2) "USED_PCT",
round(free_gb/total_gb*100,2) "FREE_PCT" from dba_tablespaces ts, (select
tablespace_name,round(sum(bytes)/1024/1024/1024,2) free_gb from dba_free_space group by
tablespace_name) fr1, (select tablespace_name,round(sum(bytes)/1024/1024/1024,2)
total_gb,count(*) datafiles from dba_data_files group by tablespace_name) df1
where ts.tablespace_name=fr1.tablespace_name and ts.tablespace_name=df1.tablespace_name order by
"FREE_PCT" ) union all select 'TOTAL SUMMARY:','
Plan hash value: 3463738489
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | 3804K(100)| |
| 1 | UNION-ALL | | | | | | |
| 2 | VIEW | | 138 | 15456 | | 1902K(100)| 06:20:27 |
| 3 | SORT ORDER BY | | 138 | 13248 | | 1902K(100)| 06:20:27 |
|* 4 | HASH JOIN | | 138 | 13248 | | 1902K(100)| 06:20:27 |
| 5 | NESTED LOOPS | | 2 | 132 | | 7 (15)| 00:00:01 |
| 6 | VIEW | | 2 | 86 | | 5 (20)| 00:00:01 |
| 7 | HASH GROUP BY | | 2 | 60 | | 5 (20)| 00:00:01 |
| 8 | VIEW | DBA_DATA_FILES | 2 | 60 | | 4 (0)| 00:00:01 |
| 9 | UNION-ALL | | | | | | |
| 10 | NESTED LOOPS | | 1 | 236 | | 2 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|* 13 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| |
|* 14 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| |
|* 16 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| |
| 17 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
| 19 | NESTED LOOPS | | 1 | 279 | | 2 (0)| 00:00:01 |
| 20 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 21 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 22 | NESTED LOOPS | | 1 | 234 | | 0 (0)| |
|* 23 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| |
|* 24 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| |
|* 25 | TABLE ACCESS BY INDEX ROWID| FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 26 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| |
|* 27 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| |
| 28 | TABLE ACCESS CLUSTER | TS$ | 1 | 21 | | 1 (0)| 00:00:01 |
|* 29 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
|* 30 | TABLE ACCESS BY INDEX ROWID | TS$ | 1 | 23 | | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | I_TS1 | 1 | | | 0 (0)| |
| 32 | VIEW | | 3501 | 102K| | 1902K(100)| 06:20:27 |
| 33 | HASH GROUP BY | | 3501 | 102K| | 1902K(100)| 06:20:27 |
| 34 | VIEW | DBA_FREE_SPACE | 3501 | 102K| | 1902K(100)| 06:20:27 |
| 35 | UNION-ALL | | | | | | |
| 36 | NESTED LOOPS | | 1 | 71 | | 4 (0)| 00:00:01 |
| 37 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 40 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
| 41 | NESTED LOOPS | | 70 | 5390 | | 11 (0)| 00:00:01 |
| 42 | NESTED LOOPS | | 70 | 4900 | | 11 (0)| 00:00:01 |
|* 43 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 44 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| |
|* 45 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
| 46 | NESTED LOOPS | | 3429 | 385K| | 1902K(100)| 06:20:27 |
| 47 | NESTED LOOPS | | 96108 | 9M| | 1902K(100)| 06:20:27 |
|* 48 | HASH JOIN | | 263K| 10M| 6440K| 1531 (1)| 00:00:19 |
| 49 | TABLE ACCESS FULL | RECYCLEBIN$ | 274K| 3216K| | 1204 (1)| 00:00:15 |
|* 50 | TABLE ACCESS FULL | TS$ | 29 | 899 | | 11 (0)| 00:00:01 |
|* 51 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 52 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 53 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 54 | NESTED LOOPS | | 1 | 96 | | 17 (0)| 00:00:01 |
| 55 | NESTED LOOPS | | 1 | 84 | | 15 (0)| 00:00:01 |
| 56 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 57 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 58 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 59 | TABLE ACCESS CLUSTER | TS$ | 1 | 25 | | 1 (0)| 00:00:01 |
|* 60 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
|* 61 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 54903 | | | 2 (0)| 00:00:01 |
| 62 | NESTED LOOPS | | 1 | 39 | | 1902K(100)| 06:20:27 |
| 63 | VIEW | | 1 | 13 | | 1902K(100)| 06:20:27 |
| 64 | SORT AGGREGATE | | 1 | 13 | | | |
| 65 | VIEW | DBA_FREE_SPACE | 3501 | 45513 | | 1902K(100)| 06:20:27 |
| 66 | UNION-ALL | | | | | | |
| 67 | NESTED LOOPS | | 1 | 57 | | 4 (0)| 00:00:01 |
| 68 | NESTED LOOPS | | 1 | 46 | | 3 (0)| 00:00:01 |
| 69 | TABLE ACCESS FULL | FET$ | 1 | 39 | | 3 (0)| 00:00:01 |
|* 70 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 71 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
| 72 | NESTED LOOPS | | 70 | 4410 | | 11 (0)| 00:00:01 |
| 73 | NESTED LOOPS | | 70 | 3920 | | 11 (0)| 00:00:01 |
|* 74 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 75 | FIXED TABLE FIXED INDEX | X$KTFBFE (ind:1) | 2 | 78 | | 0 (0)| |
|* 76 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
| 77 | NESTED LOOPS | | 3429 | 338K| | 1902K(100)| 06:20:27 |
| 78 | NESTED LOOPS | | 96108 | 8822K| | 1902K(100)| 06:20:27 |
|* 79 | HASH JOIN | | 263K| 7471K| 6440K| 1531 (1)| 00:00:19 |
| 80 | TABLE ACCESS FULL | RECYCLEBIN$ | 274K| 3216K| | 1204 (1)| 00:00:15 |
|* 81 | TABLE ACCESS FULL | TS$ | 29 | 493 | | 11 (0)| 00:00:01 |
|* 82 | FIXED TABLE FIXED INDEX | X$KTFBUE (ind:1) | 1 | 65 | | 7 (100)| 00:00:01 |
|* 83 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 84 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 1 | 12 | | 2 (0)| 00:00:01 |
| 85 | NESTED LOOPS | | 1 | 82 | | 17 (0)| 00:00:01 |
| 86 | NESTED LOOPS | | 1 | 70 | | 15 (0)| 00:00:01 |
| 87 | NESTED LOOPS | | 1 | 59 | | 14 (0)| 00:00:01 |
| 88 | TABLE ACCESS FULL | UET$ | 1 | 52 | | 14 (0)| 00:00:01 |
|* 89 | INDEX UNIQUE SCAN | I_FILE2 | 1 | 7 | | 0 (0)| |
|* 90 | TABLE ACCESS CLUSTER | TS$ | 1 | 11 | | 1 (0)| 00:00:01 |
|* 91 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
|* 92 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 54903 | | | 2 (0)| 00:00:01 |
| 93 | VIEW | | 1 | 26 | | 4 (0)| 00:00:01 |
| 94 | SORT AGGREGATE | | 1 | 13 | | | |
| 95 | VIEW | DBA_DATA_FILES | 2 | 26 | | 4 (0)| 00:00:01 |
| 96 | UNION-ALL | | | | | | |
| 97 | NESTED LOOPS | | 1 | 222 | | 2 (0)| 00:00:01 |
| 98 | NESTED LOOPS | | 1 | 215 | | 1 (0)| 00:00:01 |
| 99 | NESTED LOOPS | | 1 | 202 | | 1 (0)| 00:00:01 |
|*100 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| |
|*101 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 20 | | 1 (0)| 00:00:01 |
|*102 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| |
|*103 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| |
| 104 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*105 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
| 106 | NESTED LOOPS | | 1 | 265 | | 2 (0)| 00:00:01 |
| 107 | NESTED LOOPS | | 1 | 258 | | 1 (0)| 00:00:01 |
| 108 | NESTED LOOPS | | 1 | 245 | | 1 (0)| 00:00:01 |
| 109 | NESTED LOOPS | | 1 | 234 | | 0 (0)| |
|*110 | FIXED TABLE FULL | X$KCCFN | 1 | 182 | | 0 (0)| |
|*111 | FIXED TABLE FIXED INDEX | X$KTFBHC (ind:1) | 1 | 52 | | 0 (0)| |
|*112 | TABLE ACCESS BY INDEX ROWID | FILE$ | 1 | 11 | | 1 (0)| 00:00:01 |
|*113 | INDEX UNIQUE SCAN | I_FILE1 | 1 | | | 0 (0)| |
|*114 | FIXED TABLE FIXED INDEX | X$KCCFE (ind:1) | 1 | 13 | | 0 (0)| |
| 115 | TABLE ACCESS CLUSTER | TS$ | 1 | 7 | | 1 (0)| 00:00:01 |
|*116 | INDEX UNIQUE SCAN | I_TS# | 1 | | | 0 (0)| |
| 117 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("TS"."NAME"="FR1"."TABLESPACE_NAME")
13 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
14 - filter("F"."SPARE1" IS NULL)
15 - access("FNFNO"="F"."FILE#")
16 - filter("FE"."FENUM"="F"."FILE#")
18 - access("F"."TS#"="TS"."TS#")
23 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
24 - filter("FNFNO"="HC"."KTFBHCAFNO")
25 - filter("F"."SPARE1" IS NOT NULL)
26 - access("FNFNO"="F"."FILE#")
27 - filter("FE"."FENUM"="F"."FILE#")
29 - access("HC"."KTFBHCTSN"="TS"."TS#")
30 - filter(("TS"."ONLINE$"<>3 AND BITAND("FLAGS",2048)<>2048))
31 - access("TS"."NAME"="DF1"."TABLESPACE_NAME")
39 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
40 - filter(("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#"))
43 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0))
44 - filter("TS"."TS#"="F"."KTFBFETSN")
45 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
48 - access("TS"."TS#"="RB"."TS#")
50 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0))
51 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#"))
52 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
53 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))
58 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
59 - filter("TS"."BITMAPPED"=0)
60 - access("TS"."TS#"="U"."TS#")
61 - access("U"."TS#"="RB"."TS#")
70 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#")
71 - filter(("TS"."BITMAPPED"=0 AND "TS"."TS#"="F"."TS#"))
74 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0))
75 - filter("TS"."TS#"="F"."KTFBFETSN")
76 - access("F"."KTFBFETSN"="FI"."TS#" AND "F"."KTFBFEFNO"="FI"."RELFILE#")
79 - access("TS"."TS#"="RB"."TS#")
81 - filter((INTERNAL_FUNCTION("TS"."ONLINE$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0))
82 - filter(("U"."KTFBUESEGTSN"="RB"."TS#" AND "U"."KTFBUESEGFNO"="RB"."FILE#" AND
"U"."KTFBUESEGBNO"="RB"."BLOCK#"))
83 - access("RB"."TS#"="FI"."TS#" AND "U"."KTFBUEFNO"="FI"."RELFILE#")
84 - filter(("U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#"))
89 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#")
90 - filter("TS"."BITMAPPED"=0)
91 - access("TS"."TS#"="U"."TS#")
92 - access("U"."TS#"="RB"."TS#")
100 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
101 - filter("F"."SPARE1" IS NULL)
102 - access("FNFNO"="F"."FILE#")
103 - filter("FE"."FENUM"="F"."FILE#")
105 - access("F"."TS#"="TS"."TS#")
110 - filter(("FNNAM" IS NOT NULL AND "FNTYP"=4 AND "INST_ID"=USERENV('INSTANCE') AND
BITAND("FNFLG",4)<>4))
111 - filter("FNFNO"="HC"."KTFBHCAFNO")
112 - filter("F"."SPARE1" IS NOT NULL)
113 - access("FNFNO"="F"."FILE#")
114 - filter("FE"."FENUM"="F"."FILE#")
116 - access("HC"."KTFBHCTSN"="TS"."TS#")
201 rows selected.
现在14秒了，之前 50分钟啊。。。 也许你会问 为啥现在 RECYCLEBIN$还是有276K呢，这个别管了，不准确，你要想准确重新收集 数据字典统计信息吧。
运维DBA们，回收站还是要经常清理得好。
有系统需要优化的 加QQ群 220761024 有SQL 需要优化的 加我 QQ 692162374 注明 来自CSDN
