Skip to Main Content
  • Questions
  • How to set the INITTRANS value when Isolation level as Serializable

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Wang.

Asked: September 26, 2016 - 3:33 am UTC

Last updated: September 26, 2016 - 9:08 am UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

We current need to set the serializable isolation level, and will enable the ROWDEPENDENCIES when create the table like as below. My question is what is an optimal value for INITRANS? Or what should we consider when set this parameter? Thanks.

create table TAB1
(
  COL1           NUMBER(12),
  COL2           VARCHAR2(255 CHAR)
)
INITRANS <i><b>?</b></i> 
ROWDEPENDENCIES;


and Chris said...

Initrans relates to how many concurrent transactions you expect to have updating a block.

In most cases you can go with the default (2). If you're expecting to have lots of concurrent updates you may want to increase it - particularly if the table is small.

You may also want to increase it for indexes if you'll have lots of parallel DML. As the docs say:

If you have global indexes, a global index segment and global index blocks are shared by server processes of the same parallel DML statement. Even if the operations are not performed against the same row, the server processes can share the same index blocks. Each server transaction needs one transaction entry in the index block header before it can make changes to a block. Therefore, in the CREATE INDEX or ALTER INDEX statements, you should set INITRANS, the initial number of transactions allocated within each data block, to a large value, such as the maximum DOP against this index.

http://docs.oracle.com/database/121/VLDBG/GUID-6181F1F5-CCD0-4F95-B831-1045C5F8EC2D.htm#GUID-1FE0C03C-C70A-4109-B574-51A8ADC7DE86

Is this answer out of date? If it is, please let us know via a Comment