DBA_TABLES.EMPTY_BLOCKS
时间：2011-03-30 来源：maojinyu
| BMB versus Freelist Segment: DBMS_SPACE.UNUSED_SPACE and DBA_TABLES.EMPTY_BLOCKS [ID 149516.1] | |||||
| Modified 22-OCT-2010 Type BULLETIN Status PUBLISHED | |||||
PURPOSE
-------
This bulletin explains how to interpret the DBA_TABLES.EMPTY_BLOCKS value and
DBMS_SPACE.UNUSED_SPACE information regarding FREELIST and BMB (Bit Mapped Block) segments in 9i.
SCOPE & APPLICATION
-------------------
The following examples should help the DBAs not to misunderstand the values
returned by different sources of information regarding the space used within
blocks.
*** *****************
*** Freelist segments
*** *****************
NOTE: LOB PARTITION size information is not supported in the DBMS_SPACE
package until 10g.
Also, note that passing a LOB partition to dbms_space() without
specifying a partition name yields an internal error (ORA-600).
To get the size of LOB PARTITIONS use dba_segments when the database version is
9i, instead of using DBMS_SPACE package. If the database version is 10g or higher then DBMS_SPACE can be used.
When DBMS_SPACE.UNUSED_SPACE relates 3 blocks unused, DBA_TABLES.EMPTY_BLOCKS
displays the same value 3.
This means that there are 3 blocks unused that could be deallocated if all
restrictions were met. (Refer Note:115586.1)
Example
-------
SQL> create table FREELIST_T (C varchar2(10))
2 tablespace FREELIST_TBS;
Table created.
SQL> begin
2 for b in 1..19 loop
3 for i in 1..324 loop
4 insert into FREELIST_T values (i);
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze table FREELIST_T compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from dba_tables
2 where table_name='FREELIST_T';
BLOCKS EMPTY_BLOCKS
---------- ------------
10 2
SQL> set serveroutput on
SQL>
SQL> declare
2 TOTAL_BLOCKS number;
3 TOTAL_BYTES number;
4 UNUSED_BLOCKS number;
5 UNUSED_BYTES number;
6 LAST_USED_EXTENT_FILE_ID number;
7 LAST_USED_EXTENT_BLOCK_ID number;
8 LAST_USED_BLOCK number;
9
10 begin dbms_space.unused_space('SYSTEM', 'FREELIST_T', 'TABLE',
11 TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
12 LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
13 LAST_USED_BLOCK);
14
15 dbms_output.put_line('OBJECT_NAME = FREELIST_T');
16 dbms_output.put_line('-----------------------------------');
17 dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
18 dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
19 end;
20 /
OBJECT_NAME = FREELIST_T
-----------------------------------
TOTAL_BLOCKS = 13
UNUSED_BLOCKS = 2
PL/SQL procedure successfully completed.
Conclusion
----------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE
------------------------ ------------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS
------------------------ ------------------------------
10 2 13 2
10 blocks + 2 empty blocks + 1 header segment block = 13 Total blocks
The information between both sources is consistent.
*** **************
*** BMB segments
*** **************
When DBMS_SPACE.UNUSED_SPACE relates 0 block unused, DBA_TABLES.EMPTY_BLOCKS
displays another value:
Example
-------
SQL> create table TEST (C varchar2(10))
2 tablespace BMB_TBS ;
Table created.
SQL> begin
2 for b in 1..19 loop
3 for i in 1..324 loop
4 insert into TEST values (i);
5 end loop;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> analyze table TEST compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks from dba_tables
2 where table_name='TEST';
BLOCKS EMPTY_BLOCKS
---------- ------------
10 3
SQL> set serveroutput on
SQL>
SQL> declare
2 TOTAL_BLOCKS number;
3 TOTAL_BYTES number;
4 UNUSED_BLOCKS number;
5 UNUSED_BYTES number;
6 LAST_USED_EXTENT_FILE_ID number;
7 LAST_USED_EXTENT_BLOCK_ID number;
8 LAST_USED_BLOCK number;
9
10 begin dbms_space.unused_space('SYSTEM', 'TEST', 'TABLE',
11 TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
12 LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
13 LAST_USED_BLOCK);
14
15 dbms_output.put_line('OBJECT_NAME = TEST');
16 dbms_output.put_line('-----------------------------------');
17 dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
18 dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
19 end;
20 /
OBJECT_NAME = TEST
-----------------------------------
TOTAL_BLOCKS = 13
UNUSED_BLOCKS = 0
PL/SQL procedure successfully completed.
Conclusion 1
------------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE
------------------------ ------------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS
------------------------ ------------------------------
10 3 13 0
One source of information says there are 3 blocks empty and the other 0.
New procedure DBMS_SPACE.SPACE_USAGE for BMB segments
-----------------------------------------------------
This new procedure provides the space usage ratio within each block. This
procedure replaces the DBMS_SPACE.FREE_BLOCKS procedure for BMB segments.
(refer Note:116565.1)
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'TEST', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 9
PL/SQL procedure successfully completed.
where
FS1 means 0-25% free space within a block
FS2 means 25-50% free space within a block
FS3 means 50-75% free space within a block
FS4 means 75-100% free space within a block
Conclusion 2
------------
DBA_TABLES DBMS_SPACE.UNUSED_SPACE DBMS_SPACE.SPACE_USAGE
--------------------- ---------------------------- ------------------------
BLOCKS | EMPTY_BLOCKS TOTAL_BLOCKS | UNUSED_BLOCKS FS4 Blocks | Full Blocks
--------------------- ---------------------------- ------------------------
10 3 13 0 1 9
1 FS4 (75-100% free) + 9 Full blocks = 10 blocks used
What are the 3 extra blocks related to EMPTY_BLOCKS though mentioned as
UNUSED by DBMS_SPACE.UNUSED_SPACE procedure ?
Dump extra blocks
-----------------
If you dump the blocks of the segment, the 3 blocks are the Bitmap Blocks used
to track space used in the segment.
Bitmap blocks of 3 different levels:
1. Extent Control Header contains the block addresses of other Bitmap blocks
Last Level 1 BMB: 0x00800009 => means one L1 bitmap block
Last Level II BMB: 0x0080000a => means one L2 bitmap block
Last Level III BMB: 0x00000000 => means no L3 bitmap block
and the extent map of the segment:
--------------------------------------------------
Extent 0 : L1 dba: 0x00800009 Data dba: 0x0080000c => means 1 extent with
-------------------------------------------------- one L1 bitmap block
2. First Level Bitmap Block contains the freeness of the blocks for the extent:
0:Metadata 1:Metadata 2:Metadata 3:FULL
4:FULL 5:75-100% free 6:FULL 7:FULL
8:FULL 9:FULL 10:FULL 11:FULL
12:FULL
This shows that the first 3 blocks (0,1,2) are used for metadata, and they
cannot contain any data. This is explicitely written in this Level1 bitmap
block itself:
first useful block: 3
Blocks 3 to 12 are full of data.
3. Second Level Bitmap Block contains the addresses of the Bitmap blocks of
Level 1:
L1 Ranges :
--------------------------------------------------------
0x00800009 Free: 5 Inst: 1
Another example of bitmap blocks of a segment with 3 extents
------------------------------------------------------------
1. Extent Control Header contains the block addresses of other Bitmap blocks
Last Level 1 BMB: 0x00800023
Last Level II BMB: 0x0080000a
Last Level III BMB: 0x00000000
and the extent map of the segment:
--------------------------------------------------------
Extent 0 : L1 dba: 0x00800009 Data dba: 0x0080000c -------------------
Extent 1 : L1 dba: 0x00800016 Data dba: 0x00800017 -------------- |
Extent 2 : L1 dba: 0x00800023 Data dba: 0x00800024 -------- | |
-------------------------------------------------------- | | |
| | |
1st Extent | | |
| | |
0:Metadata 1:Metadata 2:Metadata 3:FULL | | |
4:FULL 5:FULL 6:FULL 7:FULL | | <---
8:FULL 9:FULL 10:FULL 11:FULL | |
12:FULL | |
| |
2nd Extent | |
| |
0:Metadata 1:75-100% free 2:75-100% free 3:75-100% free | |
4:75-100% free 5:75-100% free 6:75-100% free 7:75-100% free| <---
8:75-100% free 9:75-100% free 10:75-100% free 11:75-100% free|
12:75-100% free |
|
3rd Extent |
|
0:Metadata 1:unformatted 2:unformatted 3:unformatted |
4:unformatted 5:unformatted 6:unformatted 7:unformatted <--
8:unformatted 9:unformatted 10:unformatted 11:unformatted
12:unformatted
* Second Level Bitmap Block contains the addresses of the Bitmap blocks of
Level 1:
L1 Ranges :
--------------------------------------------------------
0x00800009 Free: 1 Inst: 1
0x00800016 Free: 5 Inst: 1
0x00800023 Free: 5 Inst: 1
CONCLUSION
----------
DBA_TABLES.EMPTY_BLOCKS column displays the right number of unused blocks for
Freelist segments, but not for BMB segments.
For BMB segments, use the DBMS_SPACE.UNUSED_SPACE procedure to get the right
number of unused blocks returned by the parameter UNUSED_BLOCKS.
RELATED DOCUMENTS
-----------------
Note:180608.1 Automatic Space Segment Management in RAC Environments
Note:115586.1 How to Deallocate Unused Space from a Table, Index or Cluster.
Note:116565.1 When to use DBMS_SPACE.UNUSED_SPACE or DBMS_SPACE.FREE_BLOCKS
Procedures
|
Related
Products
|
||
相关阅读 更多 +
