Slow sql execution on Oracle connection from weblogic controlled data source
PROBLEM
A simple sql that returns about 500 rows takes:
- 40ms in Toad (this returns the first 500 rows)
- 500-600ms in Java with connection obtained from oracle.jdbc.pool.OracleDataSource instance.
- 2100-2500ms in Java with a connection derived from data source configured in Weblogic viewed via JNDI
The above timings prevent getting a connection instance.
Problem: Slow performance of Option 3. Why? How can this be made more reasonable?
SETUP
- Weblogic 10.3.5
- Oracle 11g RDMBS
TEST CODE
import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.GregorianCalendar; import java.util.Hashtable; import java.util.List; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import oracle.jdbc.OracleConnection; import oracle.jdbc.pool.OracleDataSource; import Facility; import BrowserSQL; public class JDBCTest { public static Connection conn; public static Connection getConnection () { String url = "localhost: 1521: testdb"; String usr = "test"; String pswd = "test"; Connection conn = null; try { OracleDataSource ods = new OracleDataSource (); ods.setURL ("jdbc: oracle: thin: @" + url); ods.setUser (usr); ods.setPassword (pswd); conn = ods.getConnection (); } catch (SQLException e) { e.printStackTrace (); } return conn; } public static Connection getConnectionWeblogicJndi () { Connection conn = null; try { Hashtable ht = new Hashtable (); ht.put (Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); ht.put (Context.PROVIDER_URL, "t3: // localhost: 7001"); Context ctx = new InitialContext (ht); DataSource ods = (DataSource) ctx.lookup ("jdbc / test"); conn = ods.getConnection (); } catch (SQLException e) { e.printStackTrace (); } catch (NamingException e) { e.printStackTrace (); } return conn; } // get the next val from a given sequence. public static void runQuery (Connection conn, String sql) { ResultSet rs = null; Statement stmt = null; try { stmt = conn.createStatement (); rs = stmt.executeQuery (sql); while (rs.next ()) {} } catch (SQLException ex) { ex.printStackTrace (); } finally { close (rs, stmt, conn); } } public static void close (ResultSet rs, Statement ps, Connection conn) { if (rs! = null) { try {rs.close (); } catch (SQLException ex) {ex.printStackTrace (); } } if (ps! = null) { try {ps.close (); } catch (SQLException ex) {ex.printStackTrace (); } } if (conn! = null) { try {conn.close (); } catch (SQLException ex) {ex.printStackTrace (); } } } public static void main (String [] arg) throws SQLException { // Connection conn = JDBCTest.getConnectionWeblogicJndi (); Connection conn = JDBCTest.getConnection (); ((OracleConnection) conn) .setDefaultRowPrefetch (1000); long a = GregorianCalendar.getInstance (). getTimeInMillis (); System.out.println ("Stating testGetAllFacilityByQuery ..." + a); runQuery (conn, BrowserSQL.getAllFacilities ()); long b = GregorianCalendar.getInstance (). getTimeInMillis (); System.out.println ("End testGetAllFacilityByQuery ..." + b + "-------- total time:" + (ba) + "ms"); } }
+3
source to share
No one has answered this question yet
Check out similar questions: