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

10 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

Choi young joon 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...

情趣用品,情趣用品,情趣用品,情趣用品,情趣,情趣,情趣,情趣,按摩棒,震動按摩棒,微調按摩棒,情趣按摩棒,逼真按摩棒,G點,跳蛋,跳蛋,跳蛋,性感內衣,飛機杯,充氣娃娃,情趣娃娃,角色扮演,性感睡衣,SM,潤滑液,威而柔,香水,精油,芳香精油,自慰套,自慰,性感吊帶襪,吊帶襪,情趣用品加盟AIO交友愛情館,情人歡愉用品,美女視訊,情色交友,視訊交友,辣妹視訊,美女交友,嘟嘟成人網,成人網站,A片,A片下載,免費A片,免費A片下載愛情公寓,情色,舊情人,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,一葉情貼圖片區,情色小說,色情,色情遊戲,情色視訊,情色電影,aio交友愛情館,色情a片,一夜情,辣妹視訊,視訊聊天室,免費視訊聊天,免費視訊,視訊,視訊美女,美女視訊,視訊交友,視訊聊天,免費視訊聊天室,情人視訊網,影音視訊聊天室,視訊交友90739,成人影片,成人交友,美女交友,微風成人,嘟嘟成人網,成人貼圖,成人電影,A片,豆豆聊天室,聊天室,UT聊天室,尋夢園聊天室,男同志聊天室,UT男同志聊天室,聊天室尋夢園,080聊天室,080苗栗人聊天室,6K聊天室,女同志聊天室,小高聊天室,上班族聊天室,080中部人聊天室,同志聊天室,聊天室交友,中部人聊天室,成人聊天室,一夜情聊天室,情色聊天室,寄情築園小遊戲情境坊歡愉用品,情趣用品,成人網站,情人節禮物,情人節,AIO交友愛情館,情色,情色貼圖,情色文學,情色交友,色情聊天室,色情小說,七夕情人節,色情,情色電影,色情網站,辣妹視訊,視訊聊天室,情色視訊,免費視訊聊天,美女視訊,視訊美女,美女交友,美女,情色交友,成人交友,自拍,本土自拍,情人視訊網,視訊交友90739,生日禮物,情色論壇,正妹牆,免費A片下載,AV女優,成人影片,色情A片,成人論壇,情趣,免費成人影片,成人電影,成人影城,愛情公寓,成人影片,保險套,舊情人,微風成人,成人,成人遊戲,成人光碟,色情遊戲,跳蛋,按摩棒,一夜情,男同志聊天室,肛交,口交,性交,援交,免費視訊交友,視訊交友,一葉情貼圖片區,性愛,視訊,視訊聊天,A片,A片下載,免費A片,嘟嘟成人網,寄情築園小遊戲,女同志聊天室,免費視訊聊天室,一夜情聊天室,聊天室

said...

A片,A片,成人網站,成人漫畫,色情,情色網,情色,AV,AV女優,成人影城,成人,色情A片,日本AV,免費成人影片,成人影片,SEX,免費A片,A片下載,免費A片下載,做愛,情色A片,色情影片,H漫,A漫,18成人

a片,色情影片,情色電影,a片,色情,情色網,情色,av,av女優,成人影城,成人,色情a片,日本av,免費成人影片,成人影片,情色a片,sex,免費a片,a片下載,免費a片下載

情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣,情趣用品,情趣用品,情趣,情趣

A片,A片,A片下載,做愛,成人電影,.18成人,日本A片,情色小說,情色電影,成人影城,自拍,情色論壇,成人論壇,情色貼圖,情色,免費A片,成人,成人網站,成人圖片,AV女優,成人光碟,色情,色情影片,免費A片下載,SEX,AV,色情網站,本土自拍,性愛,成人影片,情色文學,成人文章,成人圖片區,成人貼圖

情色,AV女優,UT聊天室,聊天室,A片,視訊聊天室

漢美女 said...

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

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

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

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

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

Anonymous said...

童裝批發
童裝批發
童裝
童裝
酒店喝酒
酒店消費
暑假打工
寒假打工
酒店經紀人
酒店現領
禮服店
酒店小姐
酒店兼職
酒店上班
酒店兼職
禮服店
酒店上班
酒店打工
酒店小姐
酒店經紀
酒店兼差
假日打工
台北酒店經紀
寒暑假打工
專業酒店經紀
酒店藝名
台北酒店
酒店日保
酒店入行
台北禮服店
酒店類型
酒店午場
酒店規定
酒店行政
酒店資訊

tjutrfjutrjirt said...

情趣用品,情趣用品,情趣,情趣,情趣商品,情趣商品,飛機杯,充氣娃娃,自慰套,自慰,電動按摩棒,按摩棒,G點,跳蛋,潤滑液,SM道具,SM,酒店,情趣,情趣內衣,性感睡衣,角色扮演,丁字褲,吊帶襪,巨乳,18禁,美女自拍,美女自拍,免費聊天,免費視訊,一對一視訊,一對多視訊,聊天人緣,談情說愛,成人視訊,影音視訊聊天室,相片,靚辣妹群

teguh wibowo said...

obat kutil kelamin kaskus
lama pengobatan kutil kelamin
obat kutil kelamin mujarab
obat kutil kelamin medis
mengobati kutil kelamin secara medis
obat kutil kelamin paling murah
jual obat kutil kelamin murah
kutil kelamin dan pengobatan nya
obat kutil kelamin tanpa operasi
pengobatan setelah operasi kutil kelamin
pengobatan penyakit kutil kelamin wanita
cara mengobati kutil kelamin pria
mengobati kutil pada kelamin pria
mengobati kutil kelamin dengan propolis
pengobatan kutil pada kelamin
obat kutil kelamin resep dokter
obat kutil kelamin salep
Cara mengobati herpes di kemaluan pri dan wanita
Pengobatan herpes kelamin atau herpes genital ampuh
Obat herpes kulit
Obat herpes simplex
Obat herpes genital
Obat herpes di apotik
Obat herpes kulit di apotik
Obat herpes genital di apotik
Obat herpes untuk bayi
Obat herpes untuk ibu hamil
Obat herpes ampuh
Obat herpes untuk anak
Obat herpes mulut