1 2 Previous Next 23 Replies Latest reply: Jan 27, 2013 9:32 PM by NathanielNie Go to original post RSS
      • 15. Re: Does 'SQL*Net message from client'  indicate the network is not good?
        Rob_J
        P.S. Please use the code tags to wrap the output and make it more readable.
        • 16. Re: Does 'SQL*Net message from client'  indicate the network is not good?
          Hemant K Chitale
          The client did not even show any data as I set autotrace to trace statistics
          The data is, nevertheless, fetched to the client with TRACE STATISTICS. The client saves time in formatting the output and writing it to the screen so these overheads are avoided. Therefore, it does seem that 'SQL*Net message from client' is a network wait with little or no overheads on the client (in the AUTOTRACE TRACE STATISTICS scenario).
          Do NOT oversize your ARRAYSIZE. You might try an ARRAYSIZE of 100 only.

          Hemant K Chitale
          • 17. Re: Does 'SQL*Net message from client'  indicate the network is not good?
            NathanielNie
            Jonathan Lewis wrote:
            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".
            Hi, Jonathan,

            1.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".

            select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
            from v$process a, v$session b, v$parameter c, v$instance d
            where a.addr = b.paddr
            and b.audsid = userenv('sessionid')
            and c.name = 'user_dump_dest';

            The trace file was found by issuing the query above within that session.

            2.Could you also show us the autotrace output from the 2 calls.

            set arraysize 500
            select *from XXXXXXXXXXXXX;

            431582 rows selected.

            Elapsed: 00:02:57.89

            Statistics
            ----------------------------------------------------------
            0 recursive calls
            0 db block gets
            7999 consistent gets
            0 physical reads
            0 redo size
            50489601 bytes sent via SQL*Net to client
            9852 bytes received via SQL*Net from client
            865 SQL*Net roundtrips to/from client
            0 sorts (memory)
            0 sorts (disk)
            431582 rows processed



            set arraysize 5000
            select *from XXXXXXXXXXXXX;

            431582 rows selected.

            Elapsed: 00:01:57.08

            Statistics
            --------------------------------------------------------
            0 recursive calls
            0 db block gets
            7229 consistent gets
            0 physical reads
            0 redo size
            50421225 bytes sent via SQL*Net to client
            1305 bytes received via SQL*Net from client
            88 SQL*Net roundtrips to/from client
            0 sorts (memory)
            0 sorts (disk)
            431582 rows processed


            Thanks
            • 18. Re: Does 'SQL*Net message from client'  indicate the network is not good?
              NathanielNie
              Rob_J wrote:
              P.S. Please use the code tags to wrap the output and make it more readable.
              I find no code tag button in message editor panel...
              Could you please explain how to use it?

              Thanks
              • 19. Re: Does 'SQL*Net message from client'  indicate the network is not good?
                Hemant K Chitale
                Enclose the text that you want properly formatted within two lines that are [  c o d e  ] and [  / c o d e ]

                (no spaces in those tags ; I have deliberately put spaces to avoid them being interpreted as code tags

                Hemant K Chitale

                Edited by: Hemant K Chitale on Jan 28, 2013 10:40 AM
                • 20. Re: Does 'SQL*Net message from client'  indicate the network is not good?
                  NathanielNie
                  Hemant K Chitale wrote:
                  The client did not even show any data as I set autotrace to trace statistics
                  The data is, nevertheless, fetched to the client with TRACE STATISTICS. The client saves time in formatting the output and writing it to the screen so these overheads are avoided. Therefore, it does seem that 'SQL*Net message from client' is a network wait with little or no overheads on the client (in the AUTOTRACE TRACE STATISTICS scenario).
                  Do NOT oversize your ARRAYSIZE. You might try an ARRAYSIZE of 100 only.

                  Hemant K Chitale
                  Actually, there's an ETL job runs everyday to fetch data from that DB.
                  The ETL job is written in java,currently the JDBC fetch size is set to default(0).
                  As the network may not be improved, I'm considering if I should enlarge the value to make the job faster although it might take a lot of memory.


                  Regards
                  • 21. Re: Does 'SQL*Net message from client'  indicate the network is not good?
                    NathanielNie
                    Hemant K Chitale wrote:
                    Enclose the text that you want properly formatted within two lines that are [  c o d e  ] and [  / c o d e ]

                    (no spaces in those tags ; I have deliberately put spaces to avoid them being interpreted as code tags

                    Hemant K Chitale

                    Edited by: Hemant K Chitale on Jan 28, 2013 10:40 AM
                    Test 
                    Test
                    Test 
                    Thanks,
                    • 22. Re: Does 'SQL*Net message from client'  indicate the network is not good?
                      Hemant K Chitale
                      Are you running the SQL query tests from the same Oracle_Home and Server as the ETL engine ? (Want it to use the same libraries and host and network as ETL).

                      Large data sets in ETL can be tuned with network optimisation. Some of it is on the physical network layer (e.g. having a dedicated private network or switched network between the ETL server and the DB Server), some of it can be on the configuration parameters (e.g. arraysize, TCP parameter configuration at the OS layer) or on the libraries (e.g. the JDBC libraries). Some of it could simply be patches to fix bugs !


                      Hemant K Chitale
                      • 23. Re: Does 'SQL*Net message from client'  indicate the network is not good?
                        NathanielNie
                        Hemant K Chitale wrote:
                        Are you running the SQL query tests from the same Oracle_Home and Server as the ETL engine ? (Want it to use the same libraries and host and network as ETL).

                        Large data sets in ETL can be tuned with network optimisation. Some of it is on the physical network layer (e.g. having a dedicated private network or switched network between the ETL server and the DB Server), some of it can be on the configuration parameters (e.g. arraysize, TCP parameter configuration at the OS layer) or on the libraries (e.g. the JDBC libraries). Some of it could simply be patches to fix bugs !


                        Hemant K Chitale
                        Will do.
                        Many thanks for your suggestion.

                        Regards,
                        Nathaniel
                        1 2 Previous Next