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