5 Replies Latest reply on Feb 18, 2013 3:27 PM by Jan-Marten Spit

    Slow connection to Oracle when internet connection is down

    823394
      We have an application suite which inlcudes Oracle 11g on Linux. The application server runs Glassfish.

      From time to time, we need to take the servers out for on site demos, and whenthis happens the applications becomes unstable and slow.

      After a great deal of experimenting and debugging, I found that the problem is that establishing the connection to the Oracle database takes around 30s when there is not internet connection, and around 240ms when present.

      I can reproduce this error using a simple JDBC Java program to connect to the database:


      import java.sql.*;
      import java.util.Date;

      public class oraConnTest {

      public static void main(String[] argv) {

      if ( argv.length != 5 )
      {
      System.out.println ( "Parameters: hostName portNum dbName userName passWord" );
      return;
      }

      String hostName = argv[0];
      String portNum = argv[1];
      String dbName = argv[2];
      String userName = argv[3];
      String passWord = argv[4];

      System.out.println("-------- Oracle JDBC Connection Testing ------");
           System.out.println(new Timestamp(new java.util.Date().getTime()));

      try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Class.forName("oracle.jdbc.OracleDriver");
      }
      catch (ClassNotFoundException e) {
      System.out.println("Where is your Oracle JDBC Driver?");
      e.printStackTrace();
      return;
      }

           System.out.println(new Timestamp(new java.util.Date().getTime()));
      System.out.println("Oracle JDBC Driver Registered!");

      Connection connection = null;

      try {
      connection = DriverManager.getConnection( "jdbc:oracle:thin:@//" + hostName +
      ":" + portNum + "/" + dbName, userName, passWord );
      System.out.println(new Timestamp(new java.util.Date().getTime()));
      }
      catch (SQLException e) {
      System.out.println("Connection Failed! Check output console");
      e.printStackTrace();
      System.out.println(new Timestamp(new java.util.Date().getTime()));
      return;
      }

      if (connection != null) {
      System.out.println("Connection successful!");
      } else {
      System.out.println("Failed to make connection!");
      }
           System.out.println(new Timestamp(new java.util.Date().getTime()));

      }
      }


      The JDBC jar is jdbc6-11.2.0.jar.

      I get similar results when using sqlplus from the Windows desktop.

      First with the internet connection on (simply ensure the ISP router status is connected):

      $ echo "quit" > quit.sql

      $ time sqlplus uname/pwd@dev11r2 @quit

      SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 18 01:22:47 2013

      Copyright (c) 1982, 2010, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      real 0m0.369s
      user 0m0.000s
      sys 0m0.015s

      Next, disconnected router ADSL connection, and retry:

      $ time sqlplus uname/pwd@dev11r2 @quit

      SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 18 01:23:02 2013

      Copyright (c) 1982, 2010, Oracle. All rights reserved.


      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      real 0m28.375s
      user 0m0.000s
      sys 0m0.015s

      So, 28s v 300ms, similar results to JDBC.

      So I don't think it's a JDBC/Java specific problem.

      It appears the database is doing something strange.

      I will run some tcpdumps on the database server to try and see what is happening, but can anyone shed any light on this?
        • 1. Re: Slow connection to Oracle when internet connection is down
          sb92075
          820391 wrote:
          We have an application suite which inlcudes Oracle 11g on Linux. The application server runs Glassfish.

          From time to time, we need to take the servers out for on site demos, and whenthis happens the applications becomes unstable and slow.

          After a great deal of experimenting and debugging, I found that the problem is that establishing the connection to the Oracle database takes around 30s when there is not internet connection, and around 240ms when present.

          I can reproduce this error using a simple JDBC Java program to connect to the database:


          import java.sql.*;
          import java.util.Date;

          public class oraConnTest {

          public static void main(String[] argv) {

          if ( argv.length != 5 )
          {
          System.out.println ( "Parameters: hostName portNum dbName userName passWord" );
          return;
          }

          String hostName = argv[0];
          String portNum = argv[1];
          String dbName = argv[2];
          String userName = argv[3];
          String passWord = argv[4];

          System.out.println("-------- Oracle JDBC Connection Testing ------");
               System.out.println(new Timestamp(new java.util.Date().getTime()));

          try {
          Class.forName("oracle.jdbc.driver.OracleDriver");
          Class.forName("oracle.jdbc.OracleDriver");
          }
          catch (ClassNotFoundException e) {
          System.out.println("Where is your Oracle JDBC Driver?");
          e.printStackTrace();
          return;
          }

               System.out.println(new Timestamp(new java.util.Date().getTime()));
          System.out.println("Oracle JDBC Driver Registered!");

          Connection connection = null;

          try {
          connection = DriverManager.getConnection( "jdbc:oracle:thin:@//" + hostName +
          ":" + portNum + "/" + dbName, userName, passWord );
          System.out.println(new Timestamp(new java.util.Date().getTime()));
          }
          catch (SQLException e) {
          System.out.println("Connection Failed! Check output console");
          e.printStackTrace();
          System.out.println(new Timestamp(new java.util.Date().getTime()));
          return;
          }

          if (connection != null) {
          System.out.println("Connection successful!");
          } else {
          System.out.println("Failed to make connection!");
          }
               System.out.println(new Timestamp(new java.util.Date().getTime()));

          }
          }


          The JDBC jar is jdbc6-11.2.0.jar.

          I get similar results when using sqlplus from the Windows desktop.

          First with the internet connection on (simply ensure the ISP router status is connected):

          $ echo "quit" > quit.sql

          $ time sqlplus uname/pwd@dev11r2 @quit

          SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 18 01:22:47 2013

          Copyright (c) 1982, 2010, Oracle. All rights reserved.


          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          real 0m0.369s
          user 0m0.000s
          sys 0m0.015s

          Next, disconnected router ADSL connection, and retry:

          $ time sqlplus uname/pwd@dev11r2 @quit

          SQL*Plus: Release 11.2.0.2.0 Production on Mon Feb 18 01:23:02 2013

          Copyright (c) 1982, 2010, Oracle. All rights reserved.


          Connected to:
          Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
          With the Partitioning, OLAP, Data Mining and Real Application Testing options

          real 0m28.375s
          user 0m0.000s
          sys 0m0.015s

          So, 28s v 300ms, similar results to JDBC.

          So I don't think it's a JDBC/Java specific problem.

          It appears the database is doing something strange.
          You are funny!
          Do you realize & understand that Oracle has NO inherent networking capability
          & depends completely (100%) on the Operating System for any & all packet exchange?

          Do you know that DNS lookup TIMEOUT is typically around 30 seconds?

          Consider doing some packet sniffing to see what & where the delays are being introduced.

          >
          I will run some tcpdumps on the database server to try and see what is happening, but can anyone shed any light on this?
          Handle:     820391
          Status Level:     Newbie (10)
          Registered:     Dec 13, 2010
          Total Posts:     17
          Total Questions:     9 (8 unresolved)

          why do you waste time here since you rarely get your questions answered here?
          • 2. Re: Slow connection to Oracle when internet connection is down
            Dave Rabone
            About 30 seconds sounds suspiciously like a DNS timeout to me.
            • 3. Re: Slow connection to Oracle when internet connection is down
              Dropbear67
              ditto on the dns time out..

              try putting the IP address of the db host into the tnsnames.ora and see if that resolves it ..
              • 4. Re: Slow connection to Oracle when internet connection is down
                Osama_Mustafa
                Refer to
                How to Diagnose Slow TNS Listener / Connection Performance [ID 557416.1]
                Slow Connection to Database using Shared Server [ID 837104.1]
                • 5. Re: Slow connection to Oracle when internet connection is down
                  Jan-Marten Spit
                  man resolv.conf

                  timeout:n
                  sets the amount of time the resolver will wait for a response from a remote name server before
                  retrying the query via a different name server. Measured in seconds, the default is RES_TIMEOUT
                  (currently 5, see <resolv.h>).

                  attempts:n
                  sets the number of times the resolver will send a query to its name servers before giving up and
                  returning an error to the calling application. The default is RES_DFLRETRY (currently 2, see
                  <resolv.h>).


                  so if it were 10 seconds, i would think DNS.

                  please check your routing table to see there is a gateway listed at the wrong side of your ADSL connection.