| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL*Net message from client
Thanks Cary. It's large number snmfc calls with smaller durations. Below
snip has more details.
PARSING IN CURSOR #6 len=336 dep=0 uid=19 oct=6 lid=19 tim=1248377797
hv=3885370321 ad='a7027a74'
UPDATE warehouse.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=0FETCH #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 WAIT #6: nam='SQL*Net message from client' ela= 47 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=1248377890 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377890
WAIT #5: nam='SQL*Net message from client' ela= 0 p1=1413697536 p2=1 p3=0 EXEC #7:c=0,e=0,p=0,cr=3,cu=4,mis=0,r=1,dep=0,og=4,tim=1248377890 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=1248377936 WAIT #5: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0FETCH #5:c=0,e=0,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1248377936 WAIT #5: nam='SQL*Net message from client' ela= 1 p1=1413697536 p2=1 p3=0 ... Goes on.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Cary Millsap
Sent: Wednesday, June 02, 2004 8:25 PM
To: oracle-l_at_freelists.org
Subject: RE: SQL*Net message from client
Stalin,
I don't think the Oracle and OS versions really matter so much...
You're really not showing enough information to diagnose the problem. I can't see enough detail from this V$*_EVENT query to know whether the time waited is because of a large number of snmfc calls with similar durations (with total time large because of so many calls), or if you have a small number of long-latency snmfc calls.
If the problem is a large number of short-duration calls, then your attack should be to reduce the number of dbcalls (parse, exec, fetch) that your application makes. This is because snmfc calls exist between dbcalls. The way to reduce the number of snmfc calls is thus to reduce the number of dbcalls. How? Make sure that the application never parses inside of loops, and use array processing instead of inserting/updating/deleting/fetching a single row per dbcall.
If the problem is a small number of large-duration calls (or if the calls are in excess of about 0.001 seconds if you're using IPC, or 0.010 seconds if you're on a LAN, or about 0.100 seconds if you're on a WAN), then figure out what's causing the individual call latency problem. Potential culprits include overburdened or faulty network equipment, or simply an application client process that's executing a lot of code path between dbcalls.
For a lot more detail, see Part III of the book called "Optimizing Oracle Performance" if you have access to a copy.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
-----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 9:26 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: SQL*Net message from client
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...
Thanks,
Stalin
-- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 22:53:22 CDT
 |
 |