This discussion is archived
13 Replies Latest reply: Jan 30, 2013 1:05 AM by user-1221 RSS

Monitoring blocking Locks

885820 Newbie
Currently Being Moderated
Hi

This question relates to monitoring blocking locks on a 9.2.0.5 2 node RAC

Origionally I have been monitoring bocking locks with every 5 mins using the following query:
"select * from dba_blockers"

I have recently implemented monitoring via grid control this is running an out of the box metric every 5 mins, the sql behind it is as follows:

"SELECT blocking_sid, num_blocked
FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
FROM ( SELECT l.id1, l.id2,
MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,
2, i.instance_name||'-'||l.sid, 0 )) blocking_sid,
SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
FROM gv$lock l, gv$instance i
WHERE ( l.block!= 0 OR l.request > 0 ) AND
l.inst_id = i.inst_id
GROUP BY l.id1, l.id2)
GROUP BY blocking_sid
ORDER BY num_blocked DESC)
WHERE num_blocked != 0 "

Now.. At one point today the alert using "select * from dba_blockers" fired where as the out of the box metric from gird control did not fire.... alert duration was around 5 - 10 mins

At first i simply assumed that this could have been a brief lock and due to both 5 min intervals being out of sync, the lock had shown and cleared before the grid control interval run.

now im a little more curious.

Is there any significan difference in what these 2 different SQL's will alert on, I was under the impression that DBA_BLOCKERS was simply querying a number of joined views, and Oracle had decided to use V$lock for their out of the box metric as it was more efficient.

Any comments welcome

