12 Replies Latest reply: Jan 21, 2013 10:24 AM by Nikolay Savvinov RSS

    how to troubleshoot contention

    user13312943
      Hi,
      I am addressing following situation

      Session 1:-

      10:00 AM EST statement 1: Acquire lock on table 1
      10:01 AM EST statement 2: select * from table2;

      Session 2:

      10:02 AM EST statement 1: Trying to acquire lock on table 1 and hangs

      Under this situation, I would like to find out which sql statement (In our case Session 1, Statement 1)
      was actually holding the lock along with the program name, osuser, username
      and which statement is waiting for the lock to be released (session 2, statement 1)

      Is it possible to find this information? Oracle database version : 10.2.0.4 and above

      Thank you

      Sarayu
        • 1. Re: how to troubleshoot contention
          asahide
          Hi,

          V$LOCK(LMODE & REQUEST)?

          Regards,
          • 2. Re: how to troubleshoot contention
            Girish Sharma
            Is it possible to find this information? Oracle database version : 10.2.0.4 and above
            Yes. Try to run below SQL and see who is blocking whom :
            column blocker format a25
            column blockee format a25
            column sid_serial format a10
            
            select
             (select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
             a.sid || ', ' ||
             (select serial# from v$session where sid=a.sid) sid_serial,
             ' is blocking ',
             (select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
             b.sid || ', ' ||
             (select serial# from v$session where sid=b.sid) sid_serial
             from v$lock a, v$lock b
             where a.block = 1
               and b.request > 0
               and a.id1 = b.id1
               and a.id2 = b.id2;
            Regards
            Girish Sharma
            • 3. Re: how to troubleshoot contention
              Nikolay Savvinov
              Hi Girish,

              that's not quite what the OP is looking for. If I understand correctly, he wants to know the statement responsible for the lock, and the statement requesting the lock. Getting latter is straightforward, because it's always the current sql_id for the blocked session, getting former is not (I'm not even sure if it's possible at all).

              Best regards,
              Nikolay
              • 4. Re: how to troubleshoot contention
                Nikolay Savvinov
                Hi,

                if you have the Diagnostic Pack License, then you can use V$ACTIVE_SESSION_HISTORY to browse through recent statements run by the blocking session (which you can easily identify using DBA_BLOCKERS and/or V$LOCK, e.g. http://avdeo.com/2008/06/21/identifying-locks-on-objects-using-vlocks/). For less recent statements you can use DBA_HIST_ACTIVE_SESS_HISTORY or log miner.

                Best regards,
                Nikolay
                • 5. Re: how to troubleshoot contention
                  Girish Sharma
                  Hi Nikolay,

                  Yes, OP is looking for Locking SQL and I got clue from below link : (v$open_cursor)
                  http://jonathanlewis.wordpress.com/2009/04/19/locking-sql/

                  See this demo :
                  Session 1: User Scott :
                  select * from emp where empno=7369 for update;
                  
                  Session 2: User Scott :
                  select * from emp where empno=7369 for update;
                  
                  ... Its blocked.
                  
                  Session 3:
                  I ran my above query and got blocker's SID=5 and then said :
                  SQL> select sql_id,sql_text from v$open_cursor where sid=5 and sql_text like '%emp%';
                  
                  SQL_ID        SQL_TEXT
                  ------------- ------------------------------------------------------------
                  2fwkw323b9d5q select * from emp where empno=7369 for update
                  
                  SQL>
                  
                  Because I know that blockee is going to acquire lock on emp table, so I said sql_text like '%emp%'.
                  Regards
                  Girish Sharma
                  • 6. Re: how to troubleshoot contention
                    Nikolay Savvinov
                    Girish,

                    thanks for the link, but as Jonathan says himself in the post: "whatever approach you take is at best intelligent guesswork, and it’s liable to error".

                    Also, your demo takes the easiest case possible: session 1 executes DML and holds the lock, session 2 requests the DML and cannot obtain the lock, and session 3 runs SQL to see who's locking whom. That's not what the OP is after. As he showed in his original post, he's mostly interested in scenarios when session 1 obtains a lock, and then runs other statements (maybe hundreds of them, maybe even obtaining other locks) while still holding the original lock. Then at some moment session 2 tries to acquire the lock and has to wait -- and your task is to determine the statement responsible (which can easily be 1000 statements before the last one).


                    Best regards,
                    Nikolay
                    • 7. Re: how to troubleshoot contention
                      Girish Sharma
                      Nikolay,

                      Session 1: SID=5
                      SQL> update emp set ename='ALLEN' where empno=7499;
                      
                      1 row updated.
                      
                      SQL> update emp set ename='SMITH' where empno=7369;
                      
                      1 row updated.
                      Session 2:
                      SQL> update emp set ename='ALLEN' where empno=7499;
                      ....hang
                      Session 3:
                      SQL> select sql_id,sql_text from v$open_cursor where sid=5 and sql_text like '%emp%';
                      
                      SQL_ID        SQL_TEXT
                      ------------- ------------------------------------------------------------
                      5458cjv7wsccz update emp set ename='SMITH' where empno=7369
                      
                      SQL>
                      But, I am not getting in what business need it is requires that which Session 1's sql is blocking Session 2. When it is identified that Session 1(SID=5) is blocking Session2 (SID=70) in mine example; then simple either ask blocker to end the transaction or kill the session. I am not getting what business need is going to suffer to know whether it is :
                      update emp set ename='SMITH' where empno=7369
                      or
                      update emp set ename='ALLEN' where empno=7499
                      or
                      any PL/SQL
                      or
                      ...whatever you are doing your session is blocking to X user, please end the transaction or check your code.

                      I have learnt that if something which is not possible or tough to do in Oracle, then I think its business need is good candidate for question.

                      Regards
                      Girish Sharma
                      • 8. Re: how to troubleshoot contention
                        Nikolay Savvinov
                        Girish,

                        >
                        I am not getting what business need is going to suffer to know whether it is :
                        update emp set ename='SMITH' where empno=7369
                        or
                        update emp set ename='ALLEN' where empno=7499
                        or
                        any PL/SQL
                        or
                        ...whatever you are doing your session is blocking to X user, please end the transaction or check your code.
                        A database may contain millions of lines of code -- which one needs to be checked?

                        Best regards,
                        Nikolay
                        • 9. Re: how to troubleshoot contention
                          Girish Sharma
                          A database may contain millions of lines of code -- which one needs to be checked?
                          When we are able to find the blocker's session ID then I think what ever code or PL/SQL he/she is executing, don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone; whether it is single line or millions of line ? Upto what extend Oracle will keep track idiot programmer's flaws and why ? In above example, I just executed two transactions... first transaction is running and I executed second without ending first one. This is bad programming and Oracle teaches how to write database code.

                          Smart technology makes people smart too... :)

                          Regards
                          Girish Sharma
                          • 10. Re: how to troubleshoot contention
                            Nikolay Savvinov
                            Hi,
                            Girish Sharma wrote:
                            A database may contain millions of lines of code -- which one needs to be checked?
                            When we are able to find the blocker's session ID then I think what ever code or PL/SQL he/she is executing,
                            or was executing 1 minute ago. Or 10 minutes ago. Or an hour ago. In a real-world application, when everything is run through a connection pool, how are you going to determine that?
                            don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;
                            No, I don't think that. In fact, I'm quite sure of the opposite. A user doesn't even have to know what "DBMS", "session" or "lock" means -- in 9 cases out of 10, this has nothing to do with his job. He just presses buttons and clicks on controls. He lives in a different world and operates with different concepts (e.g. "open a new document" instead of "open a new transaction by inserting into DOCUMENT table").
                            whether it is single line or millions of line ? Upto what extend Oracle will keep track idiot programmer's flaws and why ?
                            So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging, and it's not clear whether or not the holding session can be safely killed (because you have no idea what it was doing), you cannot make any reasonable assumption regarding chances of the application closing the transaction naturally within reasonable time, and you cannot tell the "idiot" developer which part of the codebase he has to work on to avoid the situation in the future.
                            In above example, I just executed two transactions... first transaction is running and I executed second without ending first one. This is bad programming and Oracle teaches how to write database code.
                            No one means for the transaction to hang about unclosed for hours -- but the code could have gone down some unexpected path or get trapped inside an infinite loop. As a DBA, it is still your job to help resolve the situation.

                            Best regards,
                            Nikolay
                            • 11. Re: how to troubleshoot contention
                              Girish Sharma
                              There are three persons, Application user, developer and DBA. When I said "don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;" it means, I was pointing to developer who write database code; one who always forgets to close the cursor or don't commit/rollback the transaction.
                              So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging
                              So DBA is telling that this user (database user) is blocker. This user did not closed the transaction by above sql please.
                              which part of the codebase he has to work on to avoid the situation in the future.
                              very simple either commit or rollback and forgot to close the cursor.

                              I am not sure, on which point we are not going to agree, but my simple assertions is idiot developers write database code and either DBA or Oracle stands culprit, while Oracle/DBA is victim here. Application user says its DBA's mistake, developers says its DBA's mistake, while I says neither I (DBA) developed application nor I written PL/SQL. As a DBA, when I (DBA) am saying that this user is blocking other session then as a DBA, Am I not doing my task please ?

                              Regards
                              Girish Sharma
                              • 12. Re: how to troubleshoot contention
                                Nikolay Savvinov
                                Girish Sharma wrote:
                                There are three persons, Application user, developer and DBA. When I said "don't you think that this user needs to learn basics of DBMS because his/her code is blocking someone;" it means, I was pointing to developer who write database code; one who always forgets to close the cursor or don't commit/rollback the transaction.
                                So that's your answer -- you're going to call the developer an idiot and claim that the problem is solved? But the application is still hanging
                                So DBA is telling that this user (database user) is blocker. This user did not closed the transaction by above sql please.
                                Allowing users to access the database directly is a bad practice. Normally, users access the database via an application layer. And normally, the application is using a connection pool -- i.e. there are tens or hundreds of sessions with the same username open. So in real life, you're going to see something like this in your session browser:
                                SID        SERIAL#         USERNAME    STATUS  MACHINE                      SQL_ID        SQL_TEXT
                                
                                1101         15612         APP_USER    INACTIVE  grid55.mycompany.com     axy56aj5idc      select blah from blah where blah   --- this is the offending session
                                56           910           APP_USER    ACTIVE    grid50.mycompany.com     asyq63a51d0      begin pkg_data_load.blah   
                                <100 more entries like that>
                                503          815           APP_USER    INACTIVE  grid32.mycompany.com     frpj02jsz0as     select something from somewhere
                                How will you proceed?
                                which part of the codebase he has to work on to avoid the situation in the future.
                                very simple either commit or rollback and forgot to close the cursor.
                                No, it's not very simple. Good database development practices forbid developer to commit before the business transaction is completed (the atomicity principle). And the logic for the business transaction can be quite complex. And somewhere along the way, the code can get stuck through no fault of the developer. For example, I saw a case when the database session used to hang while trying to parse a statement (because of an internal optimizer bug). But before it got to this place, it could have acquired many locks, intending to release them soon. As result, the session hangs itself, and also hangs a bunch of other sessions competing for the held resources. When something like this happen, the DBA must provide as much information as possible to help pinpoint the exact place where the problem occurred, rather than give general advice like "always commit or roll back".


                                Best regards,
                                Nikolay