Well, I thought I'd let everyone know what we came up with. First of all many thanks to all who helped, Cary, Jared, Dennis, Anjo and others. Everyone gave me more ammo to point the finger outside the database and some of the suggestions started a better dialogue between our different groups here.
The best information I got was from Cary and company at hotsos.com who helped me see how to effectively use a 10046 trace file. The slowest job we had (that was impacting us the most) had a 1-1 parse to execute ratio and had nearly 90% of the trace time spent in SQL*Net wait. Simply by improving the parse to execute ratio, we should eliminate close to half of the waiting time. We did some research into why the application was parsing once per execute and convinced the developers to use the HOLD_CURSORS parameter in the Pro*C compilation. (I had asked a few months before and they said "no need"). Some of the programs worked without a problem, but others had problems due to attempting to reuse "implicit cursors". They are investigating that further and changed it where possible. We also turned on a second nic and changed the tnsnames.ora to use that nic for this db. Those actions together returned us to a reasonable performance, though I still feel that further work on the appdev side is needed to make significant performance improvements. Thanks again to all. Stephen >>> [EMAIL PROTECTED] 10/30/02 08:14AM >>> For what it's worth, we had the same thing going on here recently and have not resolved it. In our case, it is a visual basic app running what is essentially a batch job (don't ask me why a batch job was written as a VB app; I just work here). The client is a PC, and the database is on Tru64 (again ... I just work here). Three different PC's were tried. It appears that the tcp_nodelay parameter worked on two of them but not the third (which, as you might guess, is the production box and the one on which it NEEDS to run faster ... Of course!). All the PC's are on the same subnet, going through same routers to get to the same database. If you get the problem resolved, I will be most interested in your solution. Thus far, we have only been able to attribute it either to sunspots or something about the W2K OS on the PC ... both of which, as we all know, are responsible for a lot of unexplained behavior. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: Stephen Andert 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).
