Session and Objects Lock outs

[surajitmitra78]

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.

[davey23uk]

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

[Thomasps]

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.

[surajitmitra78]

Thanks Guys.

Let me work on the same and i will revert back if i need more inputs on the same.

[surajitmitra78]

Can anyone give me more inputs on this please.

[davey23uk]

well what have you done

[Thomasps]

Yes, I have.

http://tahiti.oracle.com/