1

I want to specify the number of rows to prefetch for connection pool object using connection properties to override the default row prefetch size (10).

I have set the connection properties as mentioned below, but it is not reflecting while executing the query. Please check and let us know the solution.

Sample code snippet: 

String dataSourceJndiName = "TestDataSource"; 
int oraPrefetchSize = 500; 

try { 
    NamingContext ctx = new NamingContext();  
    DataSource dataSource = 
       (DataSource)ctx.getContext().lookup(dataSourceJndiName);
    conn = ds.getConnection(); 
    Properties props = new Properties(); 
    props.put("oracle.jdbc.defaultRowPrefetch", oraPrefetchSize); 
    conn.setClientInfo(props); 
} catch(Exception e) { 
    logger.error("Exception while getting connection object", e); 
}

Please note that, I have verified in Toad by using "Database -> Monitor -> SGA Trace/Optimization" option to check whether defaultRowPrefetch property is working or not.

For example, I observed the below properties for executed query:

Rows Processed: 1200 
Fetches:         121

Since I have set "oracle.jdbc.defaultRowPrefetch" as 500, the fetches should be 3.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
yadam suresh Chand
  • 11
  • 2
  • Not sure about the Oracle driver, but usually connection properties need to be configured **before** the connection is made, so it should be part of the data source configuration, and clientInfoProperties is not for configuring the driver (see also https://docs.oracle.com/database/121/JJDBC/jdbcvers.htm#JJDBC29007). – Mark Rotteveel Feb 14 '18 at 08:16
  • Yes. But I want to set dynamically in the code based on the system property. I have tried using the following code "((oracle.jdbc.OracleConnection)dbConn).setDefaultRowPrefetch(oraPrefetchSize);" it works fine. But failing in the websphere with ClassCastException. – yadam suresh Chand Feb 14 '18 at 09:32

0 Answers0