Oracle-查询表空间使用率很慢

近期发现，因数据库监控指标采集比较频繁，甚至触发等待。

解决方案如下：

1、调整采集周期。现行的10S采集频率太高。

2、优化表空间查询语句。

3、考虑回收站原因：  

 3.1：查询回收站大小：
SYS@evansdb2> SELECT count(*) FROM dba_recyclebin;
  COUNT(*)
----------
       127

SYS@evansdb2> SELECT value FROM v$parameter WHERE name = 'recyclebin';
VALUE
---------------
OFF

3.3：清空全库回收站
SYS@evansdb2> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.

3.4 再次查询回收站大小：
SYS@evansdb2> SELECT count(*) FROM dba_recyclebin;
  COUNT(*)
----------
         0

3.5 再次查询回收站大小：
SYS@evansdb2> SELECT count(*) FROM dba_recyclebin;
  COUNT(*)
----------
         0

3.6 收集统计信息：
SYS@evansdb2> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname =>  'RECYCLEBIN$', estimate_percent => 100, method_opt=> 'for all indexed columns',degree=>8);
PL/SQL procedure successfully completed.
3.7 重新查询表空间使用率
SYS@evansdb2> set linesize 200 pagesize 999  long 20000 
set timi on ti on
col name format a30   
col contents format a9   
col extent_management format a8   
col size_mb format a12   
col Used_MB format a12   
col free_mb format a12   
col "Used%" format a8   
col dt format a20   

SELECT d.tablespace_name Name, 
       d.status, 
       d.contents, 
       d.extent_management, 
       d.segment_space_management, 
       a.file_cnt, 
       TO_CHAR(NVL(a.bytes/1048576, 0), '999G999G999') SIZE_MB, 
       TO_CHAR(DECODE(d.contents,'UNDO',NVL(u.bytes, 0)/1048576,NVL(a.bytes - NVL(f.bytes, 0), 0)/1048576),'999G999G999') Used_MB, 
       TO_CHAR(DECODE(d.contents,'UNDO',NVL(a.bytes - NVL(u.bytes, 0), 0)/1048576,NVL(f.bytes, 0)/1048576), '999G999G999') FREE_MB, 
       TO_CHAR(DECODE(d.contents,'UNDO',NVL(u.bytes/a.bytes * 100, 0),NVL((a.bytes - NVL(f.bytes, 0))/a.bytes * 100, 0)),'990D00') "Used%", 
       TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') dt 
  FROM sys.dba_tablespaces d, 
       (SELECT tablespace_name, SUM(bytes) bytes, COUNT(file_id) file_cnt 
          from dba_data_files 
         GROUP BY tablespace_name) a, 
       (select tablespace_name, sum(bytes) bytes 
          from dba_free_space 
         group by tablespace_name) f, 
       (SELECT tablespace_name, SUM(bytes) bytes 
          FROM (SELECT tablespace_name, sum(bytes) bytes, status 
                  from dba_undo_extents 
                 WHERE status = 'ACTIVE' 
                 group by tablespace_name, status 
                UNION ALL 
                SELECT tablespace_name, sum(bytes) bytes, status 
                  from dba_undo_extents 
                 WHERE status = 'UNEXPIRED' 
                 group by tablespace_name, status) 
         group by tablespace_name) u 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = f.tablespace_name(+) 
   AND d.tablespace_name = u.tablespace_name(+) 
   AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 
UNION ALL 
SELECT d.tablespace_name, 
       d.status, 
       d.contents, 
       d.extent_management, 
       d.segment_space_management, 
       a.file_cnt, 
       TO_CHAR(NVL(a.bytes/1048576, 0), '999G999G999') SIZE_MB, 
       TO_CHAR(NVL(t.bytes, 0)/1048576, '999G999G999') Used_MB, 
       TO_CHAR((NVL(a.bytes, 0)/1048576 - NVL(t.bytes, 0)/1048576),'999G999G999') FREE_MB, 
       TO_CHAR(NVL(t.bytes/a.bytes * 100, 0), '990D00') "Used%", 
       TO_CHAR(sysdate, 'yyyy-mm-dd hh24:mi:ss') dt 
  FROM sys.dba_tablespaces d, 
       (select tablespace_name, sum(bytes) bytes, count(file_id) file_cnt 
          from dba_temp_files 
         group by tablespace_name) a, 
       (select ss.tablespace_name, 
               sum((ss.used_blocks * ts.blocksize)) bytes 
          from gv$sort_segment ss, sys.ts$ ts 
         where ss.tablespace_name = ts.name 
         group by ss.tablespace_name) t 
 WHERE d.tablespace_name = a.tablespace_name(+) 
   AND d.tablespace_name = t.tablespace_name(+) 
   AND d.extent_management = 'LOCAL' 
   AND d.contents = 'TEMPORARY' 
 ORDER BY "Used%";