No question at this time
DBA Top 10
1 M. Cadot 22600
2 A. Kavsek 15200
3 M. Hidayathullah ... 11000
4 B. Vroman 8100
5 P. Wisse 6000
6 T. Boles 5200
7 J. Schnackenberg 3300
8 G. Lambregts 2200
8 P. Knibbs 2200
8 K. Pagadala 2200
About
DBA-Village
Download PLATO
The free tool for auditing and tuning your database
Version 55 now available
Sep 02, 2016
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48017
Total active users1850
Act. users last 24h5
Act. users last hour0
Registered user hits last week339
Registered user hits last month1183
Go up

enq:tm contention
Next thread: Error in Recover Database ORA-17502: ksfdcre:4 Failed to create file +FRA ORA-15347: logical block s
Prev thread: fixed objects stats

Message Score Author Date
Hi Guys I have a database that keeps on having ...... Tso P Mar 17, 2017, 10:34
Dear Tso, Please go through following mos note ...... Mirza Hidayathullah Baig Mar 17, 2017, 17:13
Hello Tso, it seems that you have already done ...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Bruno Vroman Mar 19, 2017, 19:38

Follow up by mail Click here


Subject: enq:tm contention
Author: Tso P, South Africa
Date: Mar 17, 2017, 10:34, 155 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, 155 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, 153 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