Oracle 11g新特性之Sql result cache
众所周知，内存的存取速度总是比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
----------------------------------------------------------
362recursive calls
0db block gets
490consistent gets
385physical reads
0redo> 542bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
5sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
4recursive calls
0db block gets
80consistent gets
0physical reads
0redo> 542bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
0recursive calls
0db block gets
0consistent gets
0physical reads
0redo> 542bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
0recursive calls
0db block gets
0consistent gets
0physical reads
0redo> 542bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
0recursive calls
0db block gets
374consistent gets
0physical reads
0redo> 543bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
----------------------------------------------------------
1recursive calls
0db block gets
374consistent gets
0physical reads
0redo> 543bytes sent via SQL*Net to client
419bytes received via SQL*Net from client
2SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
3rows 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
页:
[1]