回收站引发ORACLE查询表空间使用缓慢问题(六)
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
[html] 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 | -------------------------------------------