commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Forbis <chris.for...@veritas.com>
Subject RE: DBCP vs Oracle's jdbc2.0 optional implementation
Date Tue, 13 May 2003 21:33:22 GMT
A note you might want to have your DBA look at :)  MTS works well and Oracle
is starting to push in training for DBA's to use it for all servers.  But
your DBA needs to make sure that it is set to give the right amount of
connections out. Also there are transaction queues in Oracle that if not
sized correctly will make MTS very very slow.  (Sometimes timing out on
simple things like getting a new connection)  Now with all that said ;)  I
would suggest using dedicated servers when your will be doing your own
pooling, but not everyone gets that luxury.

-----Original Message-----
From: Michael Holly [mailto:mholly@talisentech.com] 
Sent: Tuesday, May 13, 2003 5:19 PM
To: Jakarta Commons Users List
Subject: RE: DBCP vs Oracle's jdbc2.0 optional implementation

Dave

I have used DBCP with Oracle and MySQL.  I have had a couple of problems
that you might want to know about. I found a problem with DBCP when using
Oracle MTS (Multi Threaded Server). MTS is basically a connection managment
solution that allows servers with large amount of connections to be able to
function with limited memory. (Not something I would recommend using unless
you have to) Sometimes the connection pool would hang for 60+ seconds before
returning a connection. It never timed out though.

To get around the MTS problem I had to configure my connection pool to use
dedicated connections to Oracle.  I sure ended up with a funny looking jdbc
url. (See the XML below).  Essentially I added the SERVER = DEDICATED to my
TNS entry and then cut and pasted it on the back of the jdbc url.  I guess
the Oracle thin driver can handle the url easy enough. (My DBA found this
technique in Oracle's Metalink)

My other problem was that the DB I was using had a nightly reboot. (not my
idea).  When the server would reboot the connections in the pool would be
come invalid.

To get around this I had to configure my connection pool to do a validation
query to test the connection before the actual query went through.  I admit
the documentation seems to be a little sparse. Most info can be gleaned by
querying the userlists.  Here are some resources for you

http://www.mail-archive.com/commons-user@jakarta.apache.org/

http://www.mail-archive.com/commons-user@jakarta.apache.org/msg02953.html

http://jakarta.apache.org/commons/dbcp/

http://cvs.apache.org/viewcvs.cgi/jakarta-commons/dbcp/doc/

http://jakarta.apache.org/commons/dbcp/apidocs/org/apache/commons/dbcp/


Here is a copy of the config for the DBCP pool from my server.xml.

[code]
  <Resource name="jdbc/oracle_myco" auth="Container"
         type="javax.sql.DataSource"/>

  <ResourceParams name="jdbc/oracle_myco">
    <parameter>
      <name>factory</name>
      <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
    </parameter>
    <parameter>
      <name>driverClassName</name>
      <value>oracle.jdbc.driver.OracleDriver</value>
    </parameter>
    <parameter>
      <name>url</name>
      <!-- old style connect string -->
      <!--value>jdbc:oracle:thin:@abraxxas:1521:myco</value-->
      <!-- new style connect string for dedicated connection -->
      <value>jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL = TCP)(HOST = abraxxas.myco.com)(PORT = 1521))) (CONNECT_DATA =
(SERVER = DEDICATED) (SID = MYCO)))</value>
    </parameter>
    <parameter>
      <name>username</name>
      <value>myco</value>
    </parameter>
    <parameter>
      <name>password</name>
      <value>#######</value>
    </parameter>
    <parameter>
      <name>maxActive</name>
      <value>20</value>
    </parameter>
    <parameter>
      <name>maxIdle</name>
      <value>10</value>
    </parameter>
    <parameter>
      <name>maxWait</name>
      <value>-1</value>
    </parameter>
    <parameter>
      <name>validationQuery</name>
      <value>select 'validationQuery' from dual</value>
    </parameter>
    <parameter>
      <name>testOnBorrow</name>
      <value>true</value>
    </parameter>
  </ResourceParams>
[/code]

Notice the odd look for the db url.  A entry on Oracle's metalink said that
to get a dedicated link you must copy your SID entry from the tnsnames.ora
file and add the entry (SERVER = DEDICATED) to get the connection.
Obviously, the oracle jdbc driver can handle this.

The other thing to look for is the addition of the

[code]
    <parameter>
      <name>validationQuery</name>
      <value>select 'validationQuery' from dual</value>
    </parameter>
    <parameter>
      <name>testOnBorrow</name>
      <value>true</value>
    </parameter>
[/code]

parameters for the DBCP pool.


Hope this helps


Michael









-----Original Message-----
From: Durham David Contr 805 CSS/SCBE
[mailto:David.Durham1@scott.af.mil]
Sent: Tuesday, May 13, 2003 3:51 PM
To: commons-user@jakarta.apache.org
Subject: DBCP vs Oracle's jdbc2.0 optional implementation


I'm having a problem with Oracle's connection pooling.  Basically it will
max out db processes and not release connections sporatically.  Has anyone
switched to DBCP from Oracle's connection pooling to solve this issue or has
anyone successfully used DBCP with Oracle?


-Dave


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org

Mime
View raw message