RE: Stuck session help

Check v$session_wait.
It looks like Oracle is just relaxing waiting for the data to arrive
from the application to be loaded.

Waleed

-----Original Message-----
From: Daniel Fink [mailto:Daniel.Fink@xxxxxxx]
Sent: Wednesday, April 07, 2004 6:12 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Stuck session help


We've got an application that is having problems with the
processing becoming 'stuck'. I'm now stuck trying to figure out
why. The process is a series of inserts into several tables.
Quite a few of the inserts succeed, eventually one of them
sticks.

It sticks before the execute phase of the insert. It is not
waiting on a lock (but blocking others). There is plenty of free
space in the datafile.

Any clues? Ideas?


39, 40, 47, 49 - sessions involved
#39 is session that hangs

SESSION LOCKS
-------------

  SID USERNAME   TY lmode       OBJECTNAME
----- ---------- -- ----------- ------------------------------
   14 OWB        TM ROW SHR     WB_RT_SERVICE_QUEUE_TAB
   39 STAGING    TM ROW EXCL    REPORT
   39 STAGING    TM ROW SHR     SITE
   39 STAGING    TM ROW SHR     CONTACT
   39 STAGING    TM ROW SHR     RESULT_DESCRIPTION
   39 STAGING    TM ROW SHR     CHECK_DEFINITION
   39 STAGING    TM ROW EXCL    LONG_TEXT
   39 STAGING    TM ROW EXCL    MLOG$_REPORT
   39 STAGING    TM ROW EXCL    CHECK_RESULT
   40 STAGING    TM ROW EXCL    REPORT
   40 STAGING    TM ROW SHR     CONTACT

  SID USERNAME   TY lmode       OBJECTNAME
----- ---------- -- ----------- ------------------------------
   40 STAGING    TM ROW SHR     SITE
   40 STAGING    TM ROW SHR     CHECK_RESULT
   47 STAGING    TM ROW EXCL    REPORT
   47 STAGING    TM ROW SHR     CONTACT
   47 STAGING    TM ROW SHR     SITE
   47 STAGING    TM ROW SHR     CHECK_RESULT
   49 STAGING    TM ROW EXCL    REPORT
   49 STAGING    TM ROW SHR     CONTACT
   49 STAGING    TM ROW SHR     SITE
   49 STAGING    TM ROW SHR     CHECK_RESULT

21 rows selected.




LOCKS WAITING
-------------

USERNAME          SID TYPE HELD        REQ              ID1    =20
ID2
--------------- ----- ---- ----------- ----------- --------
--------
STAGING            39 TX   Exclusive   None          262169 =20
117042
STAGING            40 TX   None        Share         262169 =20
117042
STAGING            47 TX   None        Share         262169 =20
117042
STAGING            49 TX   None        Share         262169 =20
117042


SESSION_WAIT
------------
  SID EVENT                           WAIT_TIME SECONDS_IN_WAIT
STATE                       P1         P2         P3
----- ------------------------------ ---------- ---------------
------------------- ---------- ---------- ----------
    1 pmon timer                              0           21617
WAITING                    300          0          0
    2 rdbms ipc message                       0               9
WAITING                    300          0          0
    3 rdbms ipc message                       0               3
WAITING                    300          0          0
    4 rdbms ipc message                       0               3
WAITING                    300          0          0
    5 rdbms ipc message                       0               0
WAITING                    300          0          0
    6 rdbms ipc message                       0               3
WAITING                    300          0          0
    7 smon timer                              0            3004
WAITING                    300          0          0
    8 rdbms ipc message                       0            1278
WAITING                 180000          0          0
    9 rdbms ipc message                       0             103
WAITING                    500          0          0
   10 wakeup time manager                     0               1
WAITING                      0          0          0
   11 rdbms ipc message                       0              58
WAITING                   6000          0          0
   12 rdbms ipc message                       0            2324
WAITING                  30000          0          0
   14 queue messages                          0               3
WAITING                  31610 1.5621E+10          5
   16 SQL*Net message from client             0           21382
WAITING             1413697536          1          0
   19 SQL*Net message from client             0              18
WAITING             1413697536          1          0
   20 SQL*Net message from client             0             555
WAITING             1413697536          1          0
   21 SQL*Net message from client             0              13
