问题描述：queries on dba_free_space are slow

问题描述：queries on dba_free_space are slow
数据库版本：11.2.0.4
通过采样一个时间段AWR报表，发觉了基本都是IO问题，而在top sql elapsed time等基本都是一个监控表空间SQL语句，换句话说都是查询某些fixed table 造成的IO资源瓶颈。
如下top sql elapsed time，排名前3的SQL语句：


语句1：
SELECT a.tablespace_name "Tablespace",
       b.status "Status",
       b.contents "Type",
       b.extent_management "Extent Mgmt",
       a.bytes bytes,
       a.maxbytes bytes_max,
       c.bytes_free + NVL(d.bytes_expired, :"SYS_B_0") bytes_free
  FROM ( -- belegter und maximal verfuegbarer platz pro datafile -- nach tablespacenamen zusammengefasst -- => bytes -- => maxbytes SELECT a.tablespace_name, SUM(a.bytes) bytes, SUM(DECODE(a.autoextensible, :"SYS_B_1", a.maxbytes, :"SYS_B_2", a.bytes)) maxbytes FROM dba_data_files a GROUP BY tablespace_name ) a, sys.dba_tablespaces b, ( -- freier platz pro tablespace -- => bytes_free SELECT a.tablespace_name, SUM(a.bytes) bytes_free FROM dba_free_space a GROUP BY tablespace_name ) c, ( -- freier platz durch expired extents -- speziell fuer undo tablespaces -- => bytes_expired SELECT a.tablespace_name, SUM(a.bytes) bytes_expired FROM dba_undo_extents a WHERE status = :"SYS_B_3 " GROUP BY tablespace_name ) d WHERE a.tablespace_name = c.tablespace_name (+) AND a.tablespace_name = b.tablespace_name AND a.tablespace_name = d.tablespace_name (+) UNION ALL SELECT d.tablespace_name "Tablespace", b.status "Status", b.contents "Type", b.extent_management "Extent Mgmt", sum(a.bytes_free + a.bytes_used) bytes, -- allocated SUM(DECODE(d.autoextensible, :"SYS_B_4", d.maxbytes, :"SYS_B_5", d.bytes)) bytes_max, SUM(a.bytes_free + a.bytes_used - NVL(c.bytes_used, :"SYS_B_6")) bytes_free FROM sys.v_$TEMP_SPACE_HEADER a, sys.dba_tablespaces b, sys.v_$Temp_extent_pool c, dba_temp_files d WHERE c.file_id(+) = a.file_id and c.tablespace_name(+) = a.tablespace_name and d.file_id = a.file_id and d.tablespace_name = a.tablespace_name and b.tablespace_name = a.tablespace_name GROUP BY b.status, b.contents, b.extent_management, d.tablespace_name ORDER BY :"SYS_B_7"






语句2：
SELECT t.tablespace_name,
       t.contents,
       t.status,
       NVL(df.allocated_bytes, :"SYS_B_0") -
       NVL((NVL(f.free_bytes, :"SYS_B_1") + df.max_free_bytes), :"SYS_B_2") usedBytes,
       NVL((NVL(f.free_bytes, :"SYS_B_3") + df.max_free_bytes), :"SYS_B_4") freeBytes,
       NVL(f.free_blocks, :"SYS_B_5") freeBlocks
  FROM sys.dba_tablespaces t,
       (select ff.tablespace_name,
               sum(ff.free_bytes) free_bytes,
               sum(ff.free_blocks) free_blocks
          from (SELECT fs.tablespace_name,
                       SUM(fs.bytes) free_bytes,
                       SUM(fs.blocks) free_blocks
                  FROM sys.dba_free_space fs, sys.dba_data_files dfs
                 where fs.file_id = dfs.file_id
                 GROUP BY fs.tablespace_name, dfs.autoextensible) ff
         group by tablespace_name) f,
       (select dff.tablespace_name,
               sum(dff.allocated_bytes) allocated_bytes,
               sum(dff.max_free_bytes) max_free_bytes
          from (select tablespace_name,
                       autoextensible,
                       sum(decode(sign(maxbytes - bytes),
                                  :"SYS_B_6",
                                  maxbytes,
                                  bytes)) allocated_bytes,
                       sum(decode(sign(maxbytes - bytes),
                                  :"SY S_B_7",
                                  abs(maxbytes - bytes),
                                  :"SYS_B_8")) max_free_bytes
                  from dba_data_files
                 group by tablespace_name, autoextensible) dff
         group by tablespace_name) df
 WHERE t.tablespace_name = f.tablespace_name(+)
   and t.tablespace_name = df.tablespace_name(+)
 order by tablespace_name




语句3：
SELECT sum(bytes) free_bytes FROM dba_free_space


原因分析：
 回收站存有2500多条，分区表的drop数据，大量drop后导致高水位未能下降，性能急剧下降。


解决办法：
 
 >进行表统计分析gathering statisics on x$KTFBUE using:
  exec dbms_stats.gather_fixed_objects_stats();
 
 >清空回收站
  purge dba_recyclebin;
  

优化前执行需要5min左右，优化后执行需要0.6s。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17172228/viewspace-2145276/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17172228/viewspace-2145276/