How to use segment advisor

1、查看segment advisor 方法一： select * fromtable(dbms_space.asa_recommendations(TRUE,FALSE,FALSE)); dbms_space.asa_recommentdations的三个参数： all_runs TURE返回历次运行的结果，FALSE则返回最近一次运行的结果 show_manual TURE返回手工执行se

1、查看segment advisor

方法一：

select * fromtable(dbms_space.asa_recommendations('TRUE','FALSE','FALSE'));

dbms_space.asa_recommentdations的三个参数：

all_runs TURE返回历次运行的结果，FALSE则返回最近一次运行的结果

show_manual TURE返回手工执行segment advisor的执行结果，FALSE返回自动执行的segment advisor的执行结果

show_finding TURE仅显示结果不显示建议，FALSE显示结果和建议

方法二：

select f.task_name,

execution_start,

o.attr2,

o.type,

o.attr3,

f.message,

f.more_info

from dba_advisor_executions e,

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_id = e.task_id

and e.advisor_name = 'Segment Advisor'

order by f.task_name;

segment advisor会自动的定期执行，查看其执行情况的视图是dba_auto_segadv_summary

查看ora advisor的视图

dba_advisor_executions

dba_advisor_findings

dba_advisor_objects

2、手工生成segment advisor 

查看advisor name

select * fromdba_advisor_definitions; 

ADVISOR_IDADVISOR_NAME PROPERTY

---------------------------------------- ----------

1 ADDM 1

2 SQL Access Advisor 271

3 Undo Advisor 1

4 SQL Tuning Advisor 935

5 Segment Advisor 67

6 SQL Workload Manager 0

7 Tune MView 31

8 SQL Performance Analyzer 935

9 SQL Repair Advisor 679

10 Compression Advisor 3

生成segmentadvisor

DECLARE

my_task_id number;

obj_id number;

my_task_name varchar2(100);

my_task_desc varchar2(500);

BEGIN

my_task_name := 'BIG_TABLE 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 => 'SYSTEM',

attr2 => 'AL_APPLY',

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;

/

说明：

Table 14-2 DBMS_ADVISOR packageprocedures relevant to the Segment Advisor

Table 14-3 Input forDBMS_ADVISOR.CREATE_OBJECT

Table 14-4 Input forDBMS_ADVISOR.SET_TASK_PARAMETER

Example:

variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';

dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);

dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);

dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');

dbms_advisor.execute_task(name);
end;
end;
/

查看segmentadvisor结果

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 : ' || f.task_name || chr(10) ||

'Segment Name : ' || o.attr2 || chr(10) ||

'Segment Type : ' || o.type || chr(10) ||

'Partition Name : ' || o.attr3 || chr(10) ||

'Message : ' || f.message || chr(10) ||

'More Info : ' || f.more_info TASK_ADVICE

FROMdba_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 like'BIG_TABLE Advice'

ORDER BYf.task_name;

删除segmentadvisor

execdbms_advisor.delete_task('BIG_TABLE Advice');

释放未使用的表的空间

alter tabletablename enable rowmovement;

alter tabletablename shrink spcace;

alter table tablename shrink spcace cascade;(级联收缩索引空间)

alter table tablename shrink spcace compact;(只整理碎片，不调整高水位线)--如果整理碎片和降低高水位话费时间会很长，用于分开操作