Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

enq: TM - contention while creating index

user3006396Jul 6 2012 — edited Jul 6 2012
Hi All,
I'm trying to create an index on a table with 10 million records. It got a primary key constraint and there are no foreign key's on any other tables.
When i tried to create index, within seconds i'm able to see below when queried...
select SID,EVENT,TOTAL_WAITS,TOTAL_TIMEOUTS,TIME_WAITED FROM v$session_event WHERE event='enq: TM - contention' and total_waits> 0 ;
SID EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED
399 enq: TM - contention 346 345 101316
544 enq: TM - contention 137 136 39892
862 enq: TM - contention 149 148 43485
968 enq: TM - contention 880 875 255780
985 enq: TM - contention 10 9 2842
999 enq: TM - contention 228 227 66662

I thought these events would be waiting for some time.. but the number keep on increasing as the time increases
I came to know that creating an index on foreign key constraint would solve this.. but unfortunately i don't see any foreign keys..

Will this be solved if I wait for long time ?
And the sql_text from v$sqlarea is shown as "insert into table(col1,col2...) values(:b1,:b2....);"
Can anyone please help me to solve this..

Thanks.
This post has been answered by Dom Brooks on Jul 6 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2012
Added on Jul 6 2012
10 comments
1,689 views