我们在查询数据文件的剩余空间或者查询表空间使用率时，可能会碰见查询很慢的情况，主要原因是在于查询表空间使用情况的时候，需要从dba_free_space视图中获取剩余空间大小。
如果该视图中对象过多，查询dba_free_space视图的效率特别低，就会造成查询缓慢的现象。有时候在系统运行很长一段时间后，我们再去查询表空间使用情况，发现相同的查询语句执行时间会变长，也是同样的原因造成的。

1、查看回收站的记录数

sys@orcl(2776)> select count(*) from dba_recyclebin;

  COUNT(*)
----------
     20000

已用时间:  00: 00: 00.08

当前回收站有20000条记录，我们看一下查询表空间使用，需要花多少时间？


上图可以看到，查询表空间花了27s左右。

2、查询缓慢的原因

我们看一下查询dba_free_space需要花多少时间：

select /*+ gather_plan_statistics */count(1) from dba_free_space;

查看dba_free_space记录数，要花31s左右。

官方这个文档 Queries on DBA_FREE_SPACE are Slow (文档 ID 271169.1) 有说明 dba_free_space 是个视图，基表中有：sys.recyclebin$，当回收站中内容过大，会拖慢对该视图的查询。

看下查询语句的执行计划，主要耗时在哪里？

set linesize 200 pagesize 999
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

上图可以看到，执行计划主要耗时是在16-24行。

3、解决方法

业务用户清理回收站：Purge the recyclebin.
For example:
SQL> purge recyclebin;
Recyclebin purged.
Or, as SYSDBA for system wide purging. --或sysdba用户清理全局回收站
SQL> purge dba_recyclebin;
Recyclebin purged.

--清空回收站
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from dba_recyclebin;
 COUNT(1)
----------
 0

–清空回收站后，重新查询表空间使用情况，1s不到就查询出了。

–此时重新看下查询dba_free_space的执行计划

select /*+ gather_plan_statistics */count(1) from dba_free_space;
set linesize 200 pagesize 999
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));

可以看到原先的耗时比较多的16-24行，现在耗时已经大大减少了。

注意：在ORACLE 12C中，各个PDB和CDB之间回收站是私有的，所以对每个PDB回收站进行单独的清空。

#查看CDB中回收站对象数量
SQL> select count(1) from dba_recyclebin;
  COUNT(1)
----------
 123
 
#清空回收站
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from dba_recyclebin;
 COUNT(1)
----------
 0

#切换到PDB中再次查看
SQL> ALTER SESSION SET CONTAINER=ZHIXIN;
Session altered.
SQL> select count(1) from dba_recyclebin;
  COUNT(1)
----------
91
PDB中的回收站对象是没有清空的，需要单独清理回收。
#清空回收站
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from dba_recyclebin;
 COUNT(1)
----------
 0

每次都要手动清理比较麻烦，我们考虑定期去清理，创建一个job，当回收站记录达到500时，就开始清除7天前的记录。

–创建job调度过程实现自动化

create or replace procedure clear_recyclebin is
  recy_count number(9);
  time_drop  varchar2(2000);
  query_str  varchar2(1000);
  cursor c_drop_time is
    select owner,original_name
      from dba_recyclebin
     where droptime <
           to_char((sysdate - interval '7' day), 'yyyy-mm-dd hh24:mi:ss');
begin
  select count(1) into recy_count from dba_recyclebin;
  if recy_count >= 500 then
    for time_drop in c_drop_time loop
      query_str := 'purge table '||time_drop.owner||'.'||time_drop.original_name;
      execute immediate  query_str;
      dbms_output.put_line(query_str);
    end loop;
  end if;
end clear_recyclebin;

declare 
  v_count int:=0;
begin
   select count(*) into v_count from user_scheduler_jobs where job_name=upper('clear_recyclebin_job');
   if v_count>0 then
       dbms_scheduler.drop_job(upper('clear_recyclebin_job'));
   end if;   
   dbms_scheduler.create_job(
   job_name=>'clear_recyclebin_job', 
   job_type => 'STORED_PROCEDURE',
   job_action=>'CLEAR_RECYCLEBIN',          
   start_date => sysdate,          
   repeat_interval=>'FREQ=MINUTELY;INTERVAL=1', 
   enabled=>true);                             
end;

–创建定时job

declare 
  v_count int:=0;
begin
   select count(*) into v_count from user_scheduler_jobs where job_name=upper('clear_recyclebin_job');
   if v_count>0 then
       dbms_scheduler.drop_job(upper('clear_recyclebin_job'));
   end if;   
   dbms_scheduler.create_job(
   job_name=>'clear_recyclebin_job', 
   job_type => 'STORED_PROCEDURE',
   job_action=>'CLEAR_RECYCLEBIN',          
   start_date => sysdate,          
   repeat_interval=>'Freq=Daily;Interval=1', 
   enabled=>true);                             
end;