Thanks
  • 1. Re: Monitoring blocking Locks
    Mihael Pro
    Currently Being Moderated
    dba_blockers can be used only in single-instance database
  • 2. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    so your saying that dba_blockers will not show conflicts across two instances?

    im trying to understand what the differences are between the two queries used for monitoring purposes, why an alert fired when querying dba_blockers but not (g)v$lock
  • 3. Re: Monitoring blocking Locks
    Mihael Pro
    Currently Being Moderated
    dba_blockers shows only local blockers, inside one instance
    for global locks you should use gv$lock, but your query is not correct
    or you can upgrade to 10g or 11g and use v$session.blocking_instance, blocking_sid
  • 4. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    what do you mean the query is not correct? This is the sql that runs from the out of the box grid control metric "blocking sessions count"

    Im trying to understand why the monitor did not fire with the query whilst a different monitor querying dba_blockers did.....
  • 5. Re: Monitoring blocking Locks
    Mihael Pro
    Currently Being Moderated
    what do you mean the query is not correct? This is the sql that runs from the out of the box grid control metric "blocking sessions count"
    if the query not shows blockers, how it can be correct ? Just try the following : on node 1 lock row, on node 2 lock the same row, run the query. You should get that session on node 2 is waiting for session on node 1.
  • 6. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    Sorry im not following you here, the following sql is the sql that the blocking sessions metric in grid control uses... this is the out of the box metric written by Oracle

    "SELECT blocking_sid, num_blocked
    FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
    FROM ( SELECT l.id1, l.id2,
    MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,
    2, i.instance_name||'-'||l.sid, 0 )) blocking_sid,
    SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
    FROM gv$lock l, gv$instance i
    WHERE ( l.block!= 0 OR l.request > 0 ) AND
    l.inst_id = i.inst_id
    GROUP BY l.id1, l.id2)
    GROUP BY blocking_sid
    ORDER BY num_blocked DESC)
    WHERE num_blocked != 0 "
    /
    are you suggesting that this is incorrect?
  • 7. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    Just to prove that the SQL is correct I have constrcuted a demo for you...

    SQL> create table t (a char(1));

    Table created.

    SQL> insert into t values ('z');

    1 row created.

    SQL> commit;


    in session 1 ---->

    select * from t where a='z' for update;

    ==================================================================

    in session 2 ---->

    update t set a='x' where a='z';

    (session simply hangs)

    ==================================================================

    in session 3 ------>


    SQL> select * from dba_blockers;

    HOLDING_SESSION
    ---------------
    48

    SQL>
    SQL> SELECT blocking_sid, num_blocked
    FROM ( SELECT blocking_sid, SUM(num_blocked) num_blocked
    FROM ( SELECT l.id1, l.id2, MAX(DECODE(l.block, 1, i.instance_name||'-'||l.sid,
    2, i.instance_name||'-'||l.sid, 0 )) blocking_sid,
    SUM(DECODE(l.request, 0, 0, 1 )) num_blocked
    FROM gv$lock l, gv$instance i
    WHERE ( l.block!= 0 OR l.request > 0 ) AND
    l.inst_id = i.inst_id
    GROUP BY l.id1, l.id2)
    GROUP BY blocking_sid
    ORDER BY num_blocked DESC)
    WHERE num_blocked != 0;
    2 3 4 5 6 7 8 9 10 11 12
    BLOCKING_SID NUM_BLOCKED
    --------------------------------------------------------- -----------"
    RAC1-48 1


    So back to the origional question,
    I am using both these queries from different monitors on my prod syystem, both running on 5 minute intervals, " select * from dba_blockers" fired where as the above query - querying gv$lock did not fire.

    Origionaly i assumed that the blocking lock may have simply lasted 3t0 seconds, and due the 5 minute monitor intervals of each metric not being in sync, ... "select * from dba_blockers" may have picked up the lock, then the query selecting from gv$lock ran 2 mins later by which time the lock had disapeared.

    -Can anyone suggest any other reasons other than this why one monitor (select * from dba_blockers) picked up the lock and the other (gv$lock) didnt?


    Thanks
  • 8. Re: Monitoring blocking Locks
    Mihael Pro
    Currently Being Moderated
    Okay. If you are sure that the query is correct, then check parallel_max_servers parameter. Queries to gv$ views uses PQ mechanism. If there no free slaves, query can not work.
  • 9. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    Yes this is fine. And as mentioned the query is not mine but oracles !

    sorry but I think Your not understanding the question im asking here. Please see my previous and origional posts.


    Does anyone else have any opinions
    Thanks
  • 10. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    To try and clarify,

    lets say this was a single instance database......Im trying to understanding if there are any situaions / different types of blocking sessions / locks that one query may pic up whilst the other one wont?

    i.e dba_blockers vs V$lock query
  • 11. Re: Monitoring blocking Locks
    Mihael Pro
    Currently Being Moderated
    In order to close this issue I will try to explain.
    View dba_lock, that is based on v$lock, has the following text :
    select
    ...
    decode(block,
    0, 'Not Blocking',  /* Not blocking any other processes */
    1, 'Blocking',      /* This lock blocks other processes */
    2, 'Global',        /* This lock is global, so we can't tell */
    to_char(block)) blocking_others
        from v$lock
    So, for global locks "v$lock.block=2" and no other info.
    The query that you mentioned here finds blocking_sid based on v$lock.block.
    This means that for single-instance "block=1" and it is ok. For global locks "block=2",
    and there is no guarantee that waiting sid will not have this value.

    Good luck.
  • 12. Re: Monitoring blocking Locks
    885820 Newbie
    Currently Being Moderated
    Thanks for the response but im sorry I don't follow what you are saying here,

    This isn't the question im asking, also the query above will check for both local and global locking.
  • 13. Re: Monitoring blocking Locks
    user-1221 Newbie
    Currently Being Moderated
    Hi,

    Use the following query to identify the blocking sessions in a RAC Environment;
    SELECT DECODE(G.INST_ID,1,'Orcl1',2,'Orcl2') INSTANCE, S.SID || ',' || S.SERIAL# "SidSerial",G.TYPE, S.USERNAME, S.PROCESS, DECODE(LMODE,0,'None',1,'Null',2,'Row-',3,'Row-X',4,'Share',5,'S/ROW',6,'Exclusive') LMODE,DECODE(REQUEST,0,'None',1,'Null',2,'Row-S',3,'Row-X',4,'Share',5,'S/ROW',6,'Exclusive')REQUEST, DECODE(REQUEST,0,'BLOCKER','WAITER') STATE FROM GV$GLOBAL_BLOCKED_LOCKS G, GV$SESSION S WHERE G.SID = S.SID AND G.INST_ID = S.INST_ID ORDER BY STATE;
    
    INSTA SidSerial    TY USERNAME     PROCESS      LMODE     REQUEST   STATE
    ----- ------------ -- ------------ ------------ --------- --------- -------
    Orcl1 769,7671     TX SCOTT         3216:1360    Exclusive None      BLOCKER
    Orcl2 227,3322     TX SCOTT         11456:12272  Exclusive None      BLOCKER
    Orcl1 627,33818    TX SCOTT         692:3324     None      Exclusive WAITER
    Orcl2 438,32457    TX SCOTT         3576:2092    None      Exclusive WAITER
    Orcl1 199,43113    TX SCOTT         284:844      None      Exclusive WAITER
    Orcl2 389,36502    TX SCOTT         472:2388     None      Exclusive WAITER
    Note: replace "Orcl1" & "Orcl2" with your instance names.

    You query is showing how many sessions are blocked by which session;
    BLOCKING_SID                                              NUM_BLOCKED
    --------------------------------------------------------- -----------
    orcl1-230                                                           3
    orcl1-383                                                           1
    orcl2-1049                                                          1

Legend

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