| 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
Yes you are right. I shouldn't have started with count on time waited.
Once again thanks cary.
-----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:37 PM
To: oracle-l_at_freelists.org
Subject: RE: SQL*Net message from client
One point of clarification that comes to me upon seeing my own response here... Just adding a COUNT column to the existing output isn't going to reveal the answer you need. You really have to know something about the distribution of snmfc durations (are they uniformly distributed? Or skewed?). You'll only be able to see this by looking at the raw trace data. It is impossible to see in the V$ data unless you poll the V$ fixed views very rapidly (on the order of 100s of times per second).
Example: Imagine that you saw "total duration"="100 seconds", "call count"="100 calls". How are the call durations distributed? Nine of ten people will guess that each call is responsible for 1 second of time consumption. But from the information at hand, it is entirely possible that what really happened were ninety-nine calls at 0.001 seconds apiece, and one call at 99.901 seconds. This is evidence of a completely different problem. You can't tell just by looking at the total duration and the call count. It's what in Chapter 1 of the book I refer to as the "red rock problem."
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 Cary Millsap
Sent: Wednesday, June 02, 2004 10: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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 - 23:14:35 CDT
 |
 |