Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I am wondering about a weird deadlock and after checking all other components I am considering it to be a INITRANS problem in Oracle 11.2.

Now three questions arose:

  • Is there some way of analyzing how full the blocks are?

  • My database reached its max tablespace size a while ago and started rejecting inserts, then I added another data file, but this only grows if nearly all the space is used up. My PCT_USED is 96%. Is my assumption correct that all data blocks will be totally stuffed and a INITRANS deadlock is likly?

  • Is there some way of seeing an INITRANS problem in a deadlock trace?

I'm happy about partial answers as well.

share|improve this question

1 Answer

up vote 1 down vote accepted

Initrans is most important for concurrent inserts in the same blocks of a table. You normally recognize this when there is contention for the block headers. As long as there is enough free space in the block, list can grow in the free space of the block. So if you know that for some tables you have many concurrent jobs inserting data in the same end of the table, it will help to set the INITRANS to a value similar to that of the number of concurrent jobs.

pct_used means that - in your case - as soon as a block gets 4% space free, that block will get to the free list to accept new rows.

pct_free means that as soon the free space in a block falls below pct_free, the block is no longer accepting new rows, in order to keep space for updates where rows can grow.

The table statistics show a statistic like AVG_SPACE, AVG_ROW_LEN.

share|improve this answer
Thanks, so your saying the my discribed scenario could be realistic? I don'T quite understand what you said about pct_used, but I might have to read about this first. – Franz Kafka Feb 8 '12 at 20:11
yes, there is certainly some kind of locking possible. If it's a deadlock depends on how the app works. – ik_zelf Feb 8 '12 at 20:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.