Oracle Database 11g Client Result Cache Coming to Java

Server Side Query Result Cache

"Caching, caching and caching” is a well-known secret for performance.
The Oracle database 11g furnishes Query Result Cache, which, when enabled, keeps query result sets in the database server memory for reuse by successive invocation of the same query until invalidated by changes to the underlying table(s); as result, the parsing and executing phases of the query are eliminated.
It also furnishes PL/SQL Function Result Cache[1], which is similar to the SQL query result cache but applied to PL/SQL Functions.

Client-Side Query Result Cache

Caching query result sets in the database memory still requires client/server network round-trip(s). The other well-known secret is "The fastest database access is no database access". You now understand the proliferation of middle-tier data caching frameworks such as open-source Memcache (PHP, Ruby, Java) or Oracle’s Coherence (JCache-compliant in memory distributed data grid), which are object-oriented distributed data caches.
Client Result Cache is SQL oriented[2] middle-tier or client-tier data caching feature of Oracle database 11g which, eliminates the network roundtrip incurred by the server-side query result cache.

In addition, Client Result Cache is synchronized with the database and is automatically invalidated.

Performance Proof Points
Running the Nile benchmark[3] with Client Result Cache enabled and simulating up to 3000 users results in

• Up to 6.5 times less server CPU usage
• 15-22% response time improvement
• 7% improvement in mid-tier CPU usage
  

Enabling Client Result Cache

As of Oracle database 11g Release 1, Client Result cache is available only with OCI-based drivers or adapters including: PHP, JDBC-OCI, OCCI, ODP.Net, Pro*C/C++, Pro*COBOL, and ODBC.

1) Server (database):
Set CLIENT_RESULT_CACHE_SIZE (default 0, cache disabled)
2) Client (sqlnet.ora):
Set OCI_RESULT_CACHE_MAX_SIZE (optional); it overrides server cache size
3) Application code.
Explicitly specify which query to cache with a hint in the query string.
e.g., /*+ RESULT_CACHE */

That’s it!

JDBC Example – CLientRSCache.java

/*
* Client Query Result Cache (sorry for the code formatting)
*/
import the java.sql package
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

class CLientRSCache
{

public static void main (String args [])
throws SQLException
{
long start;
long end;
long elapsed;

String name = null;
OracleConnection conn = null;
OraclePreparedStatement pstmt = null;
OracleDataSource ods = new OracleDataSource();
// Set the URL (user name, and password)
String url = "jdbc:oracle:oci:scott/tiger@//localhost:1522:orcl11g";
ods.setURL(url);

String query = "select /*+ result_cache */ * from emp where empno < ?" conn.setImplicitCachingEnabled(true);

conn.setStatementCacheSize(1); // Cache of 1 Stmt

PreparedStatement pstmt ;

ResultSet rs;

for (int i = 1; i <= 10; i++) {

pstmt = conn.prepareStatement (query);

pstmt.setInt(1,7500);

// Set the start time

start = System.currentTimeMillis();

// Execute the query and retrieve the Result Set

rs = pstmt.executeQuery();

while (rs.next( ) )

{

// process result set

rs.close;

pstmt.close( ) ;

}

// Set the End time

end = System.currentTimeMillis();

elapsed = end - start;

// Print the time taken to prepare and execute query

// Note: the 1st invocation is nor cached

// Successive invocations are cached

// You may invalidate the result set using SQL*Plus.

System.out.println(" Iteration# “ + i + “ Time to prepare and execute the query ” + elapsed);

}

// Close the connection

conn.close();

}

}

Benefits of Client Result Cache

• Easy to use
• Transparent cache consistency with server side changes
• Frees application developers from building a per-process result cache
• Extends database-side query caching to middle-tier and client-tiers
• Ensures better performance by eliminating round trips to the server
• Improves server scalability by saving server resources
• Transparent cache management
• Concurrent access to result-sets optimizes memory management
• RAC support
  

See more details on Client Result Cache in the Oracle Call Interface Programmer’s guide[4].
[1] See the Oracle database 11g documentation for more details on Query Result cache and PL/SQL Function Result Cache.
[2] It is currently not distributed, unlike Coherence or MemCache
[3] An end-to-end ecommerce application server benchmark
[4] http://www.oracle.com/pls/db111/to_pdf?pathname=appdev.111/b28395.pdf