显示自动段顾问建议

问题描述：你有一个查询耗费时间很长，但是表中数据很少，你想查看段顾问有什么建议

解决方案：
使用段顾问来显示表的信息，曾经分配过但现在是空的空间(删除大量数据行)，有大量未使用的空间的表会使全表扫描变得很慢，因为全表扫描会扫描高水位线下的所有块
dbms_space包可以查看段顾问的建议。这些信息由段顾问生成，这些段适合收缩，移动或压缩，如下查询：
select
 'segment advice--------------------'    ||chr(10)||
 'TABLESPACE_NAME  :'||tablespace_name   ||chr(10)||
 'SEGMENT_OWNER    :'||segment_owner     ||chr(10)||
 'SEGMENT_NAME     :'||segment_name      ||chr(10)||
 'ALLOCATED_SPACE  :'||allocated_space   ||chr(10)||
 'RECLAIMABLE_SPACE:'||reclaimable_space ||chr(10)||
 'RECOMMENDATIONS  :'||RECOMMENDATIONS   ||chr(10)||
 'solution 1       :'||c1                ||chr(10)||
 'solution 2       :'||c2                ||chr(10)||
 'solution 3       :'||c3 advice
from table(dbms_space.asa_recommendations('FALSE','FALSE','FALSE'));

ADVICE
--------------------------------------------
segment advice--------------------
TABLESPACE_NAME  :ACCT_TBS01
SEGMENT_OWNER :JYTLOCAL
SEGMENT_NAME :SC_IDENTIFY_CHECK_MSG
ALLOCATED_SPACE  :1567621120
RECLAIMABLE_SPACE:12969320
RECOMMENDATIONS  :Enable row movement of the table JYTLOCAL.SC_IDENTIFY_CHECK_MSG and perform shrink, estimated savings is 12969320 bytes.
solution 1 :alter table "JYTLOCAL"."SC_IDENTIFY_CHECK_MSG" shrink space
solution 2 :alter table "JYTLOCAL"."SC_IDENTIFY_CHECK_MSG" shrink space COMPACT
solution 3 :alter table "JYTLOCAL"."SC_IDENTIFY_CHECK_MSG" enable row movement

工作原理：
自10gr2之后，oracle会自动运行段顾问作业，分析数据库中的段，并将结果保存到内部表中，结果包括
1、适合收缩的段
2、有显著行链接的段
3、可以从oltp压缩中获益的段

段顾问通过自动调度的dbms_scheduler定期运行，可查看dba_auto_segadv_summary视图查看运行时间
select segments_processed,end_time from dba_auto_segadv_summary order by end_time;

段顾问以awr作为信息源，运行结果保存在内部表中，可通过以下数据字典查看：
dba_advisor_executions
dba_advisor_findings
dba_advisor_objects

可通过三种工具获取输出：
1、运行dbms_space.asa_recommendations
2、手工查询dba_advisor_*视图
3、通过em界面查看

dbms_space.asa_recommendations参数含义：
all_runs:true则返回历次运行结果，false返回上次结果
show_manual:true返回手工运行结果，false返回自动运行的结果
show_finding:仅显示结果不显示建议

手工查询段顾问建议：
select
 'TASK_NAME      :'||T1.TASK_NAME ||CHR(10)||
 'START_RUN_TIME :'||TO_CHAR(T1.EXECUTION_START,'YYYY-MM-DD HH24:MI:SS') ||CHR(10)||
 'SEGMENT_OWNER  :'||T3.ATTR1 ||CHR(10)||
 'SEGMENT_NAME   :'||T3.ATTR2 ||CHR(10)||
 'PARTITION_NAME :'||T3.ATTR3 ||CHR(10)||
 'SEGMENT_TYPE   :'||T3.TYPE  ||CHR(10)||
 'MESSAGE        :'||T2.MESSAGE||CHR(10)||
 'MORE INFO      :'||T2.MORE_INFO||CHR(10)||
 '---------------------------------' ADVICE
  from dba_advisor_executions t1,
       dba_advisor_findings   t2,
       dba_advisor_objects    t3
 where t1.task_id = t2.task_id
   and t2.task_id = t3.task_id
   and t2.object_id=t3.object_id
   and t1.advisor_name = 'Segment Advisor'
   and t1.execution_last_modified > sysdate - 1
   and t2.type = 'PROBLEM';
   
ADVICE
------------------------------------------------------------------
TASK_NAME      :SYS_AUTO_SPCADV_07002208092016
START_RUN_TIME :2016-09-08 22:00:08
SEGMENT_OWNER  :JYTPAYDEV
SEGMENT_NAME   :CP_ORDER_FLOW
PARTITION_NAME :
SEGMENT_TYPE   :TABLE
MESSAGE        :The object has chained rows that can be removed by re-org.
MORE INFO      :17 percent chained rows can be removed by re-org.
---------------------------------

上面的显示表明某张表有行链接

通过em查看建议，先进入顾问中心(Advisor Central)，然后定位到段顾问页面，进入段建议页面。