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?