Segment Advisor

三、段顾问的分析结果类型
    适合进行段收缩操作来回收空间的段
    具有大量行链接的段
    可能会从OLTP压缩中受益的段

四、手工运行段顾问步骤
    创建一个段顾问任务
    为这个任务分配一个对象（指定以表对象或者表空间级别来运行，同时制定用户名和表名或者表空间名）
    设置任务参数（执行段顾问执行时的一些参数，例如：运行时长限制参数、仅生产与空间相关建议/所有类型的对象生成建议）
    执行这个任务

五、手工运行段顾问的代码

declare
    my_task_id     number;
    obj_id         number;
    my_task_name   varchar2(100);
    my_task_desc   varchar2(500);
begin
    my_task_name :='advisor_test tab Advice';     --运行任务名，可以任意指定，不过建议为有意义的名称
    my_task_desc :='Manual Segment Advisor Run';  --运行任务描述，可以任意指定，不过建议为有意义的描述
 -----step 1
     /* 创建一个段顾问任务 */
     dbms_advisor.create_task(
     advisor_name => 'Segment Advisor',  --运行段顾问任务这个参数必须指定为Segment Advisor
     task_id => my_task_id,
     task_name => my_task_name,
     task_desc =>my_task_desc);
 -----step 2
  /* 为这个任务分配一个对象 */
     dbms_advisor.create_object(
     task_name=>my_task_name,
     object_type=>'TABLE',         -- 指定对象级别，如果为表对象则为'TABLE'，如果为表空间级别则为'TABLESPACE'
     attr1=>'DBMON',               ---如果在表对象级别运行，这个属性为用户名，表空间级别这个属性为表空间名字
     attr2 => 'ADVISOR_TEST',      ---如果在表对象级别运行，这个属性为表名，表空间级别这个属性为null
     attr3 => NULL,
     attr4=>null,
     attr5=>null,
     object_id=>obj_id);
 -----step 3
 /* 设置任务参数 */
     dbms_advisor.set_task_parameter(
     task_name => my_task_name,
 /* 设置段顾问运行参数"recommend_all"的值,为TRUE则为所有类型的对象的生成建议，为FALSE则仅生成与空间相关的建议 */
 /* 另一个滚问运行参数"time_limit",制定顾问运行的时间限制，默认值为无限制 */
      parameter=>'recommend_all',
     value=>'TRUE');
 -----step 4
 /* 执行这个任务 */
     dbms_advisor.execute_task(my_task_name);
 end;
 /

六、查询段顾问分析结果的语句

(1)
 windows平台：
    select
     /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
     'Task name    :'||f.task_name||chr(13)||chr(10)||
     'Segment name  :'||o.attr2    ||chr(13)||chr(10)||
     'Sement type  :'||o.type    ||chr(13)||chr(10)||
     'partition name:'||o.attr3    ||chr(13)||chr(10)||
     'Message      :'||f.message  ||chr(13)||chr(10)||
     'More info    :'||f.more_info TASK_ADVICE
     from dba_advisor_findings f,dba_advisor_objects o
    where o.task_id=f.task_id
     and o.object_id=f.object_id
     and f.task_name = 'advisor_test tab Advice'
    order by f.task_name;

 linux 平台：
      select
      /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
      'Task name    :'||f.task_name||chr(10)||
      'Segment name  :'||o.attr2    ||chr(10)||
      'Sement type  :'||o.type    ||chr(10)||
      'partition name:'||o.attr3    ||chr(10)||
      'Message      :'||f.message  ||chr(10)||
      'More info    :'||f.more_info TASK_ADVICE
      from dba_advisor_findings f,dba_advisor_objects o
     where o.task_id=f.task_id
      and o.object_id=f.object_id
      and f.task_name = 'advisor_test tab Advice'
     order by f.task_name;


 (2)
 通过查询TABLE(dbms_space.asa_recommendations(all_runs=>'TRUE',show_manual=>'TRUE',show_findings=>'FALSE'))来查看建议;
 第一个参数true表示运行历次运行结果，false表示最近一次的结果
 第二个参数true表示返回手工运行段顾问的结果，false表示返回自动运行段顾问的结果
 第三个参数true表示仅显示分析结果，false表示显示分析结果和分析建议
 备注：案例中使用（1）语句来查看分析结果


七、案例1（表对象级别运行）
 SQL> create table advisor_test as select * from dba_objects;
 SQL> insert into advisor_test select * from advisor_test;
 72525 rows inserted
 SQL> /
 145050 rows inserted
 SQL> /
 290100 rows inserted
 SQL> commit;
 Commit complete
 SQL> delete advisor_test where rownum<100000;
 99999 rows deleted
 SQL> /
 99999 rows deleted
 SQL> commit;
 Commit complete

declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
 begin
  my_task_name :='advisor_test tab Advice';
  my_task_desc :='Manual Segment Advisor Run';
 -----step 1
  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor',
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);
 -----step 2
  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLE',
  attr1=>'DBMON',
  attr2 => 'ADVISOR_TEST',
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);
 -----step 3
  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all',
  value=>'TRUE');
 -----step 4
  dbms_advisor.execute_task(my_task_name);
 end;
 /


ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_ADVISOR", line 201
ORA-06512: at line 31

 attr1=>'DBMON', 其中DBMON是用户名，测试中把换位当前用户SH



ERROR at line 1:
ORA-13607: The specified task or object advisor_test tab Advice already exists
ORA-06512: at "SYS.PRVT_ADVISOR", line 4827
ORA-06512: at "SYS.DBMS_ADVISOR", line 103
ORA-06512: at line 10

exec dbms_advisor.delete_task(task_name => 'advisor_test tab Advice');

*
ERROR at line 1:
ORA-13616: The current user SH has not been granted the ADVISOR privilege.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_ADVISOR", line 920
ORA-06512: at "SYS.DBMS_ADVISOR", line 100
ORA-06512: at line 10

grant ADVISOR to sh ;



已执行


SQL> select
       /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
     'Task name    :'||f.task_name||chr(13)||chr(10)||
     'Segment name  :'||o.attr2    ||chr(13)||chr(10)||
     'Sement type  :'||o.type    ||chr(13)||chr(10)||
     'partition name:'||o.attr3    ||chr(13)||chr(10)||
     'Message      :'||f.message  ||chr(13)||chr(10)||
     'More info    :'||f.more_info TASK_ADVICE
     from dba_advisor_findings f,dba_advisor_objects o
      where o.task_id=f.task_id
        and o.object_id=f.object_id
        and f.task_name = 'advisor_test tab Advice'
      order by f.task_name;

TASK_ADVICE
 --------------------------------------------------------------------------------
 Task name    :advisor_test tab Advice
 Segment name  :ADVISOR_TEST
 Sement type  :TABLE
 partition name:
 Message      :启用表 DBMON.ADVISOR_TEST 的行移动并执行收缩, 估计可以节省 28543531 字节。
 More info    :分配空间:75497472: 已用空间:46953941: 可回收空间:28543531:



dbms_advisor.create_task: 指定类型,返回唯一的作业ID并自动生成运行的程序名.
dbms_advisor.create_object: 指定分析对象信息.
dbms_advisor.set_task_parameter: 这里指定获取所有关于表的建议.
dbms_advisor.execute_task: 执行
以下为获取表建议信息:
   col MESSAGE for a50
   col more_info for a50
   col BENEFIT_TYPE for a50
   col ATTR1 for a50
   col COMMAND for a20
   set linesize 200
   select  ID from  WRI$_ADV_TASKS where  CTIME > sysdate - 1/24  and ADVISOR_NAME='Segment Advisor' ;
   select owner,task_id,task_name,type,MESSAGE,more_info     from dba_advisor_findings         where task_id=1127;
   select owner,task_id,task_name,benefit_type                from dba_advisor_recommendations  where task_id=1127;
   select owner,task_id,task_name,command,attr1               from dba_advisor_actions          where task_id=1127;
   

八、案例2（表空间级别运行）

declare
  my_task_id number;
  obj_id number;
  my_task_name varchar2(100);
  my_task_desc varchar2(500);
 begin
  my_task_name :='Tablespace Advice';
  my_task_desc :='Manual Segment Advisor Run';
 -----step 1
  dbms_advisor.create_task(
  advisor_name => 'Segment Advisor',
  task_id => my_task_id,
  task_name => my_task_name,
  task_desc =>my_task_desc);
 -----step 2
  dbms_advisor.create_object(
  task_name=>my_task_name,
  object_type=>'TABLESPACE',
  attr1=>'USERS',
  attr2 => null,
  attr3 => NULL,
  attr4=>null,
  attr5=>null,
  object_id=>obj_id);
 -----step 3
  dbms_advisor.set_task_parameter(
  task_name => my_task_name,
  parameter=>'recommend_all',
  value=>'TRUE');
 -----step 4
  dbms_advisor.execute_task(my_task_name);
 end;
 /



SQL> select
  2    /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
  3  'Task name    :'||f.task_name||chr(13)||chr(10)||
  4  'Segment name  :'||o.attr2    ||chr(13)||chr(10)||
  5  'Sement type  :'||o.type    ||chr(13)||chr(10)||
  6  'partition name:'||o.attr3    ||chr(13)||chr(10)||
  7  'Message      :'||f.message  ||chr(13)||chr(10)||
  8  'More info    :'||f.more_info TASK_ADVICE
  9  from dba_advisor_findings f,dba_advisor_objects o
  10  where o.task_id=f.task_id
  11    and o.object_id=f.object_id
  12    and f.task_name = 'Tablespace Advice'
  13  order by f.task_name;

