5 Replies Latest reply: Oct 30, 2012 2:21 AM by 670114 RSS

    Need RAC Tuning - Query

    670114
      Hi,

      I have been facing a problem for the past two days. We have a two node RAC and the DB was working fine until Monday.

      Suddenly we came across a lots of Deadlocks with two tables and their was repeated ORA-00060 error in the Alert. And when we analyzed we got the tables and query from the trace. We are not sure about the cause of deadlock because all the columns involved in the query are properly indexes and all foreign keys are also indexes.

      When we try the same scenario locally it is working fine but when it get simultaneous hits say like 10 users the query hangs. This was working fine past week.

      Below is the observation in ADDM:

      FINDING 1: 100% impact (31499 seconds)
      --------------------------------------
      SQL statements were found waiting for row lock waits.

      RECOMMENDATION 1: Application Analysis, 96% benefit (30314 seconds)
      ACTION: Significant row contention was detected in the TABLE
      "NIC_GS.T_INSURED_LIST" with object id 270988. Trace the cause of row
      contention in the application logic using the given blocked SQL.
      RELEVANT OBJECT: database object with id 270988
      RATIONALE: The SQL statement with SQL_ID "41k7uj9f36tv0" was blocked on
      row locks.
      RELEVANT OBJECT: SQL statement with SQL_ID 41k7uj9f36tv0
      update T_INSURED_LIST set UPDATE_TIME=:1, POLICY_ID=:2,
      SUM_INSURED=:3, INSERT_TIME=:4, FIELD12=:5, FIELD13=:6 where
      INSURED_ID=:7
      RATIONALE: The SQL statement with SQL_ID "9dg72r8w5nap6" was blocked on
      row locks.
      RELEVANT OBJECT: SQL statement with SQL_ID 9dg72r8w5nap6
      update T_INSURED_LIST set UPDATE_TIME=:1, POLICY_ID=:2,
      INSERT_TIME=:3, FIELD12=:4, FIELD13=:5 where INSURED_ID=:6
      RATIONALE: The SQL statement with SQL_ID "d6n84rch33cbq" was blocked on
      row locks.
      RELEVANT OBJECT: SQL statement with SQL_ID d6n84rch33cbq
      update T_INSURED_LIST set sum_insured=:1 where insured_id=:2

      FINDING 2: 98% impact (31024 seconds)
      -------------------------------------
      SQL statements consuming significant database time were found.

      RECOMMENDATION 1: SQL Tuning, 53% benefit (16830 seconds)
      ACTION: Investigate the SQL statement with SQL_ID "41k7uj9f36tv0" for
      possible performance improvements.
      RELEVANT OBJECT: SQL statement with SQL_ID 41k7uj9f36tv0 and
      PLAN_HASH 610793629
      update T_INSURED_LIST set UPDATE_TIME=:1, POLICY_ID=:2,
      SUM_INSURED=:3, INSERT_TIME=:4, FIELD12=:5, FIELD13=:6 where
      INSURED_ID=:7
      RATIONALE: SQL statement with SQL_ID "41k7uj9f36tv0" was executed 63
      times and had an average elapsed time of 267 seconds.
      RATIONALE: Waiting for event "enq: TX - row lock contention" in wait
      class "Application" accounted for 99% of the database time spent in
      processing the SQL statement with SQL_ID "41k7uj9f36tv0".

      Please suggest....
        • 1. Re: Need RAC Tuning - Query
          berx
          can you please provide some infos about your deadlock?
          A deadlock graph similar (in size) to the one in this note would be fine: http://jonathanlewis.wordpress.com/2011/04/08/deadlock/ (I don't claim this is the issue you hit, it's just the first example I had at hand).

          It would be handy if you can provide the 'create table' and 'create index' statements to be checked; also the 'create tablespace' might be of any interest.

          Which version and OS are you running?
          Was the DB migrated from previous versions?

          I hope with these informations we can progress.
          Martin
          • 2. Re: Need RAC Tuning - Query
            670114
            Hi,

            Thanks for your response..

            We have AIX server with oracle 10.2.0.4.

            We have narrowed down the issue : The table contains multiple insurance products and the problem is not occurring all the products but in only one product.

            I have been attaching the full trace of Deadlock.

            ----------enqueue 0x7000002cf73bff0------------------------
            lock version : 37
            Owner node : 0
            grant_level : KJUSERNL
            req_level : KJUSEREX
            bast_level : KJUSERNL
            notify_func : 0
            resp : 7000002aa711890
            procp : 7000002ca743c90
            pid : 4460708
            proc version : 0
            oprocp : 0
            opid : 0
            group lock owner : 7000002c84f0398
            possible pid : 4460708
            xid : 101E-01EE-00000002
            dd_time : 2.0 secs
            dd_count : 0
            timeout : 0.0 secs
            On_timer_q? : N
            On_dd_q? : Y
            lock_state : OPENING CONVERTING
            Open Options : KJUSERDEADLOCK
            Convert options : KJUSERGETVALUE
            History : 0x1495149a
            Msg_Seq : 0x0
            res_seq : 1
            valblk : 0x00000000000000000000000000000000 .
            DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
            possible owner[494.4460708] on resource TX-0087002D-000012DF
            Submitting asynchronized dump request [28]
            *** 2012-08-22 15:51:13.021
            kjddopr: skip converting lock 7000002c8a214d0 dd_cnt 1
            user session for deadlock lock 7000002cf73c698
            pid=242 serial=2 audsid=323336032 user: 103/Samp_GS
            O/S info: user: Samp, term: , ospid: 1234, machine: Samp-PEBAAP1A-AR
            program:
            Current SQL Statement:
            update T_POLICY_CT set UPDATE_TIME=:1, INSERT_TIME=:2, FIELD01=:3, FIELD02=:4, FIELD03=:5, FIELD04=:6, FIELD07=:7, FIELD66=:8 where POLICY_CT_ID=:9
            user session for deadlock lock 7000002cf73c548
            pid=244 serial=2 audsid=323336034 user: 103/Samp_GS
            O/S info: user: Samp, term: , ospid: 1234, machine: Samp-PEBAAP1A-AR
            program:
            Current SQL Statement:
            update T_INSURED_LIST set sum_insured=:1 where insured_id=:2
            user session for deadlock lock 7000002c8adf120
            pid=244 serial=2 audsid=323336034 user: 103/Samp_GS
            O/S info: user: Samp, term: , ospid: 1234, machine: Samp-PEBAAP1A-AR
            program:
            Current SQL Statement:
            update T_INSURED_LIST set sum_insured=:1 where insured_id=:2
            user session for deadlock lock 7000002c8a21380
            pid=242 serial=2 audsid=323336032 user: 103/Samp_GS
            O/S info: user: Samp, term: , ospid: 1234, machine: Samp-PEBAAP1A-AR
            program:
            Current SQL Statement:
            update T_POLICY_CT set UPDATE_TIME=:1, INSERT_TIME=:2, FIELD01=:3, FIELD02=:4, FIELD03=:5, FIELD04=:6, FIELD07=:7, FIELD66=:8 where POLICY_CT_ID=:9
            Global blockers dump start:---------------------------------
            DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x2d0025][0x4ff2],[TX]
            ----------resource 0x7000002aa711890----------------------
            resname : [0x2d0025][0x4ff2],[TX]
            Local node : 0
            dir_node : 0
            master_node : 0
            hv idx : 118
            hv last r.inc : 4
            current inc : 4
            hv status : 0
            hv master : 1
            open options : dd
            grant_bits : KJUSERNL KJUSEREX
            grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
            count : 1 0 0 0 0 1
            val_state : KJUSERVS_NOVALUE
            valblk : 0x00000000000000000000000000000000 .
            access_node : 0
            vbreq_state : 0
            state : x0
            resp : 7000002aa711890
            On Scan_q? : N
            Total accesses: 43
            Imm. accesses: 39
            Granted_locks : 1
            Cvting_locks : 1
            value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
            GRANTED_Q :
            lp 7000002cf73c548 gl KJUSEREX rp 7000002aa711890 [0x2d0025][0x4ff2],[TX]
            master 0 gl owner 7000002cf501998 possible pid 2551964 xid 100F-00F4-00000002 bast 0 rseq 5 mseq 0 history 0x14951495
            open opt KJUSERDEADLOCK
            CONVERT_Q:
            lp 7000002cf73c698 gl KJUSERNL rl KJUSEREX rp 7000002aa711890 [0x2d0025][0x4ff2],[TX]
            master 0 gl owner 7000002c8544830 possible pid 2588702 xid 100F-00F2-00000002 bast 0 rseq 5 mseq 0 history 0x1495149a
            convert opt KJUSERGETVALUE
            ----------enqueue 0x7000002cf73c548------------------------
            lock version : 109
            Owner node : 0
            grant_level : KJUSEREX
            req_level : KJUSEREX
            bast_level : KJUSERNL
            notify_func : 0
            resp : 7000002aa711890
            procp : 7000002ca6f5870
            pid : 2588702
            proc version : 0
            oprocp : 0
            opid : 0
            group lock owner : 7000002cf501998
            possible pid : 2551964
            xid : 100F-00F4-00000002
            dd_time : 0.0 secs
            dd_count : 0
            timeout : 0.0 secs
            On_timer_q? : N
            On_dd_q? : N
            lock_state : GRANTED
            Open Options : KJUSERDEADLOCK
            Convert options : KJUSERNOQUEUE
            History : 0x14951495
            Msg_Seq : 0x0
            res_seq : 5
            valblk : 0x00000000000000000000000000000000 .
            DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
            possible owner[244.2551964] on resource TX-002D0025-00004FF2
            Submitting asynchronized dump request [28]


            Please suggest....
            • 3. Re: Need RAC Tuning - Query
              RajeshBhatt
              The First thing, Involve Application team to identify what was changed since last week specially for this two tables. This might help you to check what was changed that caused this.
              • 4. Re: Need RAC Tuning - Query
                670114
                Hi,

                As per the application team their is no change in the code.

                Other products are working fine on the same tables.
                • 5. Re: Need RAC Tuning - Query
                  670114
                  Self Solutioned