Oracle10g New Feature --12. ASSM ( Automatic Segment Space Management )
关于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 reclaimedSQL>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 enabledSQL> 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 sizeSQL> 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 changedFor 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 SegmentSQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
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.
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 enabledSQL> 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 sizeSQL> 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 changedFor 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 SegmentSQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
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.
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 sizeSQL> 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 changedFor 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 SegmentSQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
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.
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 changedFor 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 SegmentSQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
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.
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 SegmentSQL>select * from table(dbms_space.OBJECT_GROWTH_TREND ('ZHYUH','EMP','TABLE'));
TIMEPOINT SPACE_USAGE SPACE_ALLOC QUALITY
--------------------------------------------------------------------------- ----------- ----------- --------------------
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博客 ” ，链接：https://blog.itpub.net/207/viewspace-778906/ ，如需转载，请注明出处，否则将追究法律责任。
