Oracle IAS/Oracle- User sessions freezing

Posted on 2010-01-06
Medium Priority
Last Modified: 2013-12-01
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.
Question by:mansur_mca
LVL 11

Accepted Solution

it-rex earned 1200 total points
ID: 26191291
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;

Assisted Solution

raju1105 earned 200 total points
ID: 26198239

One issue i can remember is invalid objects, especially triggers when using forms.
check all invald object and compile them with utlrp.

Author Comment

ID: 26199342
thanks for your update. I will check and update the same
LVL 48

Assisted Solution

schwertner earned 200 total points
ID: 26199850
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month15 days, 1 hour left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question