-
SQL*Net message from client waiting
This is run from our application as it connects.
Why would the this take up so much rousources?
I cannot figure it out.
PHP Code:
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
********************************************************************************
- Cookies
-
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.
-
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?
PHP Code:
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)
********************************************************************************
Last edited by Cookies; 10-02-2003 at 03:20 PM.
- Cookies
-
======================
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|