ITPub博客

首页 > 数据库 > Oracle > Oracle10g New Feature --12. ASSM ( Automatic Segment Space Management )

Oracle10g New Feature --12. ASSM ( Automatic Segment Space Management )

原创 Oracle 作者:zhyuh 时间:2004-09-27 09:20:37 0 删除 编辑

关于ASSM,最主要的内容是dbms_space包的几个procedures。利用package dbms_space,我们可以确定segment中各个block的空间使用情况,预测segment能否shrink到期望的大小,预测要建索引的大小,存储参数改变时segment size的变化等。

Oracle10g的ASSM另外一个新特性是可以在线回收segment的空闲block

[@more@]

ASSM ( Automatic Segment Space Management )

We use table ZHYUH.EMP as the test object.

1.     find out exactly how much space is wasted in that segment that can be reclaimed

SQL>set serveroutput on

 

SQL>declare

   l_fs1_bytes number;

   l_fs2_bytes number;

   l_fs3_bytes number;

   l_fs4_bytes number;

   l_fs1_blocks number;

   l_fs2_blocks number;

   l_fs3_blocks number;

   l_fs4_blocks number;

   l_full_bytes number;

   l_full_blocks number;

   l_unformatted_bytes number;

   l_unformatted_blocks number;

begin

   dbms_space.space_usage(

      segment_owner      => user,

      segment_name       => 'EMP',

      segment_type       => 'TABLE',

      fs1_bytes          => l_fs1_bytes,

      fs1_blocks         => l_fs1_blocks,

      fs2_bytes          => l_fs2_bytes,

      fs2_blocks         => l_fs2_blocks,

      fs3_bytes          => l_fs3_bytes,

      fs3_blocks         => l_fs3_blocks,

      fs4_bytes          => l_fs4_bytes,

      fs4_blocks         => l_fs4_blocks,

      full_bytes         => l_full_bytes,

      full_blocks        => l_full_blocks,

      unformatted_blocks => l_unformatted_blocks,

      unformatted_bytes  => l_unformatted_bytes

   );

   dbms_output.put_line(' FS1 Blocks = '||to_char(l_fs1_blocks)||' Bytes = '||to_char(l_fs1_bytes));

   dbms_output.put_line(' FS2 Blocks = '||to_char(l_fs2_blocks)||' Bytes = '||to_char(l_fs1_bytes));

   dbms_output.put_line(' FS3 Blocks = '||to_char(l_fs3_blocks)||' Bytes = '||to_char(l_fs1_bytes));

   dbms_output.put_line(' FS4 Blocks = '||to_char(l_fs4_blocks)||' Bytes = '||to_char(l_fs1_bytes));

   dbms_output.put_line(' Full Blocks = '||to_char(l_full_blocks)||' Bytes = '||to_char(l_full_bytes));

end;

/

FS1 Blocks = 0 Bytes = 0

FS2 Blocks = 1 Bytes = 0

FS3 Blocks = 0 Bytes = 0

FS4 Blocks = 5300 Bytes = 0

Full Blocks = 739 Bytes = 6053888

 

 

fs1_blocks

Number of blocks that has at least 0 to 25% free space

fs1_bytes

Number of bytes that has at least 0 to 25% free space

fs2_blocks

Number of blocks that has at least 25 to 50% free space

fs2_bytes

Number of bytes that has at least 25 to 50% free space

fs3_blocks

Number of blocks that has at least 50 to 75% free space

fs3_bytes

Number of bytes that has at least 50 to 75% free space

fs4_blocks

Number of blocks that has at least 75 to 100% free space

fs4_bytes

Number of bytes that has at least 75 to 100% free space

ful1_blocks

Total number of blocks that are full in the segment

full_bytes

Total number of bytes that are full in the segment

 

SQL> select blocks from user_segments where segment_name='EMP';

    BLOCKS

----------

      6152

 

2.       Ensure that the table is row-movement enabled

SQL> alter table emp enable row movement;

Table altered.

 

3.       Shrink table EMP but NOT move the HWM(High Water Mark)

SQL> alter table emp shrink space compact;

Table altered.

 

Re-execute dbma_space.space_usage();

FS1 Blocks = 1 Bytes = 8192

FS2 Blocks = 216 Bytes = 8192

FS3 Blocks = 220 Bytes = 8192

FS4 Blocks = 172 Bytes = 8192

Full Blocks = 541 Bytes = 4431872

 

SQL> select blocks from user_segments where segment_name='EMP';

    BLOCKS

----------

      6152

 

The values of FS1-FS4 changed, but the block number keeps   the same. So the HWM has not been reset.

You can also compact the objects based the table in one statement:

SQL>alter table emp shrink space cascade;

4.       Shrink table EMP and move the HWM(High Water Mark)

SQL> alter table emp shrink space;

Table altered.

 

Re-execute dbma_space.space_usage();

FS1 Blocks = 1 Bytes = 8192

FS2 Blocks = 216 Bytes = 8192

FS3 Blocks = 220 Bytes = 8192

FS4 Blocks = 5043 Bytes = 8192

Full Blocks = 541 Bytes = 4431872

 

SQL> select blocks from user_segments where segment_name='EMP';

    BLOCKS

----------

      6128

 

The number of blocks decreased

5.       To determine if an object can be shrunk to specified size

SQL> declare

  2    owner varchar2(20) :='ZHYUH';

  3    object_name varchar2(20) :='EMP';

  4    object_type varchar2(20) :='TABLE';

  5    expect_size number :=102400;

  6  begin

  7     if (dbms_space.verify_shrink_candidate(owner,object_name,object_type,expect_size)

  9     ) then

 10         dbms_output.put_line(object_type||' '||object_name||' can be shrunk to '||to_char(expect_size)||' bytes');

 11     else

 12         dbms_output.put_line(object_type||' '||object_name||' can NOT be shrunk to '||to_char(expect_size)||' bytes');

 13     end if;

 14

 15  end;

 16  /

TABLE EMP can NOT be shrunk to 102400 bytes

 

PL/SQL procedure successfully completed.

6.       To estimate the size of index (when the storage parameters changed)

Precondition:

1.       to tablespaces with SEGMENT SPACE MANAGEMENT AUTO turned on

2.       the package calculates the estimated size of the index from the statistics on the table

 

SQL> declare

  2     l_used_bytes number;

  3     l_alloc_bytes number;

  4  begin

  5     dbms_space.create_index_cost (

  6        ddl => 'create index idx_emp_01 on emp '||'(empno) tablespace example initrans 10',

  7        used_bytes => l_used_bytes,

  8        alloc_bytes => l_alloc_bytes

  9     );

 10     dbms_output.put_line ('Used Bytes      = '||l_used_bytes);

 11     dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);

 12  end;

 13  /

Used Bytes      = 8202336

Allocated Bytes = 34603008

 

PL/SQL procedure successfully completed.

 

7.       To estimate the table size when PCT_FREE value changed

For example, when pctfree is changed from 20 to 30,

SQL> declare

  2     l_used_bytes number;

  3     l_alloc_bytes number;

  4  begin

  5     dbms_space.create_table_cost (

  6         tablespace_name => 'EXAMPLE',

  7         avg_row_size => 30,

  8         row_count => 40000,

  9         pct_free => 20,

 10         used_bytes => l_used_bytes,

 11         alloc_bytes => l_alloc_bytes

 12     );

 13     dbms_output.put_line('Used: '||l_used_bytes);

 14     dbms_output.put_line('Allocated: '||l_alloc_bytes);

 15  end;

 16  /

Used: 1687552

Allocated: 2097152

 

Re-execute the PL/SQL block with pct_free=30, we have the result:

Used: 1916928

Allocated: 2097152

 

8.       Predicting the Growth of a Segment

SQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));

TIMEPOINT                                     SPACE_USAGE     SPACE_ALLOC     QUALITY

--------------------------------------------------------------------------- ----------- ----------- --------------------

18-SEP-04 04.10.44.410000 PM      116596672              201326592            INTERPOLATED

19-SEP-04 04.10.44.410000 PM       116596672             201326592            INTERPOLATED

20-SEP-04 04.10.44.410000 PM       1830555                 1835008                INTERPOLATED

21-SEP-04 04.10.44.410000 PM       115208372             198913387            GOOD

22-SEP-04 04.10.44.410000 PM       114514222             197706785            PROJECTED

23-SEP-04 04.10.44.410000 PM       113820072             196500182            PROJECTED

 

The value of  column QUALITY:

GOOD: The space allocated and usage figures are accurate

PROJECTED:  The space calculations are projected from the data collected by the AWR facility—not collected directly from the segment.

INTERPOLATED: Most likely the data was not available at that time and hence the values had to be interpolated.

 

 

 

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

请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2012289