Forum Stats

  • 3,814,940 Users
  • 2,258,933 Discussions
  • 7,892,892 Comments

Discussions

Categories

enq: TM - contention

User_3YG3J
User_3YG3J Member Posts: 76 Blue Ribbon
edited Jun 30, 2015 11:54AM in General Database Discussions

Hello Everyone,

Just One day before one Developer told me that my application hangs too much. So when it hangs next time I queried v$session and in the event I found 'enq: TM - contention'. So I google this event and I found that there is a locking issue of table. So making an index in the foreign key column will solve your problem and problem is solved.

But I want to know How index comes into the picture ? What index actually doing that cause my issue to be resolved.

Please help me out to understand this.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • SUPRIYO DEY
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited Jun 30, 2015 5:47AM

    Which version you are in. It happens with foreign key in 11g.

    · Share on TwitterShare on Facebook
  • scapy
    scapy Member Posts: 41
    edited Jun 30, 2015 5:56AM

    Hi,

    For know what FK is not indexed, run the query bellow:

    SELECT * FROM (

    SELECT c.owner, c.table_name, cc.column_name, cc.position column_position

    FROM   dba_constraints c, dba_cons_columns cc

    WHERE  c.constraint_name = cc.constraint_name

    AND    c.constraint_type = 'R'

    AND c.owner like 'owner'

    MINUS

    SELECT i.owner, i.table_name, ic.column_name, ic.column_position

    FROM   dba_indexes i, dba_ind_columns ic

    WHERE  i.index_name = ic.index_name

    AND i.owner like 'owner'

    )

    ORDER BY owner,table_name, column_position;

    Hope this help you.

    Regards

    · Share on TwitterShare on Facebook
  • SUPRIYO DEY
    SUPRIYO DEY Member Posts: 2,127 Silver Trophy
    edited Jun 30, 2015 6:02AM

    HI please follow this doc

    1343365.1

    · Share on TwitterShare on Facebook
  • Geert Gruwez
    Geert Gruwez Member Posts: 1,011
    edited Jun 30, 2015 10:58AM

    when the table is empty this happens too

    so don't just skip the empty tables ... also add an index on those tables showing the enq lock

    you should be getting an TM03 in P1 for this

    · Share on TwitterShare on Facebook
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy
    edited Jun 30, 2015 11:54AM

    Hi,

    Try to get the 'p2' parameter of the enqueue wait event. It's the object_id of the locked object.

    If you have diagnostic pack, following blocking sessions in ASH may help.

    Look at foreign keys where parent is deleted (of referenced column udpated) and you have no index starting with the foreign key columns. The locking follows the cascade (delete or setnull - even when the set null is not on referenced column)

    Non indexed foreign key is the most frequent reason for TM Share lock, but you should check that there is no concurrent DDL occurring (create index for example)

    Regards,

    Franck.

    · Share on TwitterShare on Facebook
This discussion has been closed.