Data block contention

[magician]

Hi,<br><br>I have an Oracle7 (7.3.4.5) database.&nbsp;&nbsp;Upon examination of the v$waitstat table, I see some data block contention.&nbsp;&nbsp;I've upped initrans and pctfree for the tables, and db_block_buffers. The contention still exists.&nbsp;&nbsp;Could you please give me some tips on what causes and how to resolve this issue?<br><br>Thanks a lot.<br>

 

[carp]

Increasing initrans is a good start - it creates more transaction entries ('slots') when a block is allocated to the segment.&nbsp;&nbsp;However, it makes no difference if you change this value AFTER the block has been allocated.&nbsp;&nbsp;<br>You might try increasing maxtrans.&nbsp;&nbsp;This is the parameter that restricts how many slots can be created in the datablock.&nbsp;&nbsp;In the event that all slots are occupied when a transaction tries to access the block, if the current number of slots is less than maxtrans and there is sufficient free space within the block, another slot will be created within the block and the transaction may proceed.&nbsp;&nbsp;

 

[hp9000]

Thanks, but I think maxtrans is set to 255 which I think is the maximum...<br><br>

 

[Guest_imported]

This sounds like a similar problem to ours (see thread: Locking Problem - nothing in v$session.row_wait_obj#).<br>The problem occurs when a data block becomes close to full, so there is no room to create the extra 'transaction slots' (ITLs).<br>Increasing INITRANS and/or PCTFREE will reduce the likelihood of the problem arising again, but will not clear the existing problem. To ensure that data blocks are not close to full, you need to export, truncate, and re-import the data.