Re: index block cleanout
Date: Sat, 7 Mar 2009 05:13:57 -0800 (PST)
Message-ID: <58745949-ef9c-4e98-b543-0cf4013c7f46_at_l38g2000vba.googlegroups.com>
On Mar 7, 6:00 am, lsllcm <lsl..._at_gmail.com> wrote:
> Hi All,
>
> I have one question about index block cleanout. When I use two
> commands to cleanout index block. But it does not work at all.
>
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush buffer_cache;
>
> Below are my test case
>
> 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;
>
> commit;
>
> 5. analyze index test_empty_block_idx validate structure;
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 91209
>
> 6.
> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> alter system flush buffer_cache;
>
> 7.
> analyze index test_empty_block_idx validate structure;
>
> select * from index_stats;
> DEL_LF_ROWS
> ---------------------------
> 91209
>
> Thanks at first
> Jacky
Jacky,
I think that part of the trouble with your experiment is that the ALTER SYSTEM FLUSH BUFFER_CACHE; command does something entirely different from what you are expecting it to do. The command does NOT rebuild or coalesce existing indexes. The intention of the command is to force all blocks in the in-memory buffer cache out of the in-memory buffer cache, writing the blocks to disk if necessary - if the blocks were modified in memory, but not previously written to disk. Based on my understanding, it is necessary to execute this command twice to make certain that all in-memory blocks are cleared out of memory.
Maxim, I don't think that removing the COMMIT will help release the
unused blocks in the index (there by reducing the number of deleted
rows in the index structure):
SQL> SELECT VERSION FROM V$INSTANCE;
VERSION
11.1.0.6.0
The same table and index were created.
SQL> begin
2 for i in 1..100000 loop
3 insert into test_empty_block values (i, 'Bowie');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> delete from test_empty_block where id < 99999;
99998 rows deleted.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select DEL_LF_ROWS from index_stats;
DEL_LF_ROWS
99998
SQL> ROLLBACK; Rollback complete.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select DEL_LF_ROWS from index_stats;
DEL_LF_ROWS
99998
SQL> INSERT INTO test_empty_block values (1,NULL);
1 row created.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select DEL_LF_ROWS from index_stats;
DEL_LF_ROWS
99458
SQL> COMMIT; SQL> ALTER INDEX TEST_EMPTY_BLOCK_IDX REBUILD; Index altered.
SQL> analyze index test_empty_block_idx validate structure;
Index analyzed.
SQL> select DEL_LF_ROWS from index_stats;
DEL_LF_ROWS
0
Just to be clear, the above is NOT a suggestion to rebuild indexes. Inserting a single value into the table took care of 40 of the deleted leaf rows automatically, as a block in the index structure containing completely deleted rows was reused.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Mar 07 2009 - 07:13:57 CST