SQL*Net message from client waiting
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: SQL*Net message from client waiting

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    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   MaxWait  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

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Oct 2002
    Posts
    182
    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

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    ======================
    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
  •  



Click Here to Expand Forum to Full Width