segment advisor-CSDN博客

本文介绍如何使用SegmentAdvisor工具分析并解决数据库中由于频繁更新和删除操作导致的段空间碎片问题。通过具体案例展示了如何创建任务、指定参数及执行任务来评估表空间，并通过调整表属性来释放空间。

段空间内进行了许多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>