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

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

Stuck session help

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Wed, 07 Apr 2004 16:11:36 -0600
Message-id: <40747C98.E34DDFBD@sun.com> 





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     

ID2


--------------- ----- ---- ----------- ----------- --------




STAGING            39 TX   Exclusive   None          262169  
117042


STAGING            40 TX   None        Share         262169  
117042


STAGING            47 TX   None        Share         262169  
117042


STAGING            49 TX   None        Share         262169  
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






PARSING IN CURSOR #4 len=195 dep=0 uid=36 oct=2 lid=36
tim=19462693933 hv=953412814 ad='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=0,e=148,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=19462693920

BINDS #4:


 ***removed by me***



WAIT #4: nam='SQL*Net more data from client' ela= 71
p1=675562835 p2=1 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 70
p1=675562835 p2=1 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 50
p1=675562835 p2=8 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 47
p1=675562835 p2=9 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 58
p1=675562835 p2=2 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 42
p1=675562835 p2=9 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 214
p1=675562835 p2=9 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 42
p1=675562835 p2=3 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 46
p1=675562835 p2=1 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 41
p1=675562835 p2=5 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 39
p1=675562835 p2=2 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 47
p1=675562835 p2=1 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 40
p1=675562835 p2=4 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 37
p1=675562835 p2=1 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 37
p1=675562835 p2=14 p3=0


WAIT #4: nam='SQL*Net more data from client' ela= 27861
p1=675562835 p2=4 p3=0




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 - 17:08:07 CDT












Original text of this message













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