点击(此处)折叠或打开
-
--os:centos 6.6
-
--db:oracle 11.2.0.4
-
-
--建测试表
-
create table scott.t_test01 as select * from dba_objects;
-
--插入数据
-
insert into scott.t_test01 select * from scott.t_test01;
-
commit;
-
insert into scott.t_test01 select * from scott.t_test01;
-
commit;
-
insert into scott.t_test01 select * from scott.t_test01;
-
commit;
-
--删除一些数据
-
delete from scott.t_test01 where rownum<300000;
-
commit;
-
-
--建立执行scott.T_TEST01的segment advisor task
-
declare
-
v_id number;
-
v_task_name varchar2(100);
-
v_object_owner varchar2(50):='SCOTT';
-
v_object_name varchar2(50):='T_TEST01';
-
v_object_type varchar2(50):='TABLE';
-
begin
-
dbms_advisor.create_task
-
(advisor_name => 'Segment Advisor'
-
,task_id => v_id
-
,task_name => v_task_name
-
,task_desc => v_object_type||' '||v_object_owner||'.'||v_object_name
-
,template => null
-
,is_template => 'FALSE');
-
dbms_output.put_line('task_id:'||v_id);
-
dbms_advisor.create_object
-
(task_name => v_task_name
-
,object_type => v_object_type
-
,attr1 => v_object_owner
-
,attr2 => v_object_name
-
,attr3 => null
-
,attr4 => null
-
,attr5 => null
-
,object_id => v_id);
-
dbms_advisor.set_task_parameter
-
(task_name => v_task_name
-
,parameter => 'RECOMMEND_ALL'
-
,value => 'TRUE');
-
dbms_advisor.set_task_parameter
-
(task_name => v_task_name
-
,parameter => 'MODE'
-
,value => 'COMPREHENSIVE');
-
dbms_advisor.set_task_parameter
-
(task_name => v_task_name
-
,parameter => 'TIME_LIMIT'
-
,value => 'UNLIMITED');
-
dbms_advisor.execute_task
-
(task_name => v_task_name);
-
end;
-
--输入任务id
-
/*task_id:21*/
-
-
--根据上一步输出的task_id查看segment advisor任务信息
-
SELECT at.task_id, at.task_name, at.description, count(*) object_count, at.status, at.status_message,
-
at.pct_completion_time, at.created, at.last_modified,
-
at.execution_start, at.execution_end, at.owner, at.how_created
-
FROM dba_advisor_tasks at, dba_advisor_objects ao
-
WHERE at.task_id = ao.task_id
-
AND at.task_id=21
-
AND at.owner = ao.owner
-
GROUP BY at.task_id, at.task_name, at.description, at.status, at.status_message,
-
at.pct_completion_time, at.created, at.last_modified,
-
at.execution_start, at.execution_end, at.owner, at.how_created;
-
-
--根据task_id查看segment advisor
-
SELECT af.owner task_owner, af.task_name, ao.type Object_Type,
-
ao.attr1 object_owner, ao.attr2 object_name,
-
ao.attr3 subobject_name, to_char(ao.attr4) Tablespace_Name,
-
af.message, af.more_info, to_char(ao.task_id) task_id, to_char(ao.object_id) object_id
-
FROM dba_advisor_findings af, dba_advisor_tasks at,
-
dba_advisor_objects ao
-
WHERE af.task_id = at.task_id
-
AND af.task_id=21
-
AND af.owner = at.owner
-
AND af.task_id = ao.task_id
-
AND ao.object_id = af.object_id;
-
-
--根据task_id查看segment advisor recommendations
-
SELECT aa.task_id, aa.object_id, aa.attr1, aa.attr2, aa.attr3,
-
initcap(ar.annotation_status) status, ar.rec_id
-
FROM dba_advisor_actions aa, dba_advisor_tasks at,
-
dba_advisor_recommendations ar
-
WHERE aa.task_id = at.task_id
-
AND aa.task_id=21
-
AND aa.owner = at.owner
-
AND ar.task_id = at.task_id
- AND ar.rec_id = aa.rec_id;