Unless you have 50 concurrent DML processes running against one table I see no
reason you would need 50 ITL's predefined. That would be over a 1000 bytes
made unavailable to hold row data. Even if you use parallel DML I do not
think I would want 50 sessions hitting the same blocks. I would want those
sessions going after different partitions and hence different blocks. What is
the block size? I am pretty much always using 8K so if 16k or 32K you might
need more than most shops but even so 50 seems high.
The only cost of setting initrans to a non-default value is the consumption of
block space.
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Pap <oracle.developer35@xxxxxxxxx>
Sent: Tuesday, May 11, 2021 12:47 PM
To: Oracle L <oracle-l@xxxxxxxxxxxxx>
Subject: question on initrans
This database was recently moved from HP to exadata X7 and the DB version also
migrated from 11.2.0.4 to 19.9.0.0.0. It's an OLTP database. In the past we saw
some contention and got a recommendation from Oracle to set INITRANS value of
certain objects(both table and indexes) to as high as 50. And now the team is
thinking to set the initrans value to ~10 for almost all the objects(~around
~200+ objects). Want to understand , what is the negative side of setting it to
this non default value considering this is an OLTP database exposed to many
concurrent DML/query and we encounter "buffer busy waits" many times?
Regards
Pap
