Re: index block cleanout
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sun, 08 Mar 2009 12:07:59 +0100
Message-ID: <49B3A70F.4080100_at_gmail.com>
lsllcm schrieb:
> Thanks all suggestions. You are correct.
>
> After I execute "alter system flush buffer_cache;" before "commit;",
> the delayed block cleanout occurs.
>
> Anothe question:
>
> Here is another statement from Richard Foote.
>
> Fully emptied blocks are placed on freelist and recycled (although
> remain in the index structure). But when I run show_space procedure
> from asktom.
>
> SQL> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> Total Blocks............................256
> Total Bytes.............................2097152
> Unused Blocks...........................0
> Unused Bytes............................0
> Last Used Ext FileId....................4
> Last Used Ext BlockId...................1673
> Last Used Block.........................128
>
> The Unused Blocks still is 0, that means that no blocks are returned
> to free list. It is very strange.
>
>
> Thanks at first
>
> But from below test case. It looks not correct.
>
> 1. create table test_empty_block (id number, value varchar2(10));
>
>
> 2. create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
>
>
> 3.
> begin
> for i in 1..100000 loop
> insert into test_empty_block values (i, 'Bowie');
> end loop;
> commit;
> end;
> /
>
>
> 4. delete from test_empty_block where id < 99999;
>
>
> 5.
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
>
> commit;
>
>
> 6. analyze index test_empty_block_idx validate structure;
>
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 0
>
> 7.
> create or replace procedure show_space
> ( p_segname_1 in varchar2,
> p_space in varchar2 default 'MANUAL',
> p_type_1 in varchar2 default 'TABLE' ,
> p_analyzed in varchar2 default 'N',
> p_owner_1 in varchar2 default user)
> as
> p_segname varchar2(100);
> p_type varchar2(10);
> p_owner varchar2(30);
>
> l_unformatted_blocks number;
> l_unformatted_bytes number;
> l_fs1_blocks number;
> l_fs1_bytes number;
> l_fs2_blocks number;
> l_fs2_bytes number;
> l_fs3_blocks number;
> l_fs3_bytes number;
> l_fs4_blocks number;
> l_fs4_bytes number;
> l_full_blocks number;
> l_full_bytes number;
>
> l_free_blks number;
> l_total_blocks number;
> l_total_bytes number;
> l_unused_blocks number;
> l_unused_bytes number;
> l_LastUsedExtFileId number;
> l_LastUsedExtBlockId number;
> l_LAST_USED_BLOCK number;
>
> procedure p( p_label in varchar2, p_num in number )
> is
> begin
> dbms_output.put_line( rpad(p_label,40,'.') ||
> p_num );
> end;
> begin
> p_segname := upper(p_segname_1); -- rainy changed
> p_owner := upper(p_owner_1);
> p_type := p_type_1;
>
> if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
> p_type := 'INDEX';
> end if;
>
> if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
> p_type := 'TABLE';
> end if;
>
> if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
> p_type := 'CLUSTER';
> end if;
>
>
> dbms_space.unused_space
> ( segment_owner => p_owner,
> segment_name => p_segname,
> segment_type => p_type,
> total_blocks => l_total_blocks,
> total_bytes => l_total_bytes,
> unused_blocks => l_unused_blocks,
> unused_bytes => l_unused_bytes,
> LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
> LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
> LAST_USED_BLOCK => l_LAST_USED_BLOCK );
>
> if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO')
> then
> dbms_space.free_blocks
> ( segment_owner => p_owner,
> segment_name => p_segname,
> segment_type => p_type,
> freelist_group_id => 0,
> free_blks => l_free_blks );
>
> p( 'Free Blocks', l_free_blks );
> end if;
>
> p( 'Total Blocks', l_total_blocks );
> p( 'Total Bytes', l_total_bytes );
> p( 'Unused Blocks', l_unused_blocks );
> p( 'Unused Bytes', l_unused_bytes );
> p( 'Last Used Ext FileId', l_LastUsedExtFileId );
> p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
> p( 'Last Used Block', l_LAST_USED_BLOCK );
>
>
> /*IF the segment is analyzed */
> if p_analyzed = 'Y' then
> dbms_space.space_usage(segment_owner => p_owner ,
> segment_name => p_segname ,
> segment_type => p_type ,
> unformatted_blocks => l_unformatted_blocks ,
> unformatted_bytes => l_unformatted_bytes,
> fs1_blocks => l_fs1_blocks,
> fs1_bytes => l_fs1_bytes ,
> fs2_blocks => l_fs2_blocks,
> fs2_bytes => l_fs2_bytes,
> fs3_blocks => l_fs3_blocks ,
> fs3_bytes => l_fs3_bytes,
> fs4_blocks => l_fs4_blocks,
> fs4_bytes => l_fs4_bytes,
> full_blocks => l_full_blocks,
> full_bytes => l_full_bytes);
> dbms_output.put_line(rpad(' ',50,'*'));
> dbms_output.put_line('The segment is analyzed');
> p( '0% -- 25% free space blocks', l_fs1_blocks);
> p( '0% -- 25% free space bytes', l_fs1_bytes);
> p( '25% -- 50% free space blocks', l_fs2_blocks);
> p( '25% -- 50% free space bytes', l_fs2_bytes);
> p( '50% -- 75% free space blocks', l_fs3_blocks);
> p( '50% -- 75% free space bytes', l_fs3_bytes);
> p( '75% -- 100% free space blocks', l_fs4_blocks);
> p( '75% -- 100% free space bytes', l_fs4_bytes);
> p( 'Unused Blocks', l_unformatted_blocks );
> p( 'Unused Bytes', l_unformatted_bytes );
> p( 'Total Blocks', l_full_blocks);
> p( 'Total bytes', l_full_bytes);
>
> end if;
>
> end;
> /
>
> set serveroutput on
> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> SQL> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> Total Blocks............................256
> Total Bytes.............................2097152
> Unused Blocks...........................0
> Unused Bytes............................0
> Last Used Ext FileId....................4
> Last Used Ext BlockId...................1673
> Last Used Block.........................128
>
> The Unused Blocks still is 0, that means that no blocks are returned
> to free list.
>
Date: Sun, 08 Mar 2009 12:07:59 +0100
Message-ID: <49B3A70F.4080100_at_gmail.com>
lsllcm schrieb:
> Thanks all suggestions. You are correct.
>
> After I execute "alter system flush buffer_cache;" before "commit;",
> the delayed block cleanout occurs.
>
> Anothe question:
>
> Here is another statement from Richard Foote.
>
> Fully emptied blocks are placed on freelist and recycled (although
> remain in the index structure). But when I run show_space procedure
> from asktom.
>
> SQL> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> Total Blocks............................256
> Total Bytes.............................2097152
> Unused Blocks...........................0
> Unused Bytes............................0
> Last Used Ext FileId....................4
> Last Used Ext BlockId...................1673
> Last Used Block.........................128
>
> The Unused Blocks still is 0, that means that no blocks are returned
> to free list. It is very strange.
>
>
> Thanks at first
>
> But from below test case. It looks not correct.
>
> 1. create table test_empty_block (id number, value varchar2(10));
>
>
> 2. create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
>
>
> 3.
> begin
> for i in 1..100000 loop
> insert into test_empty_block values (i, 'Bowie');
> end loop;
> commit;
> end;
> /
>
>
> 4. delete from test_empty_block where id < 99999;
>
>
> 5.
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
>
> commit;
>
>
> 6. analyze index test_empty_block_idx validate structure;
>
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 0
>
> 7.
> create or replace procedure show_space
> ( p_segname_1 in varchar2,
> p_space in varchar2 default 'MANUAL',
> p_type_1 in varchar2 default 'TABLE' ,
> p_analyzed in varchar2 default 'N',
> p_owner_1 in varchar2 default user)
> as
> p_segname varchar2(100);
> p_type varchar2(10);
> p_owner varchar2(30);
>
> l_unformatted_blocks number;
> l_unformatted_bytes number;
> l_fs1_blocks number;
> l_fs1_bytes number;
> l_fs2_blocks number;
> l_fs2_bytes number;
> l_fs3_blocks number;
> l_fs3_bytes number;
> l_fs4_blocks number;
> l_fs4_bytes number;
> l_full_blocks number;
> l_full_bytes number;
>
> l_free_blks number;
> l_total_blocks number;
> l_total_bytes number;
> l_unused_blocks number;
> l_unused_bytes number;
> l_LastUsedExtFileId number;
> l_LastUsedExtBlockId number;
> l_LAST_USED_BLOCK number;
>
> procedure p( p_label in varchar2, p_num in number )
> is
> begin
> dbms_output.put_line( rpad(p_label,40,'.') ||
> p_num );
> end;
> begin
> p_segname := upper(p_segname_1); -- rainy changed
> p_owner := upper(p_owner_1);
> p_type := p_type_1;
>
> if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
> p_type := 'INDEX';
> end if;
>
> if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
> p_type := 'TABLE';
> end if;
>
> if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
> p_type := 'CLUSTER';
> end if;
>
>
> dbms_space.unused_space
> ( segment_owner => p_owner,
> segment_name => p_segname,
> segment_type => p_type,
> total_blocks => l_total_blocks,
> total_bytes => l_total_bytes,
> unused_blocks => l_unused_blocks,
> unused_bytes => l_unused_bytes,
> LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
> LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
> LAST_USED_BLOCK => l_LAST_USED_BLOCK );
>
> if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO')
> then
> dbms_space.free_blocks
> ( segment_owner => p_owner,
> segment_name => p_segname,
> segment_type => p_type,
> freelist_group_id => 0,
> free_blks => l_free_blks );
>
> p( 'Free Blocks', l_free_blks );
> end if;
>
> p( 'Total Blocks', l_total_blocks );
> p( 'Total Bytes', l_total_bytes );
> p( 'Unused Blocks', l_unused_blocks );
> p( 'Unused Bytes', l_unused_bytes );
> p( 'Last Used Ext FileId', l_LastUsedExtFileId );
> p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
> p( 'Last Used Block', l_LAST_USED_BLOCK );
>
>
> /*IF the segment is analyzed */
> if p_analyzed = 'Y' then
> dbms_space.space_usage(segment_owner => p_owner ,
> segment_name => p_segname ,
> segment_type => p_type ,
> unformatted_blocks => l_unformatted_blocks ,
> unformatted_bytes => l_unformatted_bytes,
> fs1_blocks => l_fs1_blocks,
> fs1_bytes => l_fs1_bytes ,
> fs2_blocks => l_fs2_blocks,
> fs2_bytes => l_fs2_bytes,
> fs3_blocks => l_fs3_blocks ,
> fs3_bytes => l_fs3_bytes,
> fs4_blocks => l_fs4_blocks,
> fs4_bytes => l_fs4_bytes,
> full_blocks => l_full_blocks,
> full_bytes => l_full_bytes);
> dbms_output.put_line(rpad(' ',50,'*'));
> dbms_output.put_line('The segment is analyzed');
> p( '0% -- 25% free space blocks', l_fs1_blocks);
> p( '0% -- 25% free space bytes', l_fs1_bytes);
> p( '25% -- 50% free space blocks', l_fs2_blocks);
> p( '25% -- 50% free space bytes', l_fs2_bytes);
> p( '50% -- 75% free space blocks', l_fs3_blocks);
> p( '50% -- 75% free space bytes', l_fs3_bytes);
> p( '75% -- 100% free space blocks', l_fs4_blocks);
> p( '75% -- 100% free space bytes', l_fs4_bytes);
> p( 'Unused Blocks', l_unformatted_blocks );
> p( 'Unused Bytes', l_unformatted_bytes );
> p( 'Total Blocks', l_full_blocks);
> p( 'Total bytes', l_full_bytes);
>
> end if;
>
> end;
> /
>
> set serveroutput on
> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> SQL> exec show_space ( 'TEST_EMPTY_BLOCK_IDX','AUTO', 'INDEX' );
>
> Total Blocks............................256
> Total Bytes.............................2097152
> Unused Blocks...........................0
> Unused Bytes............................0
> Last Used Ext FileId....................4
> Last Used Ext BlockId...................1673
> Last Used Block.........................128
>
> The Unused Blocks still is 0, that means that no blocks are returned
> to free list.
>
If you have access to metalink, you can lookup a note 116565.1, where (shortened) is stated:
<cite>
The UNUSED_SPACE procedure refers to unused space above the high
water mark in a segment.
The FREE_BLOCKS procedure refers to the blocks in a segment
below the high water mark whose number of rows falls below
the PCTUSED attribute, and therefore are candidate for new
inserted rows.
</cite>
On assm tablespaces you get reported only blocks above highwatermark, if you run your test on index created in tablespace with segment space management manual, you'll see anoher results. You can alternatively do a treedump and see, that the leaf blocks are empty (number of rows equals to number of deleted rows equals to 0) or insert some values again into this table, to see, no new blocks ( above highwatermark ) are allocated but the existing blocks are reused.
Best regards
Maxim Received on Sun Mar 08 2009 - 06:07:59 CDT