ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用10g 的Segment Advisor来确认是否需要对表做shrink

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

原创 Linux操作系统 作者:lsq_008 时间:2009-05-22 09:55:27 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: shrink机制初探
下一篇: expdp时遭遇ORA-600
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1236027