RE: SQL*Net message from client
- From: "Subbiah, Stalin" <SSubbiah@xxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 2 Jun 2004 19:25:39 -0700
It would been better if I had given this information.
8.1.7.4/sol8
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Subbiah, Stalin
Sent: Wednesday, June 02, 2004 7:15 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: SQL*Net message from client
Hi,
I'm trying to figure out a problem with "SQL*Net message from client" smfc
wait event albeit, they are considered a idle event. We have a batch script
running from a machine different than the database via cron. This script
summarizes data and inserts/updates rows one at a time. There will be lots
of individual insert/updates. Yeah we could have done this via single insert
select and update but we don't have control over it. Back to the problem,
looking at v$session_event for this batch session (see below), the top event
being 'SQL*Net message from client'. This batch is been running for almost
3hrs.
EVENT TIME_WAITED
------------------------------ -----------
SQL*Net message from client 950067
STAT--CPU used by this session 6801
db file sequential read 5886
db file scattered read 788
direct path read 425
direct path write 362
SQL*Net more data to client 299
log buffer space 40
SQL*Net message to client 29
log file switch completion 20
latch free 13
buffer busy waits 0
file open 0
Also I see the txn being active in v$transaction for this batch session.
Then I did 10046 trace via oradebug to see what's happening and I don't see
anything unusual other than smfc wait (most of it). How could I further
debug this issue without tampering batch script.
Snip from raw trc file...
*** SESSION ID:(103.24473) 2004-06-02 20:20:01.513 WAIT #6: nam='SQL*Net
message from client' ela= 46 p1=1413697536 p2=1 p3=0 =====================
PARSING IN CURSOR #5 len=130 dep=0 uid=19 oct=3 lid=19 tim=1248377751
hv=2027686058 ad='aefffd68'
SELECT count (*) from summary_hits_fact shf where
shf.page_hit_key = :p1 AND shf.hit_date_key = :p2
END OF STMT
EXEC #5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1248377751
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377751
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
===================== PARSING IN CURSOR #7 len=340 dep=0 uid=19 oct=2 lid=19
tim=1248377751
hv=2447813369 ad='ac3b2208'
INSERT INTO summary_hits_fact
(page_hit_key, hit_date_key, hit_count)
SELECT hf.page_hit_key, hf.hit_date_key, count(*)
FROM hits_fact hf
WHERE hf.page_hit_key = :p1
AND hf.hit_date_key = :p2
GROUP BY hf.page_hit_key, hf.hit_date_key END OF STMT EXEC
#7:c=0,e=0,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=4,tim=1248377751
WAIT #7: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT
#7: nam='SQL*Net message from client' ela= 46 p1=1413697536 p2=1 p3=0 EXEC
#5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1248377797
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377797
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
===================== PARSING IN CURSOR #6 len=336 dep=0 uid=19 oct=6 lid=19
tim=1248377797
hv=3885370321 ad='a7027a74'
UPDATE summary_hits_fact shf
SET (hit_count) =
(SELECT count(*)
FROM hits_fact hf
WHERE shf.page_hit_key = hf.page_hit_key
AND shf.hit_date_key = hf.hit_date_key)
WHERE shf.page_hit_key = :p1
AND shf.hit_date_key = :p2
END OF STMT
EXEC #6:c=0,e=0,p=0,cr=6,cu=2,mis=0,r=1,dep=0,og=4,tim=1248377797
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0 WAIT
#6: nam='SQL*Net message from client' ela= 46 p1=1413697536 p2=1 p3=0 EXEC
#5:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1248377843
WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377843
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0
EXEC #6:c=0,e=0,p=0,cr=6,cu=2,mis=0,r=1,dep=0,og=4,tim=1248377843
WAIT #6: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
..... Goes on.
Thanks,
Stalin
----------------------------------------------------------------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
