使用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');