Home » RDBMS Server » Performance Tuning » JDBC connection performance
JDBC connection performance [message #405815] Fri, 29 May 2009 12:03 Go to next message
Messages: 3
Registered: April 2009
Junior Member
Hi, I know very little about Oracle, and I'm not even sure if this is the right place for my question, but hopefully someone can help me.

I have a Java class that creates an Oracle connection and then executes a simple call. The call itself is very simple and takes barely any time, and often when the code is executed it runs very quickly, under a second to run a procedure and print the result. However sometimes when I run it takes forever, 15, 30 seconds for the exact same inputs and result as when it runs very fast.

I ran a test where I executed and processed the results of the statement 100 times after creating the connection and this always ran fast, leading me to believe the hang-up is during the creation of the connection (strangely, when executing the statement 50 times or so per connection, I never received the hang-up that I did when running it only once). I don't know if I am doing something wrong in my code that is causing this, or if I do not have Oracle set up right, any help would be appreciated.

Thanks, I have included the relevant code, its ugly, but maybe it will show my problem if it is indeed the code.

     40     String driverName = "oracle.jdbc.driver.OracleDriver";
     41     Class.forName(driverName).newInstance();
     43       Connection conn = null;
     44       String connStr = //connection string;
     45       String username = //user
     46       String password = //pass
     48       Properties props = new Properties();
     50       props.put("user", username );
     51       props.put("password", password);
     52       props.put("SetBigStringTryClob", "true");
     54       ResultSet rs = null;
     55       String realQ = generateQuery(query);
     57      //for(int i=0; i<100; i++){
     59       // Create the database connection, if it is closed.
     60       if ((conn==null)||conn.isClosed())
     61         conn = DriverManager.getConnection( connStr, props );
     63      String thetext=null;
     64      for(int i=0; i<1; i++){ //this is for testing, currently does nothing
     66      //PreparedStatement pstmt = null;
     67      CallableStatement cstmt = null;
     69      cstmt = conn.prepareCall("{? = call markup_document(?,?)}");
     70      cstmt.registerOutParameter(1, java.sql.Types.CLOB);
     71      cstmt.setString(2,realQ);
     72      cstmt.setString(3,docno);
     73      cstmt.execute();     
     76      Clob mklob = cstmt.getClob(1);
     78      thetext = (mklob.getSubString(1,(int)mklob.length()));
     79      cstmt.close();
     80      }
     81      conn.close();
     82      System.out.println(thetext);
Re: JDBC connection performance [message #405856 is a reply to message #405815] Sat, 30 May 2009 07:29 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Add instrumentation to your program (ie. write a message with the date/time after each statement. It looks like you are calling a PL/SQL procedure - do the same thing with that (write to a log table). Trace the session with SQL Trace as well and analyse the output with TKPROF. (See http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#i4763)

This should narrow down exactly where the problem is occurring and give you a bit more information to go on

Ross Leishman
Next Topic: IOT - what is that?
Goto Forum:

Current Time: Thu Jan 19 13:59:58 CST 2017

Total time taken to generate the page: 0.09052 seconds