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 [EMAIL PROTECTED]
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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Schauss, Peter
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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
