Re: question on initrans
Date: Tue, 11 May 2021 17:02:26 +0000
Message-ID: <BN6PR01MB2547E74E2D868421A58690A1CE539_at_BN6PR01MB2547.prod.exchangelabs.com>
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
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
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Pap <oracle.developer35_at_gmail.com>
Sent: Tuesday, May 11, 2021 12:47 PM
To: Oracle L <oracle-l_at_freelists.org>
Subject: question on initrans
Pap
--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 11 2021 - 19:02:26 CEST