Locking
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Locking

  1. #1
    Join Date
    Aug 2002
    Posts
    176

    Locking

    In one of our boxes a particular Update statement get locked up frequently and we usually kill the session. How do we go about resolving this.

    Thanks
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    if you don't mind can i know,
    how you identify which update statement gets locked up.

    thank you

    -Raja

  3. #3
    Join Date
    Aug 2002
    Posts
    176
    by using

    select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
    n_id and b.object_id=c.object_id

    then use the hash value in

    select sql_text,
    USERS_OPENING,
    EXECUTIONS,
    USERS_EXECUTING,
    LOADS,
    FIRST_LOAD_TIME,
    INVALIDATIONS,
    PARSE_CALLS,
    DISK_READS,
    BUFFER_GETS
    from v$sqlarea where hash_value=upper('&hash_value')


    You can combine bothe quieries to directly fetch teh sql if you want to
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Rohit
    by using

    select a.sid, a.serial#, a.sql_hash_value, substr(c.object_name,1,25) from v$session a, v$locked_object b, dba_objects c where a.sid = b.sessio
    n_id and b.object_id=c.object_id

    then use the hash value in

    select sql_text,
    USERS_OPENING,
    EXECUTIONS,
    USERS_EXECUTING,
    LOADS,
    FIRST_LOAD_TIME,
    INVALIDATIONS,
    PARSE_CALLS,
    DISK_READS,
    BUFFER_GETS
    from v$sqlarea where hash_value=upper('&hash_value')


    You can combine bothe quieries to directly fetch teh sql if you want to
    I suppose u know what u r talking abt???
    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"

  5. #5
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    thank you Rohit!

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Have you worked out why this operation is so prone to getting locked out?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Aug 2002
    Posts
    176
    Its from an Oracle Apps Application. We regularly Kill this session whihc runs this update and gets locked up. Can we resolve it without killing the session? Or should any modifiaction be done to the application? Pls do advice..
    Success Consists of Getting Up Just One More Time Than You've Fallen Down
    Be Blessed

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved)"

    Abhay.
    Last edited by abhaysk; 03-18-2004 at 02:02 AM.
    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
    Sep 2003
    Location
    over the hill and through the woods
    Posts
    995
    Originally posted by abhaysk
    if u know a bit abt locking then, u will understand wass the difference in Locking & Blocking.. I hope u really dont mean "When Update Statement Is Run, Table/Object Involved Is Locked Up (ROWS Invloved"
    Good catch!

    Another simple way to see if that process is blocking anyone else would be to use OEM's lock manager. People might hurt thier fingers typing out that much code Rohit
    Oracle it's not just a database it's a lifestyle!
    --------------
    BTW....You need to get a girlfriend who's last name isn't .jpg

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by OracleDoc
    People might hurt thier fingers typing out that much code Rohit
    That code is simply useless, what do u think that code tells .. blocking session?? -- No way..

    It jus tells u what objects are locked due any DML & the SQL Statement involved..

    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