【读书笔记】手动生成段顾问建议

DECLARE

my_task_id number;

obj_id number;

my_task_name varchar2(100);

my_task_desc varchar2(500);

BEGIN

my_task_name := 'Table_Segment_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 => 'user_name',

attr2 => 'table_name',

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;

查看段顾问建议：

select b.advisor_name,c.attr1,c.attr2,a.task_name,a.message,a.more_info

from DBA_ADVISOR_FINDINGS a,DBA_ADVISOR_TASKS b,dba_advisor_objects c

where a.task_id=b.task_id

and b.advisor_name='Segment Advisor'

and b.task_name='Table_Segment_Advice'

and a.object_id=c.object_id

and a.task_id=c.task_id

或

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', 'FALSE', 'FALSE'));