This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jan 27, 2013 7:32 PM by NathanielNie RSS

Does 'SQL*Net message from client'  indicate the network is not good?

NathanielNie Newbie
Currently Being Moderated
Regarding 10046 trace report below, I simply issued a query to fetch all the records from a table with about 400K records, but it took more than 3 min to fetch all the data.
Most of the time elapsed when waiting for SQL*Net message from client.
Does this situation indicate the network is not good?


SQL ID: d84ta7m8h6wg2
Plan Hash: 3635621446
select *from XXXXXXXXXXXXXXXXX


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 855 0.10 0.88 0 7912 0 426721
total 857 0.10 0.88 0 7912 0 426721

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 123

Rows Row Source Operation
426721 TABLE ACCESS FULL XXXXXXXXXXXXXXX(cr=7912 pr=0 pw=0 time=467817 u
s cost=1847 size=46080540 card=415140)


Elapsed times include waiting on following events:
Event waited on Times Waited Max. Wait Total Waited

SQL*Net message to client 856 0.00 0.00
SQL*Net message from client 856 3.71 179.63
Disk file operations I/O 1 0.00 0.00
SQL*Net more data to client 5224 0.37 0.46

Edited by: NathanielNie on Jan 24, 2013 4:40 PM
  • 1. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Mihael Pro
    Currently Being Moderated
    Yes because elapsed for fetch is 0.88 seconds.
  • 2. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    asahide Expert
    Currently Being Moderated
    Hi,


    Could you check same SQL via BEQ?

    Regards,
  • 4. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    asahide Expert
    Currently Being Moderated
    that'll be fast
    There is the possibility of network problems..

    Regards,
  • 5. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    moreajays Pro
    Currently Being Moderated
    Hi,

    Although this event points to N/W issues , but their could be a situation where a database is waiting for client/application to respond/acknowledge to call
    A situation where data being fetched from DB transferred over the n/w to application/client tool & client processing those records set for report formatting so delay during formatting or writing records to some log file at client can be reported as "SQL*Net message to client" as well

    So its not always n/w but client side processing could be responsible to this event

    Thanks,
    Ajay More
    http://www.moreajays.com
  • 6. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Check the link
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:1860804200346441327
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:366220900346090415
  • 7. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    karan Pro
    Currently Being Moderated
    SQL*Net message to client” does NOT measure network latency! It merely measures how long it took to put the response message into TCP send buffer on the server!

    Once the response packet is put into TCP send buffer, Oracle server process continues on and starts waiting for “SQL*Net message FROM client” again. It’s up to TCP stack to deliver this packet from this point and Oracle server process has no way for measuring directly when did the packet arrive (it would have to start intercepting TCP ACK packets at kernel level for that).

    See TANEL PODER POST here http://blog.tanelpoder.com/2008/02/07/sqlnet-message-to-client-wait-gotcha/
  • 8. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    A situation where data being fetched from DB transferred over the n/w to application/client tool & client processing those records set for report formatting so delay during formatting or writing records to some log file at client can be reported as "SQL*Net message to client" as well
    You are correct.

    The wait indicates time waiting for a response or next call from the client. All of this is not necessarily network time. Some or a significant portion could be the client "busy" doing something. This can be verified by running a query that fetches a very large number of records over the network and testing with different formatting options in SQLPlus. For example, SET PAGES50000 versus SET PAGES10 -- in the former case, the client doesn't spend much time creating page breaks and rewriting column headers, in the latter case, the client has to write column headers to the output. Similarly, if the client is spooling to disk, the disk write time on the client can be significant portion of the "SQL*Net message from client".


    Hemant K Chitale

    PS : You will also see this with ARRAYSIZE. Use SET ARRAYSIZE 10 and 100 in SQLPlus and see the difference. With a smaller ARRAYSIZE, the client will retrieve fewer rows per round trip, resulting in more round trips, resulting in more SQL*Net message from client waits, resulting in a larger total time waited on this event. All without any change in the network speed (bandwidth).

    Edited by: Hemant K Chitale on Jan 25, 2013 11:06 AM
    Added PS note on ARRAYSIZE impact
  • 9. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    that'll be fast
    Is it that "that will be fast" OR "that has already been tested and proven to be fast for the same SQL query on the same database and server" ?

    Hemant K Chitale
  • 10. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    NathanielNie Newbie
    Currently Being Moderated
    Hi all, thanks for your reply.
    Previously I tested it in sql/plus window with 'set arraysize 500' and 'set autotrace trace statistics'
    Still have no idea what the database is waiting for except network.

    With arraysize 5000, I tested again:

    SQL ID: d84ta7m8h6wg2
    Plan Hash: 3635621446
    select *from XXXXXXXXXXXXXXXXXXX


    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 87 0.12 82.73 0 7158 0 429506
    total 89 0.12 82.73 0 7158 0 429506

    Misses in library cache during parse: 0
    Optimizer mode: ALL_ROWS
    Parsing user id: 123

    Rows Row Source Operation
    429506 TABLE ACCESS FULL XXXXXXXXXXXXXXX(cr=7158 pr=0 pw=0 time=406589 u
    s cost=1847 size=46080540 card=415140)


    Elapsed times include waiting on following events:
    Event waited on Times Waited Max. Wait Total Waited
    SQL*Net message to client 88 0.00 0.00
    SQL*Net message from client 88 0.48 28.98
    Disk file operations I/O 1 0.00 0.00
    SQLNet more data to client 5905 0.86 82.36*
    ********************************************************************************

    For each batch(5000 rows), a SQL*Net message from client occured.It seems like the database is waiting for the client response to tell the server that 'I have already got all the 5000 rows, please send another 5000 rows.' And each response sent to server via network took 28.98/88 sec.
    Please point out if I'm wrong here
    Why another event came up after arraysize changed?

    Edited by: NathanielNie on Jan 25, 2013 11:11 AM
  • 11. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    Your ARRAYSIZE is too large for all the rows to fit into one SQLNet "packet". Oracle has to create multiple network packets. That is the new wait event.

    See my update.


    Hemant K Chitale
  • 12. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    NathanielNie Newbie
    Currently Being Moderated
    That has already been tested and proven to be fast for the same SQL query on the same database and server with same settings.
  • 13. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    NathanielNie Newbie
    Currently Being Moderated
    arraysize 500
    Event waited on Times Waited Max. Wait Total Waited
    SQL*Net message from client 856 3.71 179.63
    SQL*Net more data to client 5224 0.37 0.46


    arraysize 5000
    Event waited on Times Waited Max. Wait Total Waited
    SQL*Net message from client 88 0.48 28.98
    SQL*Net more data to client 5905 0.86 82.36

    Hi, Hemant, thank for your interpretation.
    1.It looks like ARRAYSIZE 500 is also too large for all the rows to fit into one SQLNet "packet",when it became 5000, why 'Times Waited' just increased from 5224 to 5905, but 'Total Waited' became huge?
    2.The client did not even show any data as I set autotrace to trace statistics, so I feel there's something wrong with the network that I could not tell.
    I'm upset because the IT support team insist that there's no network issue and I have no idea how to diagnose the network, what I could do is just trying to localize this issue from OWI perspective.

    Regards

    Edited by: NathanielNie on Jan 25, 2013 5:43 PM
  • 14. Re: Does 'SQL*Net message from client'  indicate the network is not good?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    NathanielNie wrote:
    arraysize 500
    Event waited on                   Times Waited Max. Wait Total Waited
    SQL*Net message from client  856              3.71         179.63
    SQL*Net more data to client   5224            0.37         0.46
    
    
    arraysize 5000
    Event waited on                   Times Waited Max. Wait Total Waited
    SQL*Net message from client  88                0.48        28.98
    SQL*Net more data to client   5905             0.86        82.36
    Just to be sure - can you confirm that both these sets of figures came from an SQL*Plus session where you had preceded the SQL with "set autotrace traceonly statistics".
    Could you also show us the autotrace output from the 2 calls.

    Regards
    Jonathan Lewis
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points