segment advisor

段空间内进行了许多update和delete操作以后容易出现空间的空闲碎片，这将会给dml操作带来一定的性能影响。
使用segment advisor可以帮助判断是否有空间可以回收，
advisor可以在三个级别给出建议信息：
1，对象级别(object)
2，段级别(segment)
3，表空间级别(tablespace)

dbms_advisor.create_task(
advisor_name,  --
task_id,    --
task_name,   --
task_desc    --
);

dbms_advisor.create_object(
task_name      ,--
 object_type      ,--
 attr1            ,--
 attr2            ,
 attr3            ,
 attr4            ,
 attr5            ,
 object_id  
);

参数的详细解析如下：
Input Parameter
OBJECT_TYPE      ATTR1          ATTR2       ATTR3         ATTR4
TABLESPACE      tablespace_name      NULL        NULL          Unused. Specify NULL.
TABLE        schema_name        table_name     NULL          Unused. Specify NULL.
INDEX        schema_name        index_name     NULL          Unused. Specify NULL.
TABLE PARTITION   schema_name        table_name     table_partition_name  Unused. Specify NULL.
INDEX PARTITION     schema_name        index_name     index_partition_name Unused. Specify NULL.
TABLE SUBPARTITION schema_name        table_name     table_subpartition_name Unused. Specify NULL.
INDEX SUBPARTITION schema_name        index_name     index_subpartition_name Unused. Specify NULL.

dbms_advisor.set_task_parameter(
task_name       ,--
parameter       ,--
value           ,--
);

上面的parameter和默认值分别为：
MODE     COMPREHENSIVE
TIME_LIMIT  UNLIMITED
RECOMMEND_ALL TRUE
      
一个对象级别（table）的例子：

SQL>  select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='T_TEST_RESUMABLE';
 
SEGMENT_NAME                                                                     SEGMENT_TYPE       BYTES/1024/1024
-------------------------------------------------------------------------------- ------------------ ---------------
T_TEST_RESUMABLE                                                                 TABLE                           11
 
SQL> select count(*) from t_test_resumable;
 
  COUNT(*)
----------
     99942
 
SQL> delete from t_test_resumable where rownum <50000;
 
49999 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL>
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_t_test_resumable';
  8      descr := 'Segment Advisor Example';
  9 
 10      dbms_advisor.create_task(advisor_name => 'Segment Advisor',
 11                               task_id      => :id,
 12                               task_name    => name,
 13                               task_desc    => descr);
 14 
 15      dbms_advisor.create_object(task_name   => name,
 16                                 object_type => 'TABLE',
 17                                 attr1       => 'TEST',
 18                                 attr2       => 'T_TEST_RESUMABLE',
 19                                 attr3       => NULL,
 20                                 attr4       => NULL,
 21                                 attr5       => NULL,
 22                                 object_id   => obj_id);
 23 
 24      dbms_advisor.set_task_parameter(task_name => name,
 25                                      parameter => 'recommend_all',
 26                                      value     => 'TRUE');
 27 
 28      dbms_advisor.execute_task(name);
 29    end;
 30  end;
 31 
 32  /
 
PL/SQL procedure successfully completed
id
---------
888
 
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 = 'TEST';
 
TASK_NAME                      SEGNAME                                                                          PARTITION                                                                        TYPE                                                             MESSAGE
------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------------------- --------------------------------------------------------------------------------
advisor_t_test_resumable       T_TEST_RESUMABLE                                                                                                                                                  TABLE                                                            The free space in the object is less than 10MB.
 
SQL>
SQL>
SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
 
SEGMENT_NAME                                                                     SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- --------------------
T_TEST_RESUMABLE                                                                                   11
TEST_STORAGE2                                                                                  0.3125
TEST_STORAGE1                                                                                  0.0625
TEST_STORAGE3                                                                                       2
TEST_STORAGE                                                                                     0.25
 
SQL> select table_name,row_movement from user_tables where table_name='T_TEST_RESUMABLE';
 
TABLE_NAME                     ROW_MOVEMENT
------------------------------ ------------
T_TEST_RESUMABLE               DISABLED
 
SQL> alter table t_test_resumable enable row movement;
 
Table altered
 
SQL> alter table t_test_resumable shrink space;
 
Table altered
 
SQL> Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name;
 
SEGMENT_NAME                                                                     SUM(BYTES)/1024/1024
-------------------------------------------------------------------------------- --------------------
T_TEST_RESUMABLE                                                                                    5
TEST_STORAGE2                                                                                  0.3125
TEST_STORAGE1                                                                                  0.0625
TEST_STORAGE3                                                                                       2
TEST_STORAGE                                                                                     0.25
 
SQL>

 

表占用的11m空间通过shrink后释放了6m。

 

 

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

转载于:http://blog.itpub.net/16179598/viewspace-622387/