Re: index block cleanout

From: lsllcm <lsllcm_at_gmail.com>
Date: Sat, 7 Mar 2009 21:05:16 -0800 (PST)
Message-ID: <bc1b9a26-66e3-4a73-bd43-3698ad3fecb1_at_t3g2000yqa.googlegroups.com>

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. Received on Sat Mar 07 2009 - 23:05:16 CST