数据库性能优化之段顾问

问题描述：查询访问一张表时，速度较慢，统计发现表中数据也不是很多，这时可以通过检查段顾问（Segment Advisor)的输出，看看是否有与存储空间相关的建议；

 

   select
  'Segment Advice----------------------'|| chr(10)||
  'Tablesapce_name :'||tablespace_name  || chr(10)||
  'Segment_owner   :'||segment_owner    || chr(10)||
  'Segment_name    :'||segment_name     || chr(10)||
  'Allocated_space :'||allocated_space  || chr(10)||
  'Reclaimable_spac:'||reclaimable_space|| chr(10)||
  'Recommendations :'||recommendations  || chr(10)||
  'Solution1       :'||c1               || chr(10)||
  'Solution2       :'||c2               || chr(10)||
  'Solution3       :'||c3 Advice     
  from
  table(dbms_space.asa_recommendations('FALSE','FALSE','FALSE'));

 

输出结果如：

Segment Advice----------------------
Tablesapce_name :USERS

ADVICE
--------------------------------------------------------------------------------
Segment_owner   :SKYECHO
Segment_name    :AIR_MESSAGE_M_IP_IDX
Allocated_space :1080033280
Reclaimable_spac:129610672
Recommendations :ѹ 129610672 Solution1  :alter index "SKYECHO"."AIR_MESSAGE_M_IP_IDX" shrink space
Solution2       :alter index "SKYECHO"."AIR_MESSAGE_M_IP_IDX" shrink space COMPA
CT
Solution3       :

 

可以看到AIR_MESSAGE_M_IP_IDX段可回收的空间为129610672KB，按推荐的方法：

SQL> select count(*) from dba_extents where segment_name='AIR_MESSAGE_M_IP_IDX';

  COUNT(*)
----------
       185

SQL> alter index SKYECHO.AIR_MESSAGE_M_IP_IDX shrink space;