-- increase INITRANS for the table alter table myTable initrans 4; -- Rebuild the table including indexes alter table myTable move update indexes;
Why to change
The ITL (interested transactions list) is a list that is used during DML to organize which session does currently changes to an oracle block.
INI_TRANS is the guaranteed minimum number of slots for concurrent transactions. The bigger
INI_TRANS is the more space is reserved in the header of an oracle block. So do not set it to a high value and not for all tables. Usually setting it to 2 is enough.
Only for tables (blocks) that are very full and where the same block is accessed from different sessions concurrently this should be increased. A strong indicator would be the wait event “enq: TX – allocate ITL entry“. If this wait happens frequently, then you want to increase the available ITL slots by increasing
See this post by Arup Nanda for an excellent description about ITLs and ITL waits.
How to change
3 things need to be done.
- The table parameter needs to be changed. This setting does only influence new table extents, it will not modify any existing extents.
- The existing extents need to be changed by a
MOVEoperation (into the same tablespace) so that they pick up the new setting.
- During the
MOVEall indexes on this table will become
UNUSABLE. So they have to be rebuild.
-- increase INITRANS for the table alter table myTable initrans 4;
Since Oracle 12.2 step 2 and 3 can be done with a single command.
-- Rebuild the table including indexes alter table myTable move update indexes;
UPDATE INDEXES addition to the alter table command.
In older DB versions it had to be done step by step
-- Rebuild the table including indexes alter table myTable move; -- check for unusable indexes SELECT table_name, index_name, tablespace_name FROM user_indexes WHERE status = 'UNUSABLE' order by table_name, index_name; -- prepare a index rebuild statement SELECT 'alter index '||index_name||' rebuild;' FROM user_indexes WHERE status = 'UNUSABLE'; and table_name = 'MYTABLE'; --> grab the result and run the index rebuild commands -- rebuild indexes alter index MYTABLE_FK04_IX rebuild; alter index MYTABLE_FK05_IX rebuild; alter index MYTABLES_PK rebuild; alter index MYTABLE_UK01 rebuild; alter index MYTABLE_UK02 rebuild; alter index MYTABLE_FK01_IX rebuild; alter index MYTABLE_FK02_IX rebuild; alter index MYTABLE_FK03_IX rebuild; alter index MYTABLE_FK06_IX rebuild; alter index MYTABLE_FK07_IX rebuild; alter index MYTABLE_FK08_IX rebuild; alter index MYTABLE_FK09_IX rebuild; alter index MYTABLE_FK10_IX rebuild; alter index MYTABLE_FK11_IX rebuild;
Rebuilding tables in 18c is so easy now!Sven Weller
I didn’t mention that you could
MOVE the table as an
online operation too. However I would avoid having other active sessions working with the table at the same time.