This discussion is archived
3 Replies Latest reply: May 18, 2012 5:45 PM by 931964 RSS

Oracle ODBC generating excessive network traffic

925505 Newbie
Currently Being Moderated
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?

Jerry
  • 1. Re: Oracle ODBC generating excessive network traffic
    gdarling - oracle Expert
    Currently Being Moderated
    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.

    Greg

    Edited by: gdarling on Mar 22, 2012 7:34 PM
  • 2. Re: Oracle ODBC generating excessive network traffic
    925505 Newbie
    Currently Being Moderated
    gdarling,

    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.

    Do you have any other suggestions?
  • 3. Re: Oracle ODBC generating excessive network traffic
    931964 Newbie
    Currently Being Moderated
    If possible try using a native oracle driver Oracle Objects for OLE (OO4O).

Legend

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