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
USERNAME SID OSUSER MACHINE TERMINAL PROGRAM EVENT BLOCKING_SESSION WAIT_CLASS
TECHWAVE_ECOMMERCE 146 umesh 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
SID USERNAME MACHINE PROGRAM
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
SID SQL_TEXT
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.

Thanks
Umesh Sharma

No comments:

Post a Comment