I was reading about ITL mechanism and it was mentioned that Oracle
creates ITL entry for *every* updated row in a block. So if one
transaction updates 10 rows in the same block Oracle will create 10
transaction entries (ITL entries). Is it really true? Let's say we
have a table with very short row, just one column CHAR(1). In this
case there could be more than 1000 rows in 8K block. So to update all
rows in this block Oracle will need to create more than 1000
transaction entries in the same block. As each entry is 24 bytes there
won'be be enough space to do that. However test shows that update
works just fine.

On Thu, 11 Aug 2011 16:48:44 -0700, vsevolod afanassiev wrote:

ITL stands for "Interested Transaction List. ITL entries point to
transactions. Therefore, one will be created for every interested
transaction. If a transaction updates 10 rows in the block, it will only
have 1 ITL entry.



--
http://mgogala.byethost5.com

Thanks. Let's say there are 100 rows in a block and 50 got updated by
one transaction. Then single ITL entry
should contain information about 50 rows. How do you fit information
about 50 rows in 24 bytes?

http://avdeo.com/2008/06/16/interest...tion-list-itl/
Quote:
This variable portion of the data block contains slots for
transactions to put the locking information. When a row in the block
is locked for the first time, the transaction places a lock in one of
the slots with the rowid of the row that is locked. In other words,
the transaction makes it known that it is interested in the row (hence
the name “Interested Transaction List”). When the same transaction or
another one locks another row, the information is stored in another
slot, and so on.

On Aug 12, 5:19*am, vsevolod afanassiev
<vsevolod.afanass... (AT) gmail (DOT) com> wrote:
The quoted part is wrong, the row entry contains a lock byte that
points to the ITL slot, there is no ROWID stored in the ITL slot.
There is only a single ITL slot allocated for a single transaction
within a single block no matter how many rows of that block are
modified by that transaction. The lock byte of each row entry links
the row to the transaction.

You might want to read Arup's article on that topic where he
specifically covers that point: http://arup.blogspot.com/2011/01/mor...ion-lists.html

and this one may be also: http://arup.blogspot.com/2010/12/100...idnt-know.html

Hope this helps,
Randolf

VSE:

# I was reading about ITL mechanism and it was mentioned that Oracle
creates ITL entry for *every* updated row in a block.

....

Looks like you are getting information on that question.

Think about a select for update operation that locks a whole bunch of
rows in a bunch of different blocks.

Lots of work maintaining the ITL information in all those blocks eh?

Tom Kyte talks about locking is free in oracle but the cpu costs and
latching costs can be quite significant if one does not have a good
key to do the locking with. Plus redo entries to support it etc.

Thanks, I got it:
- There is one ITL engtry per transaction.
- Every row has lock byte that points to ITL entry of transaction that
locked this row.
Simple. Should be in the Concepts manual.

On Fri, 12 Aug 2011 17:54:47 -0700, vsevolod afanassiev wrote:

The big advantage of this mechanism is that it doesn't maintain in memory
queues so it's not limited by the memory size. Now, it's a moot point,
but when you install Oracle 6.0.36 on a mVAXII with 16MB RAM (yeah, you
read correctly, it's "M", not "G") memory is extremely precious. Also,
locks were allocated using system service, from something called "non-
paged memory pool", which was even smaller than 16MB. There were two
solutions for the memory problems with locking. The first was not to use
and to develop a different approach. That's what Oracle did. Sybase took
a different approach which included lock escalation. In other words,
under certain conditions, many locks on a table would convert into a
single table level lock, thus dramatically lowering the memory
consumption. Unfortunately, that approach also limits concurrency, which
is a problem.



--
http://mgogala.byethost5.com