Howtousesegmentadvisor

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(&＃39;TRUE&＃39;,&＃39;FALSE&＃39;,&＃39;FALSE&＃39;));

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 = &＃39;Segment Advisor&＃39;

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 := &＃39;BIG_TABLE Advice&＃39;;

my_task_desc := &＃39;Manual Segment Advisor Run&＃39;;

-- Step 1创建一个任务

dbms_advisor.create_task(advisor_name =>&＃39;Segment Advisor&＃39;,

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 =>&＃39;TABLE&＃39;,

attr1 => &＃39;SYSTEM&＃39;,

attr2 => &＃39;AL_APPLY&＃39;,

attr3 => NULL,

attr4 => NULL,

attr5 => NULL,

object_id => obj_id);

-- Step 3设置任务参数

dbms_advisor.set_task_parameter(task_name=> my_task_name,

parameter=> &＃39;recommend_all&＃39;,

value => &＃39;TRUE&＃39;);

-- 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:=&＃39;Manual_Employees&＃39;;
descr:=&＃39;Segment Advisor Example&＃39;;

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

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

dbms_advisor.set_task_parameter(
task_name => name,
parameter => &＃39;recommend_all&＃39;,
value => &＃39;TRUE&＃39;);

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

查看segmentadvisor结果

SELECT

&＃39;Segment Advice --------------------------&＃39;||chr(10) ||

&＃39;TABLESPACE_NAME : &＃39; || tablespace_name || chr(10) ||

&＃39;SEGMENT_OWNER : &＃39; || segment_owner || chr(10) ||

&＃39;SEGMENT_NAME : &＃39; || segment_name || chr(10) ||

&＃39;ALLOCATED_SPACE : &＃39; || allocated_space || chr(10) ||

&＃39;RECLAIMABLE_SPACE: &＃39; || reclaimable_space ||chr(10) ||

&＃39;RECOMMENDATIONS : &＃39; || recommendations || chr(10) ||

&＃39;SOLUTION 1 : &＃39; || c1 || chr(10) ||

&＃39;SOLUTION 2 : &＃39; || c2 || chr(10) ||

&＃39;SOLUTION 3 : &＃39; || c3 Advice

FROM

TABLE(dbms_space.asa_recommendations(&＃39;TRUE&＃39;,&＃39;TRUE&＃39;, &＃39;FALSE&＃39;));

或者：

SELECT

&＃39;Task Name : &＃39; || f.task_name || chr(10) ||

&＃39;Segment Name : &＃39; || o.attr2 || chr(10) ||

&＃39;Segment Type : &＃39; || o.type || chr(10) ||

&＃39;Partition Name : &＃39; || o.attr3 || chr(10) ||

&＃39;Message : &＃39; || f.message || chr(10) ||

&＃39;More Info : &＃39; || 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&＃39;BIG_TABLE Advice&＃39;

ORDER BYf.task_name;

删除segmentadvisor

execdbms_advisor.delete_task(&＃39;BIG_TABLE Advice&＃39;);

释放未使用的表的空间

alter tabletablename enable rowmovement;

alter tabletablename shrink spcace;

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

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