oracle查询表空间大小很缓慢

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

sql语句

[html]  view plain  copy

1. SELECT A.TABLESPACE_NAME "表空间名称",  
2.       100 - ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) || '%' "已使用(%)",  
3.       ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2) || '%' "剩余(%)",  
4.       ROUND(A.BYTES_ALLOC / 1024 / 1024 / 1024, 2) "总容量(G)",  
5.       ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024 / 1024, 2) "已使用(G)",  
6.       ROUND(NVL(B.BYTES_FREE, 0) / 1024 / 1024 / 1024, 2) "剩余(G)"  
7.  FROM (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_ALLOC  
8.          FROM DBA_DATA_FILES F  
9.         GROUP BY TABLESPACE_NAME) A,  
10.       (SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE  
11.          FROM DBA_FREE_SPACE F  
12.         GROUP BY TABLESPACE_NAME) B  
13. WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)  
14. ORDER BY ROUND((NVL(B.BYTES_FREE, 0) / A.BYTES_ALLOC) * 100, 2);   

执行计划

查看x$KTFBUE的统计信息

[html]  view plain  copy

1. SQL>  select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where   
2. table_name='X$KEWRATTRNEW';  
3.    
4. OWNER                          TABLE_NAME                     LAST_ANAL  
5. ------------------------------ ------------------------------ ---------  
6. SYS                            X$KEWRATTRNEW  
7.    
8. SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;  
9.    
10. PL/SQL procedure successfully completed.  
11.    
12. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where   
13. table_name='X$KEWRATTRNEW';  
14.    
15. OWNER                          TABLE_NAME                     LAST_ANAL  
16. ------------------------------ ------------------------------ ---------  
17. SYS                            X$KEWRATTRNEW  
18.    
19. SQL> exec dbms_stats.gather_table_stats('SYS', 'X$KEWRATTRNEW');  
20.    
21. PL/SQL procedure successfully completed.  
22.    
23. SQL> select OWNER, TABLE_NAME, LAST_ANALYZED from dba_tab_statistics where   
24. table_name='X$KEWRATTRNEW';  
25.    
26. OWNER                          TABLE_NAME                     LAST_ANAL  
27. ------------------------------ ------------------------------ ---------  
28. SYS                            X$KEWRATTRNEW                  08-AUG-11  
29.    
30. 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。

=================================================

3.解决办法
3.1清空回收站所有对象
SQL> purge dba_recyclebin;


3.2 重新收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname =>  'RECYCLEBIN$ ', estimate_percent => 100, method_opt=> 'for all indexed columns',degree=>8);