1 2 Previous Next 23 Replies Latest reply: Jan 27, 2013 9:32 PM by NathanielNie RSS

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

    NathanielNie
      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
          Yes because elapsed for fetch is 0.88 seconds.
          • 2. Re: Does 'SQL*Net message from client'  indicate the network is not good?
            asahide
            Hi,


            Could you check same SQL via BEQ?

            Regards,
            • 4. Re: Does 'SQL*Net message from client'  indicate the network is not good?
              asahide
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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