TL;DR;
-- 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 INI_TRANS
.
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
MOVE
operation (into the same tablespace) so that they pick up the new setting. - During the
MOVE
all indexes on this table will becomeUNUSABLE
. 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;
Note the 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.