I'm trying to track down why reports from my WebApp are running so slow when connected to Oracle and I've discoverd that there is an unusually high amount of TCP/IP traffic between the Web Server and the Oracle Server as the report is being generated. When I connect my WebApp to SQL Server version of the database the reports run very fast.
Using a Process Monitor I discovered that, when Connected to Oracle, a report that returns zero (0) rows generates 2,671 TCP/IP packets between IIS and the Oracle server, and it takes 16 seconds to generate the report. If I run the same report, but connected to SQL Server, there are only 35 TCP/IP packets and the report runs in less than 2 seconds.
Is there some ODBC or Oracle configuration that I'm missing which is causing the excessive TCP/IP packets?
Oracle Driver: 11g ( SQORA32.DLL ) version 11.02.00.03 dated 10/30/2011.
Database: Oracle 11g
Web Server: Windows Server 2008 32bit with IIS7
Update: It is not just the reports. The entire application is slow when connected to Oracle. I connected my WebApps to a SQL Server version of my database and compared performance and captured TCP/IP traffic. When connected to Oracle there 1000% increase in TCP/IP traffic compared to SQL Server.
What could be causing Oracle and IIS to need to exchange so many TCP/IP packets as opposed to IIS and SQL Server?
There's probably more going on that just the query(s) you're issuing.
You'll want to look at the contents of the packets to see what is going on behind the scenes, or enable sqlnet tracing which will get you the same thing. ODBC tracing would probably be good too.
My best guess is that the application or app framework is making ODBC API calls like SQLTables (which generates a big data dictionary query to all_objects and all_synonyms to determine what tables the user has access to) and a lot of data is being returned, even if the actual query you're issuing returns no rows.
You may want to open a SR with support, we're used to looking at the traces and can tell you fairly quickly where the time is spent and what is going on behind the scenes.
I opened an SR and submitted SQLNet Trace Logs as you suggested but couldn't get the person handling the case to tell me what was going on behind the scenes or where the time was spent. I need to get this system on-line and I am running up against a hard deadline. I created a SQL Server version of the same database and it runs circles around Oracle. An operation within the application that takes 3.5 minutes when connected to Oracle takes only 5 seconds when connected to SQL Server. Both use ODBC. The difference is the amount of time it takes to execute the thousands of TCP/IP packets generated by the Oracle connection.