This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Jan 27, 2013 7: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 Journeyer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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