手工生成段顾问建议

问题描述：你有一张表做了大量数据更新，你发现这张表的查询速度有所下降，你怀疑有行链接问题，因此想手工生成段顾问建议

解决方案：使用dbms_advisor可分析特定表空间的所有段，或者某个特定的对象(例如表或索引)
1、创建一个任务
2、为任务分配对象
3、设置任务参数
4、执行任务

执行dbms_advisor需要advisor权限
grant advisor to scott;

下面是一个例子：
declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
begin
  my_task_name := 'RNPAY_SIGNED_FLOW ADVISOR';
  my_task_desc := 'Manual Segment Advisor Run';
-----
--1、
-----
dbms_advisor.create_task(
advisor_name=>'Segment Advisor',
task_id=>my_task_id,
task_name=>my_task_name,
task_desc=>my_task_desc
);
-----
--2、
-----
dbms_advisor.create_object(
task_name=>my_task_name,
object_type=>'TABLE',
attr1=>'JYTPAYPRD',
attr2=>'RNPAY_SIGNED_FLOW',
attr3=>null,
attr4=>null,
attr5=>null,
object_id=>obj_id
);
-----
--3、
-----
dbms_advisor.set_task_parameter(
task_name=>my_task_name,
parameter=>'recommend_all',
value=>'TRUE'
);
-----
--4、
-----
dbms_advisor.execute_task(my_task_name);
end;
/

接下来可以调用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('TRUE','TRUE','FALSE'));

也可以查询数据字典获取建议
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'
   and t2.task_name='RNPAY_SIGNED_FLOW ADVISOR';
   
工作原理：
dbms_advisor包用来指导段顾问生成针对特定表的建议，包含几种操作的过程：
create_task：创建任务，参数advisor_name值为'Segment Advisor'，dba_advisor_definitions有所有可用的参数值
create_object：为段顾问确定目标对象，下表有可用的对象类型和参数
set_task_parameter：指定所需要返回的建议类型，下下表有所有有效的参数和值
execute_task：执行段顾问任务
delete_task：删除一个任务
cancel_task：撤销一个当前正在运行的任务

dbms_advisor.create_object有效的对象类型
对象类型          属性1    属性2     属性3    属性4
tablespace        名称     null         null       null
table                用户名   表名       null       null
index               用户名   索引名    null       null
表分区             用户名   表名      分区名   null
索引分区         用户名   索引名   分区名   null
表子分区         用户名   表名      子分区名 null
索引子分区     用户名   索引名   子分区名 null
lob                  用户名   段名        null        null
lob分区           用户名   段名      分区名    null    
lob子分区       用户名   段名      子分区名 null    

dbms_advisor.set_task_parameter的有效参数
time_limit：运行时间限制，单位秒，具体值为秒数或者unlimited
recommend_all：为所有类型的对象生成建议或者仅生成空间方面的建议，true生成所有对象，false生成空间方面