Re: index block cleanout
From: lsllcm <lsllcm_at_gmail.com>
Date: Sun, 8 Mar 2009 22:28:41 -0700 (PDT)
Message-ID: <ac9d31f6-804b-41d7-aa80-84db46bcd54c_at_a12g2000yqm.googlegroups.com>
On Mar 8, 7:07 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Date: Sun, 8 Mar 2009 22:28:41 -0700 (PDT)
Message-ID: <ac9d31f6-804b-41d7-aa80-84db46bcd54c_at_a12g2000yqm.googlegroups.com>
On Mar 8, 7:07 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Thanks, you are right, after change tablespace to manual, I see the freeblks. Received on Mon Mar 09 2009 - 00:28:41 CDT