Re: Doubt related do INITRANS
Date: Wed, 16 Dec 2020 21:20:17 +0000
Message-ID: <DM6PR01MB59292DA7D1E2B6B53B55F51FCEC50_at_DM6PR01MB5929.prod.exchangelabs.com>
The manual says one ITL for a table but the last time I checked via a block dump there are actually two. I do not know where you got the rule that an index should have the table number of ITL X 2 but that is not necessary. Only sessions that perform DML need to allocate an ITL. Readers just check the ITL to find the UNDO version of rows when necessary. Oracle will attempt to automatically add an ITL when needed. Pre-allocation guarantees N number of concurrent DML sessions can access the block and if you expect only 1 or 2 concurrent updaters will access a block concurrently then the default value of two for the index is adequate. Otherwise you should not need to pre-allocate more ITL's that you expect concurrent update sessions to access the block in either the table or its indexes.
Mark Powell
Hi,
I saw in the database and documentation that For tables INITRANS is equal to 1 and for indexes, 2.
Doubts are :
a.) for indexes, We always must use the number used by table * 2 ??
b.) Imagine that I have an initrans 10 for a table and 20 for indexes of this table (the table can have one or more than one ).
Regards
Database Administration
(313) 592-5148
From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Eriovaldo Andrietta <ecandrietta_at_gmail.com>
Sent: Wednesday, December 16, 2020 3:46 PM
To: ORACLE-L <oracle-l_at_freelists.org>
Subject: Doubt related do INITRANS
And an applicationI execute select for update for 1 line in different sessions , like this, with purpose to retrieve a number
select number_seq into seq from table_a where id = 10 for update;
commit;
It causes a lock. This table works like a database sequence.
Think in simultaneous connections and a lot of transactions.
When the commit is executed , the lock is released.
Doubts here are :
b1.) What is the advantage to configure initrans equal to 10 and indexes equal to 20 for this table ???
b2.) What is different in this case doing commit line by line if I had INITRANS equal 1 for table and 2 for indexes ?
Eriovaldo
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 16 2020 - 22:20:17 CET