Re: Session Data Unit and tcp.nodelay

Peter,

You'll probably get best results by continuing down the path you've begun. You had SQL-traced a process -- have you used TKPROF to aggregate the information into something useful? Reading a ".trc" file from SQL trace for performance is like reading a person's genome to find their gender; there are faster ways...

To run TKPROF, use the syntax "tkprof <trc-filename> <out-filename> sort=exeqry,fchqry,execu,fchcu explain=<un>/<pw>" where:

• trc-filename is the filename of the ".trc" file
• out-filename is whatever filename into which you'd like to place the ASCII text output
• un is an Oracle account username for TKPROF to use in case it need to run EXPLAIN PLAN on a SQL statement
• pw is the password to 'un'

The "sort=" parameter will sort the SQL statements by "logical reads", from most to least. Therefore, the worst SQL statements (i.e. the ones consuming the most logical reads) will percolate to the top. This is important, as TKPROF will not the information for you otherwise, leaving you again with the "search-the-entire-genome" problem you had before. Since you have about 1,700 SQL statements to wade through, I would suggest using the additional "print=50" parameter with TKPROF, which will tell TKPROF to only bother summarizing, EXPLAIN PLANing, and printing the top 50 SQL...

My guess is that you will see the first 1-3 SQL statements consuming over 90% of all logical reads. Tune those and you will solve your performance issue. Fiddling with obscure parameters (especially networking parameters!) is something to consider *after* the more productive avenues have yielded no results...

---

TCP.NODELAY is SQL*Net's implementation of the TCP-level "TCP_NODELAY"
functionality.  Essentially, it instructs the network layer not to operate
in a synchronous fashion (i.e. send message, await ACK, send next message)
but rather to send messages when they are queued.  It seems to get used in a
lot of Windows applications where folks are clickety-clicking away with
their mouse, while prior requests are still being processed.  Go ahead and
try it, but it will probably have no impact.  After all, isn't it more
important to deal with the reason that the server-side of a client-server
connection is not responding?

Also, SDU ("session data unit") is the SQL*Net packet sizing.  It is also
unlikely that adjusting this upwards will help unless you are habitually
transferring huge amounts of data to and fro.  Ironically, the effect of
TCP.NODELAY would probably negate setting SDU higher, and would have the
impact of sending more (thus smaller) packets to and fro...

Hope this helps...

-Tim

----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Friday, October 11, 2002 2:05 PM




> We have third party CAD application called VPM which

> we are using to organize 3d engineering models.  It

> uses an Oracle database to store some information on

> locations of models and relationships between parts.

> We are having some performance problems which seem to

> point to the database access.  With sql tracing turned on,

> we determined that one of the operations in question

> used more than 1700 sql statements.

>

> The consultant we work with is suggesting that we try to

> improve the Net8 throughput either by seting tcp.nodelay

> or by adjusting the size of the Session Data Unit.  A quick look

> at the Net8 manual suggests that both of these could potentially

> improve throughput by forcing packets to be sent out sooner

> instead of blocking several requests/responses together.

>

> Does anyone have any experience with these settings?

>

> Any suggestions as to what settings to try as a start?

>

> Thanks,

> Peter Schauss

> Northrop Grumman Corporation

> schaupe_at_mail.northgrum.com

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: Schauss, Peter

>   INET: SCHAUPE_at_mail.northgrum.com

>

> Fat City Network Services    -- 858-538-5051 http://www.fatcity.com

> San Diego, California        -- Mailing list and web hosting services

> ---------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).