This discussion is archived
5 Replies Latest reply: Oct 30, 2012 12:21 AM by 670114 RSS

Need RAC Tuning - Query

670114 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Self Solutioned

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points