Oracle Object Statistics Test

In oracle, when a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching the block, it can easily tell that the row is locked from the block header. There is no need to queue up for some single resource like a lock manager.

ITL is the portion of the block header that contains information on locking. It is a simple data structure called “Interested Transaction List” (ITL), a linked list data structure that maintains information on transaction address and rowid. ITL contains several slots or place holders for transactions. 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.

During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.

Sometimes, transaction may not be able to find a free slot to place its lock information. This can occur because either (i) the block is so packed that the ITL cannot grow to create a free slot, or (ii) the MAXTRANS has already been reached.

To overcome this, do the following:

• Reorganizing the table by setting a high value of INITRANS will make sure that there are enough free slots in the ITL, and there will be minimal or no dynamic extension of the ITL.
• The other option is to make sure the data is less packed so that ITL can grow enough to accommodate the surges in ITL.

Buffer busy waits occur within Oracle when a task goes to fetch a data block, but it must wait because another task has control of the data block in the buffer.  A buffer busy wait is often caused by contention on an Oracle table header block because multiple tasks are waiting their turn to grab a freelist to place their new data rows.

This buffer busy wait condition happens for two reasons:

• Another session has the buffer block locked in a mode that is incompatible with the waiting session’s request.
• The block is being read into the buffer by another session, so the waiting session must wait for the block read to complete.

Ideally, value of this measure should be low. If this measure is high, you can reduce the buffer busy wait events by doing the following:

• By tuning the SQL to access this object’s rows with fewer block reads by adding indexes;
• Adding freelists to tables and indexes, or by adding this object to keep cache.

Physical reads/writes – whether direct or not – increase the processing overheads incurred by the database. Therefore, ideally, the value of these measures should be kept at a minimum at all times.

In the event of an unusually high number of Buffer busy waits, you might want to take a look at the values of these measures, to identify the bottleneck.