1 2 3 Previous Next 33 Replies Latest reply: Aug 23, 2013 2:03 AM by yxes2013 RSS

    How to get the blocking sql

    yxes2013

      Hi all,

       

      I can display the sql_txt of the blocked sql using this:

       

      select distinct s1.username ||'@'|| s1.machine ||'(INST=' || s1.inst_id ||' SID= '|| s1.sid ||')

      is blocking '|| s2.username || '@' || s2.machine || ' (INST=' || s1.inst_id||' SID='||s2.sid ||')'    

      AS blocking_status, s2.program,s3.sql_id, s3.sql_text from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2, v$sql s3

          where s1.sid=l1.sid

          and s2.sid=l2.sid

          and l1.block=1

          and l2.request >0

          and l1.id1=l2.id1

          and l2.id2=l2.id2

          and s2.sql_id = s3.sql_id;

       

      But I can display the blocker sql

      Can you help me how to get the blocking sql_txt?

       

      I tried to revise the program which is:

       

      select s1.sid,s1.serial#,s1.inst_id,s1.SQL_ID from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2, v$sql s3

          where s1.sid=l1.sid

          and s2.sid=l2.sid

          and l1.block=1

          and l2.request >0

          and l1.id1=l2.id1

          and l2.id2=l2.id2

          and s2.sql_id = s3.sql_id;

       

       

       

             SID    SERIAL#    INST_ID SQL_ID

             ---------- ---------- ---------- -------------

              88        148          1

       

      But s1.sql_id is null. where do I get it?

       

       

      Thanks

        • 1. Re: How to get the blocking sql
          sybrand_b

          And again and again and again. This must be at least the 10th time you are asking this question.

          Why don't you just use Oracle provided utlockt.sql? At least this script works.

           

          Ah, I forgot, this script is documented and you don't read documentation.

          You rather prefer to bore everyone to death.

           

          ----------

          Sybrand Bakker

          Senior Oracle DBA

          • 2. Re: How to get the blocking sql
            JohnWatson

            It is impossible to get the blocking SQL, because SQL doesn't block anything: it is enqueues that block. All you can get is the session that is holding the enqueue. The enqueue might have been taken by any statement in the uncommitted transaction.

            --

            John Watson

            Oracle Certified Master DBA

            • 3. Re: How to get the blocking sql
              sb92075

              REDUNDANCY is the best way to teach idiots.

              REDUNDANCY is the best way to teach idiots.

              REDUNDANCY is the best way to teach idiots.

              REDUNDANCY is the best way to teach idiots.

              REDUNDANCY is the best way to teach idiots.

               

               

              sooner or later  the facts might sink in & be assimilated, but I will not hold me breath for that to occur.

              • 4. Re: How to get the blocking sql
                Mark D Powell

                John, has posted what I have seen posted by Oracle support.  Using the v$ dynamic performance views there is no way to find the actual blocking SQL.  Sometimes the blocking SQL is the last/current SQL statement executed by the blocking session, but it can be any DML statement executed by the blocking session as part of its transaction.

                 

                HTH -- Mark D Powell --

                • 5. Re: How to get the blocking sql
                  EdStevens

                  sb92075 wrote:

                   

                  REDUNDANCY is the best way to teach idiots.

                  REDUNDANCY is the best way to teach idiots.

                  REDUNDANCY is the best way to teach idiots.

                  REDUNDANCY is the best way to teach idiots.

                  REDUNDANCY is the best way to teach idiots.

                   

                   

                  sooner or later  the facts might sink in & be assimilated, but I will not hold me breath for that to occur.

                  I am reminded of a very off-color joke that I can't really tell here, but it involves a hunter going into the woods day after day and every day he is assaulted by a bear, but he keeps coming back.  After several days the bear finally says to the hunter, "Let me guess, it's not the hunting that you are coming here for ..."

                   

                  I don't think its the hunting that yxes is coming here for ...

                  • 6. Re: How to get the blocking sql
                    jgarry

                    I don't think he is a troll.  If he were a troll, he would have to be a lot better than he appears to be.  Now, exhibiting trollishness he is at times.  Here may be the discriminator: Does he really not see the error with l2.id2=l2.id2?

                    • 7. Re: How to get the blocking sql
                      Hoek

                      OP may correct me when I'm wrong, but I recall having read one thread, about a week ago, which left no doubt that he's in fact a she

                      No time to dig threads up now, will do later unless OP confirms before I do.

                      • 8. Re: How to get the blocking sql
                        jgarry

                        Well, that's different then.  She should google women in technology oaktableworld

                        • 9. Re: How to get the blocking sql
                          Hoek

                          Besides Oracle Database I am a guitar fanatic as well (but practiced waaaay too little the past years), but now I'm a bit confused...

                          Back in my fanatic years there was this T-shirt containing these lines, that said it all (imho):

                           

                          repetition works

                          repetition works

                          repetition works

                          repetition works

                          repetition works

                          repetition works

                          repetition works

                          repetition works

                           

                          Probably I'm just mixing things up...and I'm not even a DJ

                          • 10. Re: How to get the blocking sql
                            Nicolas.Gasparotto

                            jgarry wrote:

                             

                            I don't think he is a troll.  If he were a troll, he would have to be a lot better than he appears to be.  Now, exhibiting trollishness he is at times.  Here may be the discriminator: Does he really not see the error with l2.id2=l2.id2?

                            However, somehow he did :

                            Re: Kill the Blocker

                             

                            Nicolas.

                            • 11. Re: How to get the blocking sql
                              jgarry

                              lol, missed that.  Correct line easily googled.  Funniest if not a troll, people give disinformation and real system messed up.  Not that I would ever do that...

                              • 12. Re: How to get the blocking sql
                                yxes2013

                                I thank you all , even if you just made my post a chatting joke forum, you better use skype or chat-avenue

                                 

                                By the way I got good news . I found this post which answers my issue. > https://forums.oracle.com/thread/2474099

                                He got the query similar to mine but more enhanced to perfection. You should get a copy of this. It will help you a lot.

                                I know by your answers you do not know how to get the blocker query. Sometimes I got second thought if you are really gurus here.

                                 

                                But this script was able to do it.

                                 

                                SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')

                                || ' User '

                                ||s1.username

                                || '@'

                                || s1.machine

                                || ' ( SID= '

                                || s1.sid

                                || ' ) with the statement: '

                                || sqlt2.sql_text

                                ||' is blocking the SQL statement on '

                                || s2.username

                                || '@'

                                || s2.machine

                                || ' ( SID='

                                || s2.sid

                                || ' ) blocked SQL -> '

                                ||sqlt1.sql_text AS blocking_status

                                FROM Gv$lock l1,

                                Gv$session s1 ,

                                Gv$lock l2 ,

                                Gv$session s2 ,

                                Gv$sql sqlt1 ,

                                Gv$sql sqlt2

                                WHERE s1.sid =l1.sid

                                AND s2.sid =l2.sid

                                AND sqlt1.sql_id= s2.sql_id

                                AND sqlt2.sql_id= s1.prev_sql_id

                                AND l1.BLOCK =1

                                AND l2.request > 0

                                AND l1.id1 = l2.id1

                                AND l2.id2 = l2.id2

                                /


                                Note that it has AND l2.id2 = l2.id2 too Maybe it is need as a dummy condition to make it work?



                                Thanks

                                • 13. Re: How to get the blocking sql
                                  sb92075

                                  >But this script was able to do it.

                                  post SQL & results that show above is true

                                  • 14. Re: How to get the blocking sql
                                    yxes2013

                                    Ill post it if you tell me how much salary a month you get. Mine is USD1,500

                                    1 2 3 Previous Next