3 Replies Latest reply: Feb 19, 2013 3:08 PM by jgarry RSS

    concurrency wait on an insert statement

    Neo-b
      Hello All,

      I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

      I have a table with unique index, and the application is doing inserts/updates into this table.
      Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
      All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.

      After this half minute everything went back to normal.

      What could be the reason and how can I investigate it ?

      Regards,
        • 1. Re: concurrency wait on an insert statement
          sb92075
          Neo-b wrote:
          Hello All,

          I am running Oracle RAC 2 nodes 11g R2 on AIX 7.1

          I have a table with unique index, and the application is doing inserts/updates into this table.
          Suddenly and for about half a minute I faced a high concurrency waits on all the processes running these inserts for one node. I saw this high concurrency wait in the top activity screen of the OEM only on one of the nodes. knowing that the processes doing these inserts are running on both nodes.
          All what I have that in this half minute I see high concurrency wait in OEM top activity screen related to this insert statement and when I clicked on the insert I found high "enq: TX - index contention". Again this was only on one node.

          After this half minute everything went back to normal.

          What could be the reason and how can I investigate it ?

          Regards,
          I bet that the INDEX contains a SEQUENCE.
          • 2. Re: concurrency wait on an insert statement
            Neo-b
            Thanks,

            Sorry i missed something the insert will fire a trigger that insert on another table, and this other table yes it has a sequence cache 1000, order.

            So my scenario is like below:

            I am facing high index contention on insert into table A. (Table A does not have a sequence)
            on Table A there is a trigger that fires and insert into table B. Table B has a unique index on a sequence with cache 1000 and order.

            But i am not seeing high contention on insert into table B that have a sequence.

            can the sequence of table B affects the performance (index contention) of insert into table A? since there is a trigger that fires after inserting into table A?

            Regards,