How to find the SQL that locks resources?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to find the SQL that locks resources?

  1. #1
    Join Date
    Apr 2001
    Posts
    125

    How to find the SQL that locks resources?

    Hi, All,

    On our production db, we have some applications locks some table for a long time, we want to find out the SQL, I join the following table
    select a.sid as sid,
    a.serial# as serial_num,
    b.sql_text sql
    v$session a,
    v$sqltext b,
    dba_lock c
    where a.sid = c.session_id
    and a.username = 'XXXX'
    and ((a.sql_address = b.address
    and a.sql_hash_value = b.hash_value)
    or (a.prev_sql_addr = b.address
    and a.prev_hash_value = b.hash_value))

    But the sql_text is not the SQL that locks the table, instead, it is the last SQL from that sid/serial#.

    Is there a way to find the SQL that hold the locks?

    Thanks

  2. #2
    Join Date
    Jan 2001
    Posts
    3,131
    Damn, reading this makes me realize how dependent I have become on OEM. Cause all you have to do is look at "locks" in OEM.
    I remember when this place was cool.

  3. #3
    Join Date
    Apr 2001
    Posts
    125
    Are you sure OEM shows the SQL that locks table, not current SQL? I just took a look at OEM

  4. #4
    Join Date
    Jan 2001
    Posts
    3,131
    Quote Originally Posted by zxmgh
    Are you sure OEM shows the SQL that locks table, not current SQL? I just took a look at OEM
    Well under locks in OEM you can grab the SID number of the session locking the table. From there you can go to session, double click on the session that is locking and look at the SQL that that session is running.
    I remember when this place was cool.

  5. #5
    Join Date
    Apr 2001
    Posts
    125
    I saw the same thing, but the problem really bothers me is how to see the SQL that locks. For example, my applications run the following SQL in this order:

    1. SELECT 1 FROM t1
    2. SELECT 1 FROM t2 FOR UPDATE
    3. SELECT 1 FROM t3

    Let's assume No.2 SQL locks table t2 and I want to find out this SQL, but the lock monitor in OEM will only show me NO.3 SQL.

    Correct me if you think I am wrong

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    you could try your luck with v$open_cursor.


    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Can't you track down the session and username and ask the developer what they are running, or is this a front end thing. Are there any developers there that might recognize that sql and help point you in the right direction? When was the last time you shot or severly beat down your developemnt team?
    I remember when this place was cool.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    hanky does that quite often.. may be u shud take a training session from him..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Apr 2001
    Posts
    125
    OK, I only shows 3 SQL in my example. In reality, that application has about 6000 DML SQLs, in my first post, I run that query and I know which table has been locked, based on that, my candidate list shrinks to 2000 SQLs, my manager wants me to tell him which SQL in these 2000 SQLs locks the table. If I tell him I only know which application but I don't know which SQL, I will get a punch on my face

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    u know the object which is locked.. what is the problem to track that SQL for Update Statement?

    something like this..

    Code:
    Select * From V$Sqltext Where Upper(Sql_Text) Like
    '%SELECT%OBJECT_NAME%FOR%UPDATE%';
    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width