This discussion is archived
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 Newbie
Currently Being Moderated

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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

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

  • 9. Re: How to get the blocking sql
    Hoek Guru
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    >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 Newbie
    Currently Being Moderated

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

1 2 3 Previous Next

Incoming Links

Legend

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