Oracle IAS/Oracle- User sessions freezing

Posted on 2010-01-06
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

    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;
    LVL 3

    Assisted Solution


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

    Author Comment

    thanks for your update. I will check and update the same
    LVL 47

    Assisted Solution

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now