Re: question on initrans

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_at_dxc.com> 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_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
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l