• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

Oracle IAS/Oracle- User sessions freezing

In the peak business hours our application is accessed by more 600 users, some times users reported with sessions hanging issue. I really confused whether the issue in the middle tier Or Database. In middle when I see the resource consumption of the server is very less, CPU  80 Idle, Memory  75% free.  We are  running form based application.

How to justify at which layer issue is persist. Is either database / Application server.

Thanks for your assistance.
3 Solutions
Let's try to isolate the main reason;and make sure if it is the database or not.
1-Do you have anything in the alert log?
2-check your listener.log
3-how frequently this happens <every day /every day the same time/do you have any other apps doing anything the same time (I am talking about the DB server).
4-you need to make an AWR report and compare (1 for normal activity ,the 2nd around when you had the issue).

please from sqlplus
show parameter session;
show parameter process;
show parameter jobs;

most likely it is not a locking issue but to make sure

do these steps

get the SID from this SQL

   Select sid, serial#, username, machine
     from v$session
    where username is not NULL;

or whoever user SID is complaining about the hung issue

from v$session_wait                                                            
where sid = <SID>;

The event 'SQL*Net message from client' means that server is waiting for new

repeat this SQL to make sure the wait message still the same
select s.username, s.sid, t.sql_text "Last SQL"                      
 from v$session s, v$sql t                                                    
 where s.sql_address =t.address and s.sql_hash_value =t.hash_value    
   and s.sid = <SID>;                                                

4) Check whether it is waiting for locks:

select username, lockwait, row_wait_obj#, row_wait_file#, row_wait_block#,
  from v$session
 where sid = <SID>;

The locks thats are waited in the system:

select sid, type, id1, id2, lmode, request, ctime, block
  from v$lock
 where request>0;

One issue i can remember is invalid objects, especially triggers when using forms.
check all invald object and compile them with utlrp.
mansur_mcaAuthor Commented:
thanks for your update. I will check and update the same
Try to investigate the DB parameter SESSIONS

It gives the maximum number of the sessions and often by default is set to 150

Even you can have set  bigger value because of the RAM consumption(up to 5 MB per session)
the database can postpone some connections.

Also there can be dead sessions that should be removed using
SQLNET.EXPIRE_TIME parameter in sqlnet.ora
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now