众所周知,内存的存取速度总是比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 size
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 size
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 size
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 size
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 size
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 size
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
转载于:https://blog.51cto.com/happyland/599271