×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Data block contention

Data block contention

Data block contention

(OP)
Hi,

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

Thanks a lot.

RE: Data block contention

Increasing initrans is a good start - it creates more transaction entries ('slots') when a block is allocated to the segment.  However, it makes no difference if you change this value AFTER the block has been allocated.  
You might try increasing maxtrans.  This is the parameter that restricts how many slots can be created in the datablock.  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.  

RE: Data block contention

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

RE: Data block contention

This sounds like a similar problem to ours (see thread: Locking Problem - nothing in v$session.row_wait_obj#).
The problem occurs when a data block becomes close to full, so there is no room to create the extra 'transaction slots' (ITLs).
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close