1 Reply Latest reply on May 1, 2012 9:49 PM by 934360

    does OCI client result cache and oracle UCP work together?

    934360
      I'm using Oracle 11.2.1.0. I've set up FCF and enabled OCI result cache on server side, so on client side, I'm using UCP for failover.

      this is my datasource configuration:

      <Resource name="jdbc/MyPool"
      auth="Container"
      factory="oracle.ucp.jdbc.PoolDataSourceImpl"
      type="oracle.ucp.jdbc.PoolDataSource"
      connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
      url="jdbc:oracle:oci:@dbhost:1521/myDb"
      user="db_user"
      password="db_password"
      fastConnectionFailoverEnabled="true"
      onsConfiguration=""
      connectionPoolName="dbPool"
      initialPoolSize="5"
      minPoolSize="5"
      maxPoolSize="25"
      connectionWaitTimeout="10000"
      inactiveConnectionTimeout="120000"
      abandonConnectionTimeout="60"
      validateConnectionOnBorrow="true"
      sqlForValidateConnection="select user from dual"
      maxStatements="30"/>

      I've enabled OCI client result cache.

      I use /*+ result_cache */ in my query to take advantage of the result cache.

      so I execute the following scenario:

      i) select /*+ result_cache */ value from myTable where id=1; (this returns a result.)
      ii) update myTable to set a different value for id=1 record;
      iii) run select query again; (this should return a different result. oci result cache gets updated when table changes.)

      if I take out
      "sqlForValidateConnection" and/or "validateConnectionOnBorrow" (i.e. disable sql validation on borrow), the 2nd query after table update doesn't show the latest result. it's still showing the old query result.

      is this a bug? that UCP validation on borrow has an impact on OCI result cache function? this doesn't happen if using dbcp java connection pooling with OCI result cache.