Session and Objects Lock outs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Session and Objects Lock outs

  1. #1
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67

    Session and Objects Lock outs

    I am facing a major problems with maintaining session & object lock outs.

    Current Scenario:
    1.) I have 600 users logging into the oracle database.
    2.) On a day to day basis i am seeing that there are session locks and object locks.
    3.) The INSERTs and UPDATEs are going very slow due to which SELECT statements to those objects are taking too much time to execute.

    I need help in following areas please:

    1.) How to deal with making the INSERTs and UPDATEs faster, so that SELECT statements dont wait too long for data retrieval.
    2.) Should the use of FOR UPDATE clause while creating cursors be avoided or not.
    2.) I need to find out all long running transaction and all sessions which is not performing any operations or is been hanged up which may be due to network failure.

    Need help pals, can any one.
    Regards,
    Surajit K Mitra

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you need to trace them and tune them - readers do not block writers in oracle so locks shouldnt be a problem.

    Avoid for update unless you need it, then dont avoid it

    sessions dont get "hanged up", long running transaction can go into v$session_longops

    sounds like you need to do some investigation

  3. #3
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    sessions which is not performing any operations
    select username,osuser,sid,serial# from v$session
    where LAST_CALL_ET > 3600
    and username is not null;

    Will give you idle sessions for an hour.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  4. #4
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    Thanks Guys.

    Let me work on the same and i will revert back if i need more inputs on the same.
    Regards,
    Surajit K Mitra

  5. #5
    Join Date
    Feb 2004
    Location
    Mumbai, Maharastra
    Posts
    67
    Can anyone give me more inputs on this please.
    Regards,
    Surajit K Mitra

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well what have you done

  7. #7
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

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