Re: index block cleanout

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sat, 7 Mar 2009 06:24:44 -0800 (PST)
Message-ID: <91a0ef33-2ea2-4975-9567-fec56f1e7ef5_at_s36g2000vbp.googlegroups.com>

On Mar 7, 8:31 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> Charles Hooper schrieb:
> > 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):
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles, the point of index block cleanout ( as i understood it) is ,
> that if you force to write index blocks to disk before commiting
> associated transaction, then deleted entries may be cleaned out of index
> structure ( bad rephrasing of original blog entry ).
> So, what i suggested to do is - take original test case and comment out
> commits to demonstrate the described behaviour:
>
> SQL> -- 1. create table test_empty_block (id number, value varchar2(10));
> SQL> create table test_empty_block (id number, value varchar2(10));
>
> Table created.
>
> SQL>
> SQL> -- 2. create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
> SQL> create index TEST_EMPTY_BLOCK_IDX on test_empty_block (id);
>
> Index created.
>
> SQL> -- 3.
> SQL> -- begin
> SQL> -- for i in 1..100000 loop
> SQL> -- insert into test_empty_block values (i, 'Bowie');
> SQL> -- end loop;
> SQL> -- commit;
> SQL> -- end;
> SQL> -- /
> SQL>
> SQL> begin
>    2  for i in 1..100000 loop
>    3  insert into test_empty_block values (i, 'Bowie');
>    4  end loop;
>    5  commit;
>    6  end;
>    7  /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL> -- 4. delete from test_empty_block where id < 99999;
> SQL> delete from test_empty_block where id < 99999;
>
> 99998 rows deleted.
>
> SQL> -- commit;
> SQL> -- *Commit* is not issued at this point as suggested by me
> SQL>
> SQL> -- 5. analyze index test_empty_block_idx validate structure;
> SQL> -- *Analyze* is not issued as suggested at this point as well
> SQL>
> SQL> -- select * from index_stats;
> SQL>
> SQL> -- 6.
> SQL> -- ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
> SQL> -- alter system flush buffer_cache;
> SQL> ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
>
> Session altered.
>
> SQL> alter system flush buffer_cache;
>
> System altered.
>
> SQL>
> SQL> -- 7.
> SQL> -- analyze index test_empty_block_idx validate structure;
> SQL> -- select * from index_stats;
> SQL> analyze index test_empty_block_idx validate structure;
>
> Index analyzed.
>
> SQL> select blocks,name,lf_rows,del_lf_rows from index_stats;
>
>      BLOCKS NAME                              LF_ROWS DEL_LF_ROWS
> ---------- ------------------------------ ---------- -----------
>         256 TEST_EMPTY_BLOCK_IDX                    2           0
>
> Best regards
>
> Maxim
>
> (Of course, it is not meant to be a substitute for index rebuild, but it
> may accent the point, that index rebuild may be avoided in some cases).

Maxim,

As odd as it may seem, I think that we are both correct (if you ignore my incorrect statement about the number of deleted leaf rows reported for the index), even though we are saying different things. I stated: "Maxim, I don't think that removing the COMMIT will help release the unused blocks in the index (incorrect information here ...)"

SQL> CREATE TABLE TEST_EMPTY_BLOCK (ID NUMBER NOT NULL, VALUE VARCHAR2 (10));

Table created.

SQL> CREATE INDEX TEST_EMPTY_BLOCK_IDX ON TEST_EMPTY_BLOCK(ID); Index 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> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered.

SQL> COMMIT; Commit complete.

SQL> ANALYZE INDEX TEST_EMPTY_BLOCK_IDX VALIDATE STRUCTURE; Index analyzed.

SQL> SELECT
  2 NAME,
  3 LF_BLKS,
  4 DEL_LF_ROWS,
  5 BTREE_SPACE,
  6 USED_SPACE
  7 FROM
  8 INDEX_STATS;

NAME                              LF_BLKS DEL_LF_ROWS BTREE_SPACE

USED_SPACE

------------------------------ ---------- ----------- -----------
----------
TEST_EMPTY_BLOCK_IDX                  199           0     1600032

1591250

SQL> DELETE FROM TEST_EMPTY_BLOCK WHERE ID < 99999;

99998 rows deleted.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered.

SQL> COMMIT; Commit complete.

SQL> ANALYZE INDEX TEST_EMPTY_BLOCK_IDX VALIDATE STRUCTURE; Index analyzed.

SQL> SELECT
  2 NAME,
  3 LF_BLKS,
  4 DEL_LF_ROWS,
  5 BTREE_SPACE,
  6 USED_SPACE
  7 FROM
  8 INDEX_STATS;

NAME                              LF_BLKS DEL_LF_ROWS BTREE_SPACE

USED_SPACE

------------------------------ ---------- ----------- -----------
----------
TEST_EMPTY_BLOCK_IDX                  199           0
1600032       2388

The above worked as you stated, that the number of DEL_LF_ROWS did drop to 0 (interesting). But, as my initial post indicated, the number of leaf blocks stayed the same, and a single insert into the index caused Oracle to clean out all of the deleted rows in the index block when it was reused. I guess the point that I was trying to make is that it does not matter that DEL_LF_ROWS is not equal to 0 if most of the blocks in the index structure are completely empty - the blocks may still be reused, the fact that one test shows that DEL_LF_ROWS is 0 while the other shows that DEL_LF_ROWS is 99998 (or 83709 in another slight variation of the test) should not affect index performance in this specific case because in each case, the same number of blocks remained in the index structure.

I think that the only thing that the above proves is that far too many people have been reading a certain Oracle index expert's blog, and were able to recognize the source of any test which uses 'Bowie'. :-)

It was nice talking with you again.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Mar 07 2009 - 08:24:44 CST