oracle查询表空间大小很缓慢

oracle 11gR2在查询表空间的sql很缓慢

sql语句

 SELECT A.TABLESPACE_NAME "表空间名称",
       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) || '%' "已使用(%)",
       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) || '%' "剩余(%)",
       ROUND(A.BYTES_ALLOC / 1024 / 1024 / 1024, 2) "总容量(G)",
       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024 / 1024, 2) "已使用(G)",
       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024 / 1024, 2) "剩余(G)"
  FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_ALLOC
          FROM DBA_DATA_FILES F
         GROUP BY TABLESPACE_NAME) A,
       (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE
          FROM DBA_FREE_SPACE F
         GROUP BY TABLESPACE_NAME) B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
 ORDER BY ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2); 

执行计划

查看x$KTFBUE的统计信息

SQL>  select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where 
table_name='X$KEWRATTRNEW';
 
OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
SYS                            X$KEWRATTRNEW
 
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
 
PL/SQL procedure successfully completed.
 
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where 
table_name='X$KEWRATTRNEW';
 
OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
SYS                            X$KEWRATTRNEW
 
SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');
 
PL/SQL procedure successfully completed.
 
SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where 
table_name='X$KEWRATTRNEW';
 
OWNER                          TABLE_NAME                     LAST_ANAL
------------------------------ ------------------------------ ---------
SYS                            X$KEWRATTRNEW                  08-AUG-11
 
SQL>

根据上面应该是统计信息不准

查看官方文档的解释

Why do some fixed tables not have statistics even after running: DBMS_STATS.GATHER_FIXED_OBJECTS_STATS?


Some fixed tables are left without stats because the development team has flagged the code to avoid statistics gathering for some fixed tables. This is because they know they will perform better without stats or because it is not supported to gather the statistics upon them.


This is expected behavior which was confirmed in : Bug 12844116 fixed tables statistics.


If you encounter performance issues specifically because statistics are missing on certain of these fixed tables, please file a SR with Database Support, quoting this article as reference.

开发人员觉得不收集统计信息性能会更好

这里有两种解决方案

1：使用11gR2新特性card feedback特性（该特性用于根据最新的统计信息生成执行计划，改执行计划以cursor的方式存在，所以一旦被age out共享池，cbo就会选用以前旧的执行计划，PS：生产库一般禁用此特性）

2：推荐：收集x$KTFBUE的统计信息

 exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'X$KTFBUE'); 

 

该问题采用第二种解决方案,得到了解决，response time从20mins下降到<1s。