segment advisor可以帮助分析表或索引的碎片,行链接问题.通过对问题表或索引收缩,移动或重建后减少存储空间提高查询速度.
点击(此处)折叠或打开
- --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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28539951/viewspace-2113728/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28539951/viewspace-2113728/