[每日一题] 11gOCP 1z0-053 :2013-10-12 RESULT_CACHE在哪个池?.............................44
来源:互联网 发布:js字符串match的用法 编辑:程序博客网 时间:2024/04/29 20:36
转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/12657479
正确答案: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 resultNAME TYPE VALUE------------------------------------ ----------- ------------------------------client_result_cache_lag big integer 3000client_result_cache_size big integer 0result_cache_max_result integer 5result_cache_max_size big integer 3360Kresult_cache_mode string MANUALresult_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 resultNAME TYPE VALUE------------------------------------ ----------- ------------------------------client_result_cache_lag big integer 3000client_result_cache_size big integer 0result_cache_max_result integer 5result_cache_max_size big integer 3360Kresult_cache_mode string MANUALresult_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)1rows 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)1rows 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)1rows 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
- [每日一题] 11gOCP 1z0-053 :2013-10-12 RESULT_CACHE在哪个池?.............................44
- [每日一题] 11gOCP 1z0-053 :2013-10-1 persistent lightweight jobs...........................11
- [每日一题] 11gOCP 1z0-053 :2013-10-11 Flashback Data Archive属性.........................43
- [每日一题] 11gOCP 1z0-053 :2013-10-10 materialized view---- online redefinition ...................35
- [每日一题] 11gOCP 1z0-053 :2013-10-2 virtual private catalogs.......................................13
- [每日一题] 11gOCP 1z0-053 :2013-10-4 Database Replay feature..................................17
- [每日一题] 11gOCP 1z0-053 :2013-10-5 SQL Access Advisor.........................................23
- [每日一题] 11gOCP 1z0-053 :2013-10-7 the backup of MULT_DATA................................32
- [每日一题] 11gOCP 1z0-053 :2013-10-13 MEMORY_TARGE初始化参数..................................51
- [每日一题] 11gOCP 1z0-053 :2013-10-15 duplexing of the backups............................60
- [每日一题] 11gOCP 1z0-053 :2013-09-29 Flashback Data Archive ...................................6
- [每日一题] 11gOCP 1z0-053 :2013-09-30 ASMCMD.......................................................8
- [每日一题] 11gOCP 1z0-052 :2013-09-12 SELECT_CATALOG_ROLE角色....................................A68
- [每日一题] 11gOCP 1z0-052 :2013-09-10 ABOUT ALERTS...............................................A50
- [每日一题] 11gOCP 1z0-053 :2013-10-3 large ASM files--- variable extent size....................15
- [每日一题] 11gOCP 1z0-053 :2013-10-6 significance of the CHANGE FAILURE ..................25
- [每日一题] 11gOCP 1z0-053 :2013-10-8 attribute for an individual disk group.....................73
- [每日一题] 11gOCP 1z0-053 :2013-10-9 backup with the KEEP option....................................33
- 几种进程间的通信方式
- emacs命令表
- Java知识点。
- 去除MDI子窗体最大化时的默认图标
- win8 DCOM 中不存在WORD、EXCEL等OFFICE组件解决方案
- [每日一题] 11gOCP 1z0-053 :2013-10-12 RESULT_CACHE在哪个池?.............................44
- 初学bash,test学习遇到的问题,希望以后能找到答案
- Mybatis 中 $ 与 # 的区别
- 编写安全代码——小心有符号数的右移操作
- 软考学习--PV操作一--概念解析
- C++学习之旅——结构体和联合体的区别,以及数据对齐方式影响内存大小
- red hat 企业版更新源的添加
- 解读Python内存管理机制
- c++词法分析器