Systems Engineering and RDBMS

Calculating Rows per Block in Oracle

Posted by decipherinfosys on March 27, 2008

Here is a simple spreadsheet displaying how to calculate total number of rows per block in Oracle. The example takes a segment and does the calculation for it. The reason for doing this calculation at a client site was to show that the high value of ITL (Interested Transaction List) was not needed and the values needed to be re-configured as not only was it a waste of space, it would lead to performance issues as well since the data that could be fit in lesser number of blocks was now more spread out.

itl.jpg

As you can see above, every ITL slot occupies 24 bytes and since this client was using 40 for initrans and 40 for pctfree, considering the average row length for the segment, only 29 rows can be added to each data block which meant that 11 of the created ITL slots will never ever get used. One should carefully evaluate the waits in the system and see how one can then minimize those waits. So, if ITL waits have a high number in the system, then you can play with the initrans parameter and pctfree (maxtrans is not even a concern beginning Oracle 10g) but don’t go overboard and do not apply the same yardstick to every table in the system. Carefully evaluate which tables fall into the heavy transactional tables category and then adjust the settings and observe the waits and then fine tune again. Do remember that when changing these settings, these apply to the new blocks only – for the existing ones, you need to re-load the data.

If you want to read up more on ITL, here are some excellent articles written by Arup Nanda on this topic:

http://www.rampant-books.com/art_nanda_interested_tarnsaction_list_itl.htm

http://www.proligence.com/itl_waits_demystified.html

In-correct setting of initrans can also cause deadlocks which are represented by the X <–> S lock modes in the deadlock graph. Here is an excellent explanation by Tom Kyte of such a scenario.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: