Sorry, hit send too soon.
Also, ITLs are relevant to concurrent transactions acting on a single
block. So, ITLs are consumed at the block level when a transaction needs
to make a change to a specific block. If your transactions are all acting
on rows in the same block, you need enough ITLs to satisfy that. But if
you have transactions acting on many different blocks, you may be fine with
a relatively small number of ITLs.
Finally, unless your system is suffering from ITL waits, there is no point
in changing the number of ITLs.
-Mark
On Tue, May 11, 2021 at 1:24 PM Mark J. Bobak <mark@xxxxxxxxx> wrote:
I echo what Mark said, but I'll add another point:
How large is your row? How many rows per block? It makes no sense to
literally waste space allocating more ITLs than you have rows per block.
If they are large rows, and you have an 8k block size, you may not have 50
rows per block.
-Mark
On Tue, May 11, 2021 at 1:02 PM Powell, Mark <mark.powell2@xxxxxxx> wrote:
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
