13 Replies Latest reply: Jan 30, 2013 3:05 AM by user-1221 RSS

    Monitoring blocking Locks

    885820
      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
          dba_blockers can be used only in single-instance database
          • 2. Re: Monitoring blocking Locks
            885820
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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