使用10g 的Segment Advisor来确认是否需要对表做shrink

最新推荐文章于 2025-07-08 11:22:49 发布

转载最新推荐文章于 2025-07-08 11:22:49 发布 · 62 阅读

本文介绍如何在Oracle 10g中利用SegmentAdvisor工具评估表空间利用率，并根据建议执行shrink操作以优化存储。通过示例展示了从创建评估任务到实施优化的全过程。

Segment Advisor是10g中新增的一个工具，可以用来估算表的空间利用率，并给出相应的建议，确定是否需要进行shrink。以下为测试过程：

1. 表test大小为80M，初始时数据比较紧凑，对其执行批量delete操作

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
--------------------------------------------------------------------------------- ------------------ ---------------
TEST TABLE 80

SQL> select count(*) from test;

COUNT(*)
----------
738624

SQL> delete from test where rownum<=400000;

400000 rows deleted.

SQL> commit;

Commit complete.

2.执行Segment Advisor，对表的存储空间进行检查

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

PL/SQL procedure successfully completed.

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

TASK_NAME            SEGNAME              PARTITION TYPE       MESSAGE
-------------------- -------------------- ---------- ---------- -------------------------
TEST                 TEST                            TABLE      Enable row movement of th
                                                                e table SYS.TEST and perf
                                                                orm shrink, estimated sav
                                                                ings is 43132568 bytes.

从可以看出，Segment Advisor给出的建议是对表做shrink，并且估算出可以节省的空间约为43132568 bytes。

3. 执行shrink操作

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

TABLE_NAME ROW_MOVE
------------------------------ --------
TEST DISABLED

SQL> alter table test enable row movement;

Table altered.

SQL> alter table test shrink space;

Table altered.

SQL> select segment_name,segment_type,bytes/1024/1024 from user_segments where segment_name='TEST';

SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024
------------------------------ ------------------ ---------------
TEST TABLE 34

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

转载于:http://blog.itpub.net/10972173/viewspace-600791/