coalesc will really cleans all the empty blocks and takes them out of the index structure?

From: Çå²è <maclean_007_at_163.com>
Date: Fri, 22 Jan 2010 20:00:09 +0800 (CST)
Message-ID: <24233214.720861264161609686.JavaMail.coremail_at_bj163app46.163.com>

Hi ,
  My customer has a problem with wait event TX:index cotention. Oracle support suggest we should coalesce or reuild the index.   Coalesce is less resource sensitive ,So i'd like to using coalesce.   But as flow test:
  19:46:45 SQL> create table test(t1 int) tablespace datatb;

 Table created.
SQL> create index ind_t1 on test(t1);

  Index created.

Elapsed: 00:00:00.01

19:47:08 SQL> begin
19:47:17   2    for i in 1..20000 loop
19:47:17   3      insert into test values(i);
19:47:17   4      commit;
19:47:17   5      end loop;
19:47:17   6      end; 
19:47:18   7  /

PL/SQL procedure successfully completed. SQL> set serveroutput on;
SQL> declare
  2

  3     l_fs1_bytes number;
  4     l_fs2_bytes number;
  5     l_fs3_bytes number;
  6     l_fs4_bytes number;
  7     l_fs1_blocks number;
  8     l_fs2_blocks number;
  9     l_fs3_blocks number;
 10     l_fs4_blocks number;
 11     l_full_bytes number;
 12     l_full_blocks number;
 13     l_unformatted_bytes number;
 14     l_unformatted_blocks number;
 15  begin
 16     dbms_space.space_usage(
 17        segment_owner      => user,
 18        segment_name       => 'IND_T1',
 19        segment_type       => 'INDEX',
 20        fs1_bytes          => l_fs1_bytes,
 21        fs1_blocks         => l_fs1_blocks,
 22        fs2_bytes          => l_fs2_bytes,
 23        fs2_blocks         => l_fs2_blocks,
 24        fs3_bytes          => l_fs3_bytes,
 25        fs3_blocks         => l_fs3_blocks,
 26        fs4_bytes          => l_fs4_bytes,
 27        fs4_blocks         => l_fs4_blocks,
 28        full_bytes         => l_full_bytes,
 29        full_blocks        => l_full_blocks,
 30        unformatted_blocks => l_unformatted_blocks,
 31        unformatted_bytes  => l_unformatted_bytes
 32     );
 33     dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes = '||l_fs1_bytes);
 34     dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
 35     dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes = '||l_fs3_bytes);
 36     dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
 37     dbms_output.put_line('Full Blocks = '||l_full_blocks||' Bytes = '||l_full_bytes);

 38 end;
 39 /

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 4 Bytes = 32768
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 39 Bytes = 319488

PL/SQL procedure successfully completed.

Full blocks number is 39 ; fs2 is 4

SQL> delete test where t1>10000 and (mod(t1,2)!=0);

5000 rows deleted.
Now :

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 4 Bytes = 32768
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 39 Bytes = 319488
nothing changed .

SQL> alter index ind_t1 coalesce;

Index altered.

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 13 Bytes = 106496
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 30 Bytes = 245760

the free blocks increased

SQL> delete test where t1>10000;

5000 rows deleted.

SQL> commit;

Commit complete.

Now :

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 23 Bytes = 188416
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 20 Bytes = 163840
after full block delete ,the free blocks increased again.

SQL> alter index ind_t1 coalesce;

Index altered.

after coalesc , nothing changed:

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 23 Bytes = 188416
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 20 Bytes = 163840

but rebuild:
SQL> alter index ind_t1 rebuild;

Index altered.

FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 1 Bytes = 8192
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 0 Bytes = 0

Full Blocks = 21 Bytes = 172032

what i want to ask, why the free blocks increased after coalesce? Can coalesce really resolve tx:index contention? If coalesce will lock table?  

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jan 22 2010 - 06:00:09 CST