Stuck session help
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 07 Apr 2004 16:11:36 -0600
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