TASK_ADVICE
 --------------------------------------------------------------------------------
 Task name    :Tablespace Advice
 Segment name  :T_SCEGAOKAOQUERY_GZ
 Sement type  :TABLE
 partition name:
 Message      :此对象中的空闲空间小于 10MB。
 More info    :分配空间:65536: 已用空间:8192: 可回收空间:57344:

Task name    :Tablespace Advice
 Segment name  :T_SCEGAOKAOQUERY_GZ
 Sement type  :TABLE
 partition name:

TASK_ADVICE
 --------------------------------------------------------------------------------
 Message      :此对象中的空闲空间小于 10MB。
 More info    :分配空间:3145728: 已用空间:2871921: 可回收空间:273807:

Task name    :Tablespace Advice
 Segment name  :T_SCEGAOKAOQUERY
 Sement type  :TABLE
 partition name:
 Message      :此对象中的空闲空间小于 10MB。
 More info    :分配空间:46137344: 已用空间:44837534: 可回收空间:1299810:
 --------为了排版,省略后面的输出--------------

备注:最后可以运行delete_task来删除任务,如下exec dbms_advisor.delete_task(task_name => 'Tablespace Advice');


Oracle内置空间管理工具-Segment Advisor
数据表上频繁的进行插入、更新和删除动作会产生表空间碎片。Oracle可在表或索引上执行Segment shrink。使得segment的空闲空间可用于表空间中的其它segment，可改善DML性能。
调用Segment Advisor对指定segment执行增长趋势分析以确定哪些Segment受益于Segment shrink。
执行shrink操作，Segment Advisor推荐启用表的ROW MOVEMENT
SQL> alter table wind enable row movement;
Table altered
配置Segment Advisor对表进行分析，使用如下PL/SQL块：
SQL> variable task_id number;
SQL> declare
      name varchar2(100);
      descr varchar2(500);
      obj_id number;
      begin
      name := '';
      descr := 'Check WIND.ShrinkTable Table';
      dbms_advisor.create_task('Segment Advisor',:task_id,name,descr,NULL);
      dbms_advisor.create_object(name,'TABLE','WIND','SHRINKTABLE',NULL,NULL,obj_id);
     dbms_advisor.set_task_parameter(name,'RECOMMEND_ALL','TRUE');
     dbms_advisor.execute_task(name);
     end;
     /
PL/SQL procedure successfully completed
task_id
---------
357
dbms_advisor.create_task: 指定类型,返回唯一的作业ID并自动生成运行的程序名.
dbms_advisor.create_object: 指定分析对象信息.
dbms_advisor.set_task_parameter: 这里指定获取所有关于表的建议.
dbms_advisor.execute_task: 执行
以下为获取表建议信息:
SQL> select owner,task_id,task_name,type,messagel,more_info     from dba_advisor_findings     where task_id=357;
OWNER   TASK_ID TASK_NAME  TYPE        MESSAGE                                MORE_INFO
------- ------- ---------- ----------- -------------------------------------- -------------------------------------------------------------
WIND    357     TASK_357   INFORMATION 进行压缩, 估计可以省出 10655319 字节。
分配空间:226492416: 已用空间:215837097: 可回收空间:10655319:
SQL> select owner,task_id,task_name,benefit_type      from dba_advisor_recommendations     where task_id=357;
OWNER TASK_ID  TASK_NAME  BENEFIT_TYPE
---------- ----------- --------------- --------------------------------------
WIND     357         TASK_357    进行压缩, 估计可以省出 10655319 字节。
SQL> select owner,task_id,task_name,command,attr1      from dba_advisor_actions     where task_id=357;
OWNER TASK_ID TASK_NAME  COMMAND         ATTR1
---------- ---------- ---------------- ------------------- ---------------------------------------
WIND     357       TASK_357     SHRINK SPACE  alter table "WIND"."THRINKTABLE" shrink space
SQL> alter table wind.thrinktable shrink space;
注意两个限制:
一. 没法在基于Oracle 10g上的LOB Segment上工作.
二. 不允许在包含任何函数索引规则的表进行操作,不管是使用10g或11g.



select owner,task_id,task_name,command,attr1      from dba_advisor_actions    where owner='LMIS';
   where task_id=357;




select  task_id from  WRI$_ADV_TASKS where  CTIME > sysdate - 1/24  and ADVISOR_NAME='Segment Advisor' ;
select owner,task_id,task_name,type,messagel,more_info     from dba_advisor_findings         where task_id=1127;
select owner,task_id,task_name,benefit_type                from dba_advisor_recommendations  where task_id=1127;
select owner,task_id,task_name,command,attr1               from dba_advisor_actions          where task_id=1127;