使用dbms_advisor模拟表碎片化及解决过程

    今天闲来无事，玩了玩dbms_advisor这个包来诊断表中是否存在碎片化。将操作过程记录如下，以备日后翻查。
1.删除记录前表的统计信息
SQL> select owner,segment_name,bytes/power(1024,2) mb from dba_segments
2  where wner='SCOTT' and segment_name='TEST';

OWNER     SEGMENT_NAME        MB
------- --------------- ------
SCOTT    TEST                  144

2.删除表中大部分记录模拟碎片化
SQL> select count(*) from test;

  COUNT(*)
----------                    
   1303264

SQL> delete test where rownum<=600000;  

已删除600000行。

3.执行分析脚本(脚本的第一个参数为对象的owner,第二个为对象的名称)
@analyze.sql SCOTT TEST
查询段相关建议
---------------------------------------------------------------------

CHECK_FRAG
TEST

TABLE
启用表 SCOTT.TEST 的行移动并执行收缩, 估计会节省 61223199 字节。

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

4.根据建议对表进行相关操作
SQL> alter table scott.test enable row movement;

表已更改。

SQL> alter table scott.test shrink space;

表已更改。

SQL> alter table scott.test disable row movement;

表已更改。

5.再次查看表的统计信息确认碎片整理效果
SQL> select owner,segment_name,bytes/power(1024,2) mb from dba_segments
 2  where wner='SCOTT' and segment_name='TEST'; 

OWNER     SEGMENT_NAME        MB
------- --------------- ------
SCOTT    TEST                70.1875

结论：根据建议估算可以节省58m，而最终结果可以节省70多m，与建议相差不大，不过总的来说还是达到了表碎片整理的效果。

analyze.sql脚本如下：
set linesize 1000 trimspool on verify off echo off feedback off heading off
--创建段建议任务
variable id number;
begin
  declare
  name varchar2(100);
  descr varchar2(500);
  obj_id number;
  begin
  name:='CHECK_FRAG';
  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            => '&1',
    attr2            => '&2',
    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;
/

--查询段相关建议
select '查询段相关建议' from dual;
select '---------------------------------------------------------------------' from dual;
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id
  and ao.task_name='CHECK_FRAG';
select '---------------------------------------------------------------------' from dual;

--删除任务
exec dbms_advisor.delete_task('CHECK_FRAG');

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20801486/viewspace-730979/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20801486/viewspace-730979/