oracle查询数据文件的剩余空间或者查询表空间使用率慢的原因及解决方案

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

我们在查询数据文件的剩余空间或者查询表空间使用率时，可能会碰见查询很慢的情况，主要原因是在于查询表空间使用情况的时候，需要从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;

关注我，学习更多的数据库知识！