Oracle 11g新特性之Sql result cache

色婆ijqwj

　　众所周知，内存的存取速度总是比IO快很多，为了解决内存和IO存取速度的差异，oracle做出很多努力，其中包括对于静态小表的caching以及存储到keep pool。
　　在11g之前，这些仅仅局限于数据量小而且不经常改变的小表。但是假如有个大表相对来说它的总体变化量不大，但是随时也会发生数据变化如更新和删除，那么能否有一种方法可以把其执行
　　结果保存下来，等下次再执行相同查询时不需要从再从硬盘读取呢？
　　oracle 11g推出了一个叫Sql result cache能否保存sql的执行结果，以便减少逻辑读。
　　下面做一下实验：
　　client_result_cache_size=0  #通常不需要指定
　　result_cache_mode=MANUAL，需要在sql中指定hint，而不是让oracle自动去判断。
　　SQL> set timing on
　　SQL> select deptno,sum(sal) from scott.emp1 group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   35840000
　　Elapsed: 00:00:02.22
　　Statistics
　　----------------------------------------------------------
　　362  recursive calls
　　0  db block gets
　　490  consistent gets
　　385  physical reads

　　0  redo>　　542  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　5  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　SQL> select /*+ result_cache */ deptno,sum(sal) from scott.emp1 group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   35840000
　　Elapsed: 00:00:00.02
　　Statistics
　　----------------------------------------------------------
　　4  recursive calls
　　0  db block gets
　　80  consistent gets
　　0  physical reads

　　0  redo>　　542  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　SQL> select /*+ result_cache */ deptno,sum(sal) from scott.emp1 group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   35840000
　　Elapsed: 00:00:00.00
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　0  consistent gets
　　0  physical reads

　　0  redo>　　542  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　第一次执行的时候，物理读385次，逻辑读为490次，因为是第一次读取，所以要从硬盘全部读取。
　　第二次使用result_cache hints，第一次使用hints的sql语句也发生了逻辑读80次，这是因为它要把结果保存到cache中。
　　第三次执行的sql语句，逻辑读为0。
　　从sql语句的执行时间可以看到，第一次为2.22秒，第二次为0.02秒。
　　注意，此时emp1表还没发生变化。
　　下面看一下，当emp1表发生变化时，oracle能否保证读取正确的结果。
　　另开启session2，注意时间。
　　13:45:02 ses2>update scott.emp1 set sal=1000 where empno=7934;
　　4096 rows updated.
　　此时，还没有提交sql。
　　13:45:10 ses1>select /*+ result_cache */ deptno,sum(sal) from scott.emp1 group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   35840000
　　Elapsed: 00:00:00.01
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　0  consistent gets
　　0  physical reads

　　0  redo>　　542  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　session1读取的仍然是result cache中的值。
　　session2提交了update语句。
　　13:45:21 ses2>commit;
　　Commit complete.
　　session1中再次执行
　　13:46:16 ses1>select /*+ result_cache */ deptno,sum(sal) from scott.emp1 group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   34611200
　　Elapsed: 00:00:00.11
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　374  consistent gets
　　0  physical reads

　　0  redo>　　543  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　发生了逻辑读，说明cache的数据是被刷新的。
　　再执行上面的查询语句，逻辑读就不见了。
　　那么，如果修改sql语句会如何？
　　13:52:22 ses1>select /*+ result_cache */ deptno,sum(sal) from scott.emp1 t group by deptno;
　　DEPTNO   SUM(SAL)
　　---------- ----------
　　30   38502400
　　20   44544000
　　10   34611200
　　Elapsed: 00:00:00.08
　　Statistics
　　----------------------------------------------------------
　　1  recursive calls
　　0  db block gets
　　374  consistent gets
　　0  physical reads

　　0  redo>　　543  bytes sent via SQL*Net to client
　　419  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　3  rows processed
　　可以看到recursive calls为1，说明oracle把它看作新的sql，又发生了逻辑读374次。
　　熟悉物化视图(Materialized View，以下简称MV)的使用者都知道MV也有查询重写(query rewrite)功能。
　　MV和Result Cache从保存数据结果的角度看很相似，但是两者截然不同。
　　首先，MV把结果记录在硬盘上，是永久的记录，而Result Cache则存储在内存里。
　　当数据库关闭或者result cache空间不足，这些信息会被删除。
　　MV具有静态特性，当query_rewrite_integrity=stale_tolerated时，如果不手动刷新MV，用户通过MV查询到的数据可能会不正确。
　　而Sql Result Cache存储的sql发生变化时，cache刷新是不可避免的。
　　Sql Result Cache不适用的情况：系统表和临时表、sequence.nextval, sequence.currval、sysdate、systimestamp、所有非确定性Pl/Sql函数。
　　Client Query Result Cache
　　前提:
　　使用OCI8的客户端，如C, C++, JDBC-OCI 等
　　优点：
　　-对于开发人员来讲，不需要为了让sql result cache保持一致的数据花更多精力在sql上。
　　-通过存放结果集到客户端的内存里，扩张服务器端的result cache，费用也节省很多。
　　-减少服务器和客户端的round-trip提高性能。
　　-在服务器的数据发生改变的时候，也使用透明的方式维持cache的数据。
　　配置方法：
　　服务器端
　　CLIENT_RESULT_CACHE_SIZE = 1G  #需要重启db
　　客户端
　　sqlnet.ora中写入下内容
　　OCI_RESULT_CACHE_MAX_SIZE    #设置客户端的cache大小
　　OCI_RESULT_CACHE_MAX_RSET_SIZE    #设置结果集的最大大小
　　OCI_RESULT_CACHE_MAX_RSET_ROWS    #设置结果集的最大记录数
　　Client-Side Query Result Cache通常用于不经常发生变化的数据表，当然如果数据发生变更，cache会被刷新）。和SQL Result Cache的区别在于结果集存放在客户端，因此，不需要直接连
　　接到服务器，其结果减少占据网络带宽以及服务器CPU资源的占用。
　　确认方法：
　　select * from client_result_cache_stats$
　　select * from v$client_result_cache_stats