Tuesday, February 19, 2008

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

5 comments:

Nigel said...

Kuassi

Your reference [3] in the paragraph "Performance Proof Points" seems to be incorrect - it tries to create a blogger post (this one?)

Regards Nigel

Kuassi Mensah said...

Nigel,

Thanks for the catch; i fixed it.

Kuassi

Anonymous said...

Hi...

I wonder about your test.
Did you check client result cache stat view ?

ex.. client_result_cache_stat$ or v$client_result_cache_stats ..

I can't see anything data in client result cache views.

How to check query used server-side result cache or client-side result cache.

please , let me know how to check query using client cache..

Kuassi Mensah said...

Hi,

The client cache stats are (i) sent periodically to the server, (ii) sent on a roundtrip to the server, (iii)and removed after the last session (in the process) using the cache is ended.

The code sample provided does not provide an opportunity for sending the stats to the server since it does not meet the above conditions.
I'll post a new code that furnish such opportunity, soon.

Kuassi

Anonymous said...

(法新社a倫敦二B十WE四日電) 「情色二零零七」情趣產品大產自二十三日起在倫敦的肯辛頓奧林匹亞展覽館舉成人電影行,倫敦成人電影人擺脫對性的保守態度踴躍參觀,許多穿皮衣與塑膠緊身衣的好色之徒擠進這項成人網站世界規模最大的成人生活展,估計三天展期可吸引八萬多好奇民眾參觀。色情

活動計畫負責人a片下載米里根承諾:「要搞浪漫、誘惑人、玩虐待,你渴望的我們都有。」
成人網站
他說:「時髦的設計成人影片與華麗女裝,從吊飾到束腹到真人大小的雕塑,是我們由a片今年展出的數千件產品精av選出的一部分,A片下載參展產品還包括時尚服飾、貼身女用內在美、鞋子、珠寶、玩具、影片、藝術av女優成人影片圖書及遊戲,更不要說性愛輔具及馬術裝備。」
色情
參觀民眾遊覽情色電影A片百五十多個攤位,有性感服AV裝、玩具a片及情色食品,迎合各種品味。

情色大舞台上表演的是美國野蠻搖滾歌手瑪莉蓮曼森的前妻─情色電影全世色情影片界頭牌脫衣舞孃黛塔范提思,這是她今年在英情色國唯一一場表演。

以一九四零年代風格演出的黛塔范提思表演性感的天堂鳥、旋轉木馬及羽扇等舞蹈AV女優

參展攤位有的推廣情趣用品,有的公開展示人體藝術和人體雕塑,也有情色藝術家工會成員提供建議。