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_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 Jun 02 2004 - 21:12:18 CDT