WAITING             1413697536          1          0
   22 SQL*Net message from client             0           21382
WAITING             1413697536          1          0
   23 SQL*Net message from client             0               1
WAITING             1413697536          1          0
   24 SQL*Net message from client             0           11483
WAITING             1413697536          1          0
   26 SQL*Net message from client             0             555
WAITING             1413697536          1          0
   27 SQL*Net message from client             0           21365
WAITING             1413697536          1          0
   29 SQL*Net message from client             0           13798
WAITING             1413697536          1          0
   30 db file scattered read                 -1               0
WAITED KNOWN TIME           17     104121         16
   33 SQL*Net message to client              -1               0
WAITED KNOWN TIME   1650815232          1          0
   35 SQL*Net message from client             0            5040
WAITING             1413697536          1          0
   36 PX Deq: Execution Msg                   0               3
WAITING              268566527       2883          0
   37 SQL*Net message from client             0              61
WAITING              675562835          1          0
   38 SQL*Net message from client             0           10212
WAITING             1413697536          1          0
   39 SQL*Net more data from client           3            2551
WAITED KNOWN TIME    675562835          4          0
   40 enqueue                                 0            2551
WAITING             1415053316     262169     117042
   41 SQL*Net message from client             0               3
WAITING             1413697536          1          0
   43 SQL*Net message from client             0               0
WAITING             1413697536          1          0
   44 PX Deq: Execution Msg                   0               3
WAITING              268566527       2885          0
   45 SQL*Net message from client             0             660
WAITING             1413697536          1          0
   46 SQL*Net message from client             0              58
WAITING             1413697536          1          0
   47 enqueue                                 0            2551
WAITING             1415053316     262169     117042
   48 PX Deq Credit: send blkd                0               1
WAITING              268566527          1          0
   49 enqueue                                 0            2499
WAITING             1415053316     262169     117042
   50 PX Deq Credit: send blkd                0               6
WAITING              268566527          1          0
   51 SQL*Net message from client             0             120
WAITING              675562835          1          0
   52 SQL*Net message from client             0              61
WAITING              675562835          1          0
   53 SQL*Net message from client             0              61
WAITING              675562835          1          0
   54 SQL*Net message from client             0             123
WAITING              675562835          1          0
   55 SQL*Net message from client             0              61
WAITING              675562835          1          0
   56 SQL*Net message from client            -1               0
WAITED KNOWN TIME    675562835          1          0
   57 SQL*Net message from client             0            1155
WAITING             1413697536          1          0
   58 SQL*Net message from client             0             120
WAITING              675562835          1          0
   60 SQL*Net message from client             0             605
WAITING             1650815232          1          0

49 rows selected.



TRACE FILE for session 39
----------
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PARSING IN CURSOR #4 len=3D195 dep=3D0 uid=3D36 oct=3D2 lid=3D36
tim=3D19462693933 hv=3D953412814 ad=3D'a89716d8'
INSERT /*+ APPEND */ INTO ods.check_result (report_id, check_id,
check_version, result, analysis_id, recommendation_id,
error_desc_id,
engineer_comment_id) values (:1, :2, :3, :4, :5, :6, :7, :8)
END OF STMT
PARSE
#4:c=3D0,e=3D148,p=3D0,cr=3D0,cu=3D0,mis=3D0,r=3D0,dep=3D0,og=3D4,tim=3D1=
9462693920
BINDS #4:

 ***removed by me***

WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 71
p1=3D675562835 p2=3D1 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 70
p1=3D675562835 p2=3D1 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 50
p1=3D675562835 p2=3D8 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 47
p1=3D675562835 p2=3D9 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 58
p1=3D675562835 p2=3D2 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 42
p1=3D675562835 p2=3D9 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 214
p1=3D675562835 p2=3D9 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 42
p1=3D675562835 p2=3D3 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 46
p1=3D675562835 p2=3D1 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 41
p1=3D675562835 p2=3D5 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 39
p1=3D675562835 p2=3D2 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 47
p1=3D675562835 p2=3D1 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 40
p1=3D675562835 p2=3D4 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 37
p1=3D675562835 p2=3D1 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 37
p1=3D675562835 p2=3D14 p3=3D0
WAIT #4: nam=3D'SQL*Net more data from client' ela=3D 27861
p1=3D675562835 p2=3D4 p3=3D0
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: