转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/12657479
![[每日一题] 11gOCP 1z0-053 :2013-10-12 RESULT_CACHE在哪个池？.............................44_11gOCP](http://img.blog.csdn.net/20131012215640281?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvZ3VveUpvZQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
正确答案：B
Oracle 11g 新特性：Result Cache ，它是存在SHARED POOL中，作用SQL提高性能,如果结果缓存起来，下次查询直接就返回数据，无须再解析、执行SQL了。大大提高了性能。但缺点是需要在共享池留一块空间出来。
结果集缓存是Oracle 11g新引入的功能，除了可以在服务器端缓存结果之外，还可以在客户端缓存结果。服务器端的Result Cache Memory由两部分组成。
■SQL Query Result Cache：存储SQL查询的结果集。
■PL/SQL Function Result Cache：用于存储过程PL/SQL函数的结果集。
Oracle通过一个新引入初始化参数来控制该Cache的大小（result_cache_max_size）
sys@OCM> show parameter result NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 result_cache_max_result integer 5 result_cache_max_size big integer 3360K result_cache_mode string MANUAL result_cache_remote_expiration integer 0
如果result_cache_max_size=0则表示禁用该特性。参数result_cache_max_result则控制单个缓存结果可以占总的Server Result Cache大小的百分比。
上面显示的参数中result_cache_mode用于控制Server Result Cache的模式，该参数有3个可选设置。
● result_cache_mode=AUTO：则优化器自动判断是否将查询结果缓存。
●result_cache_mode=MANUAL：则需要通过查询提示来告诉优化器是否缓存结果。
●result_cache_mode=FORCE：则尽可能地缓存查询结果，通过提示：no_result_cache可以拒绝缓存。
Ok，下面我们来测Result Cache的性能。
(1)当前我的result_cache_mode为manual，如上所说result_cache_mode=MANUAL：则需要通过查询提示来告诉优化器是否缓存结果。
sys@OCM> show parameter result NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ client_result_cache_lag big integer 3000 client_result_cache_size big integer 0 result_cache_max_result integer 5 result_cache_max_size big integer 3360K result_cache_mode string MANUAL result_cache_remote_expiration integer 0
（2）在没有缓存结果的况情下的测试
gyj@OCM> set autot traceonly; gyj@OCM> select count(*) from t1; Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 2 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
gyj@OCM> set autot traceonly; gyj@OCM> select count(*) from t1; Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| T1 | 2 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
不管你执行几次select count(*)from t1;，它的consistent gets值都为6.从下面可以看出逻辑读是6（逻辑读= 当前读db block gets+一致性读consistent gets）
（2）在有缓存结果的况情下的测试
gyj@OCM> select /*+ result_cache */ count(*) from t1; Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | 83r168smxh22sgfhqrq2u524uv | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T1 | 2 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(GYJ.T1); attributes=(single-row); name="select /*+ result_cache */ count(*) from t1" Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
gyj@OCM> select /*+ result_cache */ count(*) from t1; Execution Plan ---------------------------------------------------------- Plan hash value: 3724264953 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | RESULT CACHE | 83r168smxh22sgfhqrq2u524uv | | | | | 2 | SORT AGGREGATE | | 1 | | | | 3 | TABLE ACCESS FULL| T1 | 2 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Result Cache Information (identified by operation id): ------------------------------------------------------ 1 - column-count=1; dependencies=(GYJ.T1); attributes=(single-row); name="select /*+ result_cache */ count(*) from t1" Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 526 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
看到了吗，第二次执行consistent gets=0，整个逻辑读为0，说明没有执行SQL。发出SQL语句直接从共享池的Result Cache（结果集缓）存中取数据返回给客户端。
一道OCP的题目就引出了好多东西，这道题原本只是考结果缓存池在哪？实际了我们不但要知道它在共享池shared pool中，更重要的，我们要知道它的作用，Result Cache（结果集缓）是提高SQL的查询性能。哈哈。。OCP题库确实是个好东西啊，读懂每道题，转化为自己的知识，1000多道这样折腾下来，你的理论知识不扎实都不可能了。
QQ：252803295
技术交流QQ群：
DSI&Core Search Ⅰ 群：127149411（2000人技术群:未满）
DSI&Core Search Ⅱ 群：177089463（1000人技术群:未满）
DSI&Core Search Ⅲ 群：284596437（500人技术群:未满）
DSI&Core Search Ⅳ 群：192136702（500人技术群:未满）
DSI&Core Search Ⅴ 群：285030382（500人闲聊群:未满）
MAIL：dbathink@hotmail.com
BLOG： http://blog.csdn.net/guoyjoe
WEIBO：http://weibo.com/guoyJoe0218
ITPUB： http://www.itpub.net/space-uid-28460966.html
OCM： http://education.oracle.com/education/otn/YGuo.HTM
ACONG: http://www.acoug.org/category/membership
