No question at this time
DBA Top 10
1 M. Cadot 16900
2 A. Kavsek 15000
3 B. Vroman 10300
4 M. Hidayathullah ... 8400
5 T. Boles 4700
6 P. Wisse 4400
7 J. Schnackenberg 3500
8 K. Van Nieuwenhov... 2100
9 A. Hudspith 800
9 T. P 800
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 users48095
Total active users1706
Act. users last 24h8
Act. users last hour0
Registered user hits last week389
Registered user hits last month1490
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, 272 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, 272 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, 270 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