dbms_advisor 手动生成段顾问建议！

执行包需要dbms_advisor权限：

sys@ORCL> grant advisor to u1;

授权成功。

创建段顾问任务，指定create_task的advisor_name参数为“段顾问”。查询dba_advisor_definitions来获得所有有效的顾问列表。

u1@ORCL> select * from dba_advisor_definitions;

ADVISOR_ID ADVISOR_NAME                     PROPERTY
---------- ------------------------------ ----------
         1 ADDM                                    1
         2 SQL Access Advisor                    271
         3 Undo Advisor                            1
         4 SQL Tuning Advisor                    935
         5 Segment Advisor                         3
         6 SQL Workload Manager                    0
         7 Tune MView                             31
         8 SQL Performance Analyzer              935
         9 SQL Repair Advisor                    679
        10 Compression Advisor                     3

已选择10行。

手动执行段顾问：

sys@ORCL> DECLARE
  2    my_task_id   number;
  3    obj_id       number;
  4    my_task_name varchar2(100);
  5    my_task_desc varchar2(500);
  6  BEGIN
  7    my_task_name := 'BIG_TABLE Advice';
  my_task_desc := 'Manual Segment Advisor Run';
  9  ---------
 10  -- Step 1 创建一个任务
 11  ---------
 12    dbms_advisor.create_task (
 13    advisor_name => 'Segment Advisor',
 14    task_id      => my_task_id,
 15    task_name    => my_task_name,
 16    task_desc    => my_task_desc);
 17  ---------
 18  -- Step 2 为这个任务分配一个对象
 19  ---------
 20    dbms_advisor.create_object (
 21    task_name   => my_task_name,
 22    object_type => 'TABLE',
 23    attr1       => 'U1',
 24    attr2       => 'BIG_TABLE',
 25    attr3       => NULL,
 26    attr4       => NULL,
 27    attr5       => NULL,
 28    object_id   => obj_id);
 29  ---------
 30  -- Step 3 设置任务参数
 31  ---------
 32    dbms_advisor.set_task_parameter(
 33    task_name => my_task_name,
 34    parameter => 'recommend_all',
  value     => 'TRUE');
---------
 37  -- Step 4 执行这个任务
 38  ---------
 39    dbms_advisor.execute_task(my_task_name);
 40  END;
 41  / 

PL/SQL 过程已成功完成。

sys@ORCL> SELECT
  2   'Segment Advice --------------------------'|| chr(10) ||
  3   'TABLESPACE_NAME  : ' || tablespace_name   || chr(10) ||
  4   'SEGMENT_OWNER    : ' || segment_owner     || chr(10) ||
  5   'SEGMENT_NAME     : ' || segment_name      || chr(10) ||
  6   'ALLOCATED_SPACE  : ' || allocated_space   || chr(10) ||
  7   'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10) ||
  8   'RECOMMENDATIONS  : ' || recommendations   || chr(10) ||
  9   'SOLUTION 1       : ' || c1                || chr(10) ||
 10   'SOLUTION 2       : ' || c2                || chr(10) ||
 11   'SOLUTION 3       : ' || c3 Advice
 12  FROM
 13  TABLE(dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'));

ADVICE
--------------------------------------------------------------------------------
Segment Advice --------------------------
TABLESPACE_NAME  : USERS
SEGMENT_OWNER    : QYV
SEGMENT_NAME     : BIG_TABLE
ALLOCATED_SPACE  : 125829120
RECLAIMABLE_SPACE: 81788928
RECOMMENDATIONS  : 压缩对象 QYV.BIG_TABLE, 估计可以节省 81788928 字节。
SOLUTION 1       : alter table "QYV"."BIG_TABLE"  compress for oltp
SOLUTION 2       : alter table "QYV"."BIG_TABLE"  move
SOLUTION 3       :

Segment Advice --------------------------
TABLESPACE_NAME  : USERS
SEGMENT_OWNER    : U1
SEGMENT_NAME     : BIG_TABLE
ALLOCATED_SPACE  : 125829120
RECLAIMABLE_SPACE: 109535475
RECOMMENDATIONS  : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 10953547
5 字节。
SOLUTION 1       : alter table "U1"."BIG_TABLE" shrink space
SOLUTION 2       : alter table "U1"."BIG_TABLE" shrink space COMPACT
SOLUTION 3       : alter table "U1"."BIG_TABLE" enable row movement

Segment Advice --------------------------
TABLESPACE_NAME  : USERS
SEGMENT_OWNER    : U1
SEGMENT_NAME     : TT
ALLOCATED_SPACE  : 27262976
RECLAIMABLE_SPACE: 17314513
RECOMMENDATIONS  : 启用表 U1.TT 的行移动并执行收缩, 估计可以节省 17314513 字节。

SOLUTION 1       : alter table "U1"."TT" shrink space
SOLUTION 2       : alter table "U1"."TT" shrink space COMPACT
SOLUTION 3       : alter table "U1"."TT" enable row movement


sys@ORCL> SELECT
  2   'Task Name        : ' || f.task_name  || chr(10) ||
  3   'Segment Name     : ' || o.attr2      || chr(10) ||
  4   'Segment Type     : ' || o.type       || chr(10) ||
  5   'Partition Name   : ' || o.attr3      || chr(10) ||
  6   'Message          : ' || f.message    || chr(10) ||
  7   'More Info        : ' || f.more_info TASK_ADVICE
  8  FROM dba_advisor_findings f
  9      ,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 like 'BIG_TABLE Advice'
 13  ORDER BY f.task_name;

TASK_ADVICE
-----------------------------------------------------------------------------------------------------------------------------------
Task Name        : BIG_TABLE Advice
Segment Name     : BIG_TABLE
Segment Type     : TABLE
Partition Name   :
Message          : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 109535475 字节。
More Info        : 分配空间:125829120: 已用空间:16293645: 可回收空间:109535475:

Task Name        : BIG_TABLE Advice
Segment Name     : BIG_TABLE
Segment Type     : TABLE
Partition Name   :
Message          : 启用表 U1.BIG_TABLE 的行移动并执行收缩, 估计可以节省 109535475 字节。
More Info        : 分配空间:125829120: 已用空间:16293645: 可回收空间:109535475:

删除一个任务：

u1@ORCL> exec dbms_advisor.delete_task('BIG_TABLE Advice');

PL/SQL 过程已成功完成。