Monday, August 29, 2011

Users are complaining that their application is hanging.

Every DBA encountered a situation like this in his/her life.

Below are few steps you can follow in sequence to tackle this.

If the user is complaining the hang problem.

First try to connect to the database itself and fire some query to check whether you are allowed to connect or not.
Check for any locked_objects by checking v$locked_object and dba_waiters and dba_blockers.
Then check at os level that which process is consuming the most of the time.

Analyze the problem if the problem relates to a single user process.
Then check what that user is doing by checking the sql statement he is firing.