执行包需要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 过程已成功完成。