This discussion is archived
5 Replies Latest reply: Feb 18, 2013 7:27 AM by Jan-Marten Spit RSS

Slow connection to Oracle when internet connection is down

823394 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    About 30 seconds sounds suspiciously like a DNS timeout to me.
  • 3. Re: Slow connection to Oracle when internet connection is down
    Dropbear67 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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.

Legend

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