Databases hang issue

Databases hang issue

I very common problem for most DBA’s face is user complain about their application is hang. There can be so many reasons for it. One of the most common reasons I find is user do not properly commit their work so their application hang.
Let us take an example

I got a complaint from a user “My application is hanging” please check.
I go to sql prompt as DBA then execute the command.

SQL> select username,sid,osuser,machine, terminal, program,event, blocking_session,wait_class from v$session where username is not null and wait_class <> ‘Idle’ and state =’WAITING’ order by username

Output of this command is
TECHWAVE_ECOMMERCE 146 rajat HTSS\HTSS27 HTSS27 sqlplus.exe enq: TX – row lock contention 122 Application

Output clearly shows that user techwave_ecommerce having sid 146 from machine htss27 is waiting for the event row lock contention i.e. this is waiting to get a lock on a row for work. Session who has blocked this user is sid 122.

Now we will find the detail of blocking session having sid 122.

SQL> select sid,username,machine,program from v$session where sid=122

Output is
122 TECHWAVE_ECOMMERCE htss27 SQL Developer

It means blocking session is from same system htss27 but using different program SQL developer.
Now find query which is hanging another user by command.

SQL> select sid, sql_text from v$session s, v$sql q where sid in (122) and ( q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id)

Output is
122 update tek_store_master set store_url=’asfda’ where storeid=251

This is the query which hangs another user.
To solve the issue we have two options.
i) Commit session sid 122
ii) Kill session 122

Ask user to commit his session which is best solution. If it is not possible kill user’s session using commands.

SQL> alter system kill session ‘122,894’;
Your problem is solved now.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s