RE: SQL*Net message from client

From: Subbiah, Stalin <SSubbiah_at_netopia.com>
Date: Wed, 2 Jun 2004 19:25:39 -0700
Message-ID: <F1F84A2E5F9EBD46A9BDB905EB5B10393A33@mxca1.netopia.com>

It would been better if I had given this information.

8.1.7.4/sol8

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Subbiah, Stalin
Sent: Wednesday, June 02, 2004 7:15 PM
To: 'oracle-l_at_freelists.org'
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_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 Jun 02 2004 - 21:22:29 CDT