Segment Advisor

Segment Advisor可用于监控某个对象的碎片程度，并对其进行回收，从而提高性能，注意以下数字的变化，以下是我做的一个测试脚本，供参考！

SQL>
SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                          
---------------          45                                                                                                                                                                                                                                                                                                                                                                                                             
SQL> select count(*) from b;

  COUNT(*)                                                                                                                                                                 
----------                                                                                                                                                         
    405696

SQL> delete from b where rownum<100001;

已删除100000行。

SQL> commit;

提交完成。

SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
---------------     45                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

SQL> create index ind_b on b(object_id);

索引已创建。

SQL> delete from b where rownum<100001;

已删除100000行。

SQL> commit;

提交完成。

SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                                                                                                                                                                                                                                                                       
             45

SQL> variable id number;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

SQL> begin
  2       declare
  3         name   varchar2(100);
  4         descr  varchar2(500);
  5         obj_id number;
  6       begin
  7         name  := 'advisor_'||to_char(sysdate,'yyyymmdd');
  8         descr := 'Segment Advisor Example';
  9         dbms_advisor.create_task(advisor_name => 'Segment Advisor',
 10                                  task_id      => :id,
 11                                 task_name    => name,
 12                                 task_desc    => descr);
 13        dbms_advisor.create_object(task_name   => name,
 14                                  object_type => 'TABLE',
 15                                  attr1       => 'ZAKI',
 16                                  attr2       => 'B',
 17                                  attr3       => NULL,
 18                                  attr4       => NULL,
 19       attr5       => NULL,
 20                                  object_id   => obj_id);
 21         dbms_advisor.set_task_parameter(task_name => name,
 22                                         parameter => 'recommend_all',
 23                                         value     => 'TRUE');
 24        dbms_advisor.execute_task(name);
 25       end;
 26  end;
 27  /

PL/SQL 过程已成功完成。

SQL> select af.task_name,
  2             ao.attr2 segname,
  3             ao.attr3 partition,
  4             ao.type,
  5             af.message
  6  from dba_advisor_findings af, dba_advisor_objects ao
  7  where ao.task_id = af.task_id
  8    and ao.object_id = af.object_id
  9    and ao.owner = 'ZAKI';

TASK_NAME                      SEGNAME        MESSAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
advisor_t_test_resumable       A                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010             

 B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。

SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                             
---------------
             45
SQL> select count(*) from b;

  COUNT(*)                                                                                                                                                                    
----------
   205696                                                                                                             

SQL> begin
  2       declare
  3         name   varchar2(100);
  4         descr  varchar2(500);
  5         obj_id number;
  6       begin
  7         name  := 'advisor_A_'||to_char(sysdate,'yyyymmdd');
  8         descr := 'Segment Advisor Example';
  9         dbms_advisor.create_task(advisor_name => 'Segment Advisor',
 10                                  task_id      => :id,
 11                                 task_name    => name,
 12                                 task_desc    => descr);
 13        dbms_advisor.create_object(task_name   => name,
 14                                  object_type => 'TABLE',
 15                                  attr1       => 'ZAKI',
 16                                  attr2       => 'A',
 17                                  attr3       => NULL,
 18                                  attr4       => NULL,
 19       attr5       => NULL,
 20                                  object_id   => obj_id);
 21         dbms_advisor.set_task_parameter(task_name => name,
 22                                         parameter => 'recommend_all',
 23                                         value     => 'TRUE');
 24        dbms_advisor.execute_task(name);
 25       end;
 26  end;
 27  /

PL/SQL 过程已成功完成。

SQL> select af.task_name,
  2             ao.attr2 segname,
  3             ao.attr3 partition,
  4             ao.type,
  5             af.message
  6  from dba_advisor_findings af, dba_advisor_objects ao
  7  where ao.task_id = af.task_id
  8    and ao.object_id = af.object_id
  9    and ao.owner = 'ZAKI';

TASK_NAME                      SEGNAME       MESSAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
--------
advisor_t_test_resumable      

 A
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010              

 B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。
advisor_A_20111010            

 A  

 此对象中的空闲空间小于 10MB。

 

SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                                
---------------
            45 

SQL> alter table b shrink space cascade;
alter table b shrink space cascade
*
第 1 行出现错误:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table b enable row movement;

表已更改。

SQL> select table_name,row_movement from user_tables where table_name='B';

TABLE_NAME                     ROW_MOVE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
------------------------------ --------                                                                                                                                                                                                                                                                                                                                                                                                                                                                         
B                              ENABLED 

SQL> alter table b shrink space cascade;  --回收对象的同时回收对象上索引的空间！

表已更改。

SQL> select bytes/1024/1024 from user_segments where segment_name='B';

BYTES/1024/1024                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
---------------
          20.25                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          

SQL> begin
  2       declare
  3         name   varchar2(100);
  4         descr  varchar2(500);
  5         obj_id number;
  6       begin
  7         name  := 'advisor_ind_'||to_char(sysdate,'yyyymmdd');
  8         descr := 'Segment Advisor Example';
  9         dbms_advisor.create_task(advisor_name => 'Segment Advisor',
 10                                  task_id      => :id,
 11                                 task_name    => name,
 12                                 task_desc    => descr);
 13        dbms_advisor.create_object(task_name   => name,
 14                                  object_type => 'INDEX',
 15                                  attr1       => 'ZAKI',
 16                                  attr2       => 'IND_B',
 17                                  attr3       => NULL,
 18                                  attr4       => NULL,
 19       attr5       => NULL,
 20                                  object_id   => obj_id);
 21         dbms_advisor.set_task_parameter(task_name => name,
 22                                         parameter => 'recommend_all',
 23                                         value     => 'TRUE');
 24        dbms_advisor.execute_task(name);
 25       end;
 26  end;
 27  /

PL/SQL 过程已成功完成。

SQL> select af.task_name,
  2             ao.attr2 segname,
  3             ao.attr3 partition,
  4             ao.type,
  5             af.message
  6  from dba_advisor_findings af, dba_advisor_objects ao
  7  where ao.task_id = af.task_id
  8    and ao.object_id = af.object_id
  9    and ao.owner = 'ZAKI';

TASK_NAME                      SEGNAME           MESSAGE
advisor_t_test_resumable       A
进行压缩, 估计可以省出 10950496 字节。
advisor_20111010               B
启用表 ZAKI.B 的行移动并执行收缩, 估计会节省 21204669 字节。
advisor_A_20111010             A
此对象中的空闲空间小于 10MB。
TASK_NAME                      SEGNAME   MESSAGE

advisor_ind_20111010           IND_B
此对象中的空闲空间小于 10MB。