SQL*Net message from client waits

From: <baba_jaga_at_tahiti.net>
Date: Wed, 27 Jun 2007 12:46:00 -0700
Message-ID: <1182973560.318722.20190@k29g2000hsd.googlegroups.com>

Hello

some users complain about a Powercenter performance, I traced the sessions for an hour, please have a look at last section of the the output of tkprof below. It is a 32 processors Sun running 9.2.0.6, not dedicated to this application. Running the trace the box was in avg. 30% idle. I checked the plans and they seem to be OK, I know that these wait events *may* be idle. Do I have problems with the network in your opinion? Has anybody dealt with comparable problems?

Best regards and thanks in advance for your time Christoph

---

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse   110673     12.42      14.18          0         90
98           0
Execute 111732     39.95      51.05      18217      68627
22700        7215
Fetch   315496    439.51     498.31      83053    4060984
0     1433176

------- ------ -------- ---------- ---------- ---------- ----------

---

total   537901    491.88     563.55     101270    4129701
22798     1440391

Misses in library cache during parse: 1099 Misses in library cache during execute: 8

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  SQL*Net message from client 343929 54.32 87346.16 SQL*Net message to client 343905 0.00 0.75 SQL*Net more data to client 14559 0.01 1.14 db file sequential read 3491 0.44 32.84 log file sync 2159 0.98 23.78 latch free 1882 0.50 6.36 db file scattered read 6183 0.24 29.14 SQL*Net more data from client 52 0.00 0.00 buffer busy waits 2 0.01 0.01

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query
current        rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse     1385      0.23       0.24          3        124
0           0
Execute   2132      0.54       0.53          0         20
4           4
Fetch     4507      0.21       1.49         21       7821
0        3383

------- ------ -------- ---------- ---------- ---------- ----------

---

total     8024      0.98       2.27         24       7965
4        3387

Misses in library cache during parse: 12

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total

Waited

• Waited ----------

  ---

  db file sequential read 24 0.91 1.26 latch free 2 0.00 0.00

110675 user SQL statements in session.  1385 internal SQL statements in session. 112060 SQL statements in session.

---

Trace file: big.txt
Trace file compatibility: 9.02.00
Sort options: default

      35 sessions in tracefile.
  1993788 user SQL statements in trace file.    28074 internal SQL statements in trace file.   112060 SQL statements in trace file.
    1092 unique SQL statements in trace file.  2678317 lines in trace file.
 9747276 elapsed seconds in trace file. Received on Wed Jun 27 2007 - 14:46:00 CDT