Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stuck session help

RE: Stuck session help

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 7 Apr 2004 19:06:29 -0400
Message-ID: <D91D9D5A73FC694BBC52F1EB26AD410F02780CF1@MSGBOSCLD2WIN.DMN1.FMR.COM> 





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_at_Sun.COM]
Sent: Wednesday, April 07, 2004 6:12 PM


To: oracle-l_at_freelists.org


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_at_freelists.org
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_at_freelists.org
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
-----------------------------------------------------------------


Received on Wed Apr 07 2004 - 18:02:53 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US