SQL*Net message from client waiting

[Cookies]

This is run from our application as it connects.
Why would the this take up so much rousources?

I cannot figure it out.

select count(*) 
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    11018      0.56       0.65          0          0          0           0
Execute  11018      2.88       2.81          0          0          0           0
Fetch    11018      2.31       2.14          6      33055          0       11018
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    33054      5.75       5.62          6      33055          0       11018

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 47  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 r=0 w=0 time=100 us)
      1   TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=89 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   28819        0.00          0.06
  SQL*Net message from client                 28819        2.02         27.39
  latch free                                      4        0.00          0.00
  db file sequential read                         3        0.00          0.00
  SQL*Net more data from client                   2        0.09          0.18
******************************************************************************** 

[stecal]

Who says it is a problem or is consuming resources? Max wait of zero means there was a maximum wait of zero seconds. Are you experiencing performance problems?

SQL*Net message from client
Although this is an idle event, it is important to explain when this event can be used to diagnose what is not the problem. This event indicates that a server process is waiting for work from the client process. However, there are several situations where this event could accrue most of the wait time for a user experiencing poor response time. The cause could be either a network bottleneck or a resource bottleneck on the client process.

[Cookies]

Yes, our db perfomance stinks to high heaven.
Mainly I believe this to be poor sql writing and have been
re-writing sql and tracing application sessions to
find the bottlenecking code that the developers wrote.

Other than that I have been running statspack, but the
trace files show a lot more about what is happening.
Currently the CPU and DISK usage on the server goes crazy
when you run through the application.

I guess I am asking why the application trace
shows those statistics, but when I run a trace
on a seperate session I get the below stats.
What is the difference and why are theyso different?

select count(*)
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.01       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.00          0          3          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 53

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=3 r=0 w=0 time=255 us)
      1   TABLE ACCESS FULL DUAL (cr=3 r=0 w=0 time=241 us)

******************************************************************************** 

[tamilselvan]

======================
SQL*Net message to client 28819 0.00 0.06
SQL*Net message from client 28819 2.02 27.39
=======================

The number of round trip calls between the client and server can be reduced by increasing the SDU and TDU parameters in listener.ora and tnsnames.ora files. By default TCP network sends 1500 bytes in one package. Set it to 8K.

In the TCP network, the network first receives data from a process, then it will wait for 20 ms to get additional data, so that it will combine the data and send to the client. The is called nagle algorithm. In general UNIX administrator will not disable the algorithm at the UNIX level. So, Oracle provided protocol.ora file to disable it.

Also, in protocol.ora file, set tcp.nodelay=yes. This setting may not be required for newer version of oracle (from 8.1.7.4 )

If you test your process with event 10046 level 12, you will see the real elapsed time under SQL*Net message. That time is reported in centiseconds. By closely reading the raw trace file, you will come to know that whether your client is sending more data, or server is sending data to client. With various values of SDU and TDU, you can arrive the best settings.

Tamil