enq:tm contention
Follow up by mail Click here


Subject: enq:tm contention
Author: Tso P, South Africa
Date: Mar 17, 2017, 10:34, 216 days ago
Os info: rhel5
Oracle info: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Message: Hi Guys

I have a database that keeps on having blocking sessions that are preventing users to do their work.

Upon investigation I realised that the database spend 87% on enq: TM contention.

At some point the bone of contention was an index.


Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: TM - contention 157 81,040 516176 87.57 Application
DB CPU 6,151 6.65
enq: TX - row lock contention 31 4,284 138177 4.63 Application
db file sequential read 397,043 764 2 0.83 User I/O
DBMS_LDAP: LDAP operation 3,895 140 36 0.15 Other


After googling most of the docs talks about foreign keys that are not indexed...

I ran the script to check tables and columns that need indexes...

But this table that its index seemed to be a bone of contention does not appear...

When I check segments by row lock waits all are indexes, segments by buffer busy waits its also indexes.

Thanks in advance...

Please help!!!
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: enq:tm contention
Author: Mirza Hidayathullah Baig, United Kingdom
Date: Mar 17, 2017, 17:13, 215 days ago
Message: Dear Tso,

Please go through following mos note and link, you may get some idea on enq: TM contention.

Increase in 'enq: TM - contention' Wait Event Contention (11g and higher) (Doc ID 1343365.1)

https://support.oracle.com/epmos/faces/CommunityDisplay?resultUrl=https%3A%2F%2Fcommunity.oracle.com%2Fthread%2F3174016&_afrLoop=549198939671198&resultTitle=enq%3A+TM-contention&commId=3174016&displayIndex=2&_afrWindowMode=0&_adf.ctrl-state=15rrjurgqw_224

Regards
Mirza
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: enq:tm contention
Author: Bruno Vroman, Belgium
Date: Mar 19, 2017, 19:38, 213 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hello Tso,

it seems that you have already done most of the work...

  I ran the script to check tables and columns that need indexes... But this table that its index seemed to be a bone of contention does not appear...

Well, what does the script do? Presumably look for FK in your database that are not indexed on the FK side
(example: parent( id number primary key, ... ) and child( ..., parent_id number FK references parent(id), ...- with no index on child( parent_id ))

What you have identified as the source of contention does not appear in the list, very well... But is the list empty?
Please create the missing FK indexes and maybe you will realize that this has indeed fixed the situation...

Best regards,

Bruno Vroman.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here