性能的救赎 - Oracle Server Result Cache

Server Cache 介绍及工作原理       

        Oracle 11g 版本提供了一个新的独立的共享内存区域来缓存被应用程序频繁使用的结果集。我们称之为Server Result Cache. OLTP应用通过使用ServerResult Cache可以获得很大的性能提升。简单来说，Server Result Cache 允许一个查询结果集/函数返回值在内存中进行缓存，如果之后的查询/函数试图获得同样的结果集，那Oracle就会直接从缓存中读取，而避免了不必要的I/O.

         Server Result Cache 是一个内存 区域，这个内存区域存在于SGA内。更进一步说，ServerResult Cache是存在于SharedPool 中的一块内存区域，这意味着被缓存的内容可以被不同的Session共享访问。如 下图所示Server Result Cache可细分为两类，一类是SQL Query Result Cache，是用来缓存SQL的查询结果。另一类是PL/SQL function result cache，是用来缓存PL/SQL 函数返回的结果。

        Server Result Cache 的工作原理就是每当执行一个查询时，数据库会首先在缓存区查看是否有已经缓存的结果，如果有，将直接从缓存中读取结果，而不是执行查询。如果所需要的结果没有被缓存，那数据库就会执行这个查询，返回结果，并将结果缓存已被其他后续查询使用。当用户重复执行一个查询或者函数时，数据库会从缓存中获取结果，这样就大大降低了反应时间。缓存的结果在所依赖的数据对象发生变化时将变为无效状态。

SQL Query Result Cache

       常规实现方法就是在SQL 查询语句中 加入RESULT_CACHE hint。除此之外，我们还可以通过设定数据库参数RESULT_CACHE_MODE（MANUAL,FORCE,DEFAULT）或者Table annotation,即在创建表时指定 RESULT_CACHE (MODE DEFAULT|FORCE) 子句 。这几种方式中，hint的方式是优先级最高的，其次是Table Annotation,最后是RESULT_CACHE_MODE参数。让我们看一看加了RESULT_CACHE hint 的SQL Query 运行的情况吧。执行如下 SQL（ 这里的SQL和函数所用到的表均来自Oracle自带的HR Schema），这个SQL试图统计每个部门的平均工资。

explain plan for select /*+ result_cache +*/    department_id, avg(salary)     from hr.employees    group by department_id;

SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY());

 从上面的执行计划中，我们很清楚的看到Result Cache被使用了。 Oracle 使用字典表 V$RESULT_CACHE_OBJECTS 来存储这些缓存结果集的信息。

select TYPE,STATUS,NAME,CACHE_ID from V$RESULT_CACHE_OBJECTS where UPPER(status) = 'PUBLISHED';

通过上面的输出结果，我们可以看出Oracle维护了这个SQL查询所依赖的底层对象。那一旦底层对象发生了变化，Oracle会自动将缓存的查询结果集设置为无效状态。

但Oracle检测底层对象的变化也只能到对象的级别，它无法精确定位到缓存的SQL 查询结果集所依赖的数据的变化。例如，我们另起一个Session，执行如下语句

update employees set first_name = 'Matt',last_name = 'Zhang' where employee_id = 100;
commit;

这里，我们只更新了某个employee的名字，这本身与统计平均工资的查询没有太大的关系。那么，再回头查V$RESULT_CACHE_OBJECTS字典表，我们发现原来缓存的那个查询结果已经变成了Invalid。

应用场景

• 应用Result Cache的SQL 查询是需要重复大量的执行的。
• SQL 查询需要检索大量数据，但只返回满足要求的小部分数据。尤其是当SQL查询中存在例如Group by ,Count,Average 等分组/计算函数/自定义函数的时候。
• SQL 查询所面向的数据不会经常变化。

使用限制

如果SQL查询中包含如下情况，查询结果将不被缓存

• 临时表
• 字典表
• Non-deterministic PL/SQL 函数 (指对于同一个/组参数值，函数的返回结果是变化的。查看Oracle Deterministic 关键字)
• Curval 和Nextval 伪函数
• SYSDATE,SYS_TIMESTAMP,CURRENT_DATE,CURRENT_TIMESTAMP,LOCAL_TIMESTAMP,USERENV,SYS_CONTEXT,和SYS_QUID 函数。

我这里举一个简单的例子，就那SYSDATE来作为Where条件来查询结果，看是否能被Oracle 缓存。

select /*+ result_cache +*/
    department_id, avg(salary)
    from hr.employees
    where hire_date > (sysdate - 365*8)
   group by department_id;

首先说明这条SQL的查询肯定是有返回结果的。

那我们看看这条SQL的执行计划，看其是否被缓存了。

explain plan for select /*+ result_cache +*/     department_id, avg(salary)     from hr.employees    where hire_date > (sysdate - 365*8)    group by department_id;
   
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY());   

很明显，上面这条加了sysdate的SQL查询并没有被Oracle缓存。

PL/SQL Function Result Cache

Function Result Cache 和Query Result Cache 共享一个ResultCache 内存区域。基于Function的结果集缓存需要通过显式的声明，加上RESULT_CACHE关键字。方式如下

create or replace
function is_manager (p_emp_id IN employees.employee_id%TYPE)
RETURN BOOLEAN
RESULT_CACHE
IS
    manager_count NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Manager status confirmation in progress.');
    SELECT  COUNT(1)
    INTO    manager_count
    FROM    departments
    WHERE   MANAGER_ID = p_emp_id;
    IF manager_count > 0 THEN
         RETURN (TRUE);
    ELSE
         RETURN (FALSE);
    END IF;
    
END is_manager;

我们通过如下匿名块来执行上面的函数

set serveroutput on;

declare
l_emp_id NUMBER;
begin
l_emp_id := 99;

IF is_manager(p_emp_id => l_emp_id) THEN
 DBMS_OUTPUT.PUT_LINE('I am a manager.');
ELSE
DBMS_OUTPUT.PUT_LINE('I am not a manager.');
END IF;

end;

执行完后的输出结果为

anonymous block completed
Manager status confirmation in progress.
I am not a manager.

执行完后，让我们回头看看 Oracle的字典表吧

select rco.type,rco.status,rco.name,rco.cache_id,rco.cache_key,rco_dependency.type dependent_type,rco_dependency.status dependent_status,rco_dependency.name dependent_name from V$RESULT_CACHE_OBJECTS rco
join
V$RESULT_CACHE_DEPENDENCY rcd
on rcd.result_id = rco.id
join V$RESULT_CACHE_OBJECTS rco_dependency
on
rcd.depend_id = rco_dependency.id

我们能看到Oracle缓存了一个结果集，这个存储结果的依赖对象是Departments表和 Is_manager 函数本身。也就是这两个对象中任何一个发生变化，Oracle都会将对应的Function Result Cache 设置为无效。

 

当同一个函数被Oracle Instance 上的不同Session执行时，如果传入的参数值完全相同，Oracle将试图从缓存中直接获得结果。这意味着对于函数 的Result Cache，不同的参数值组合将对应不同的结果集。我们试着执行上面的函数两次，第一次的输入参数为l_emp_id := 99;  ，那么第二次的输入参数为l_emp_id := 100;那Oracle 会缓存两份返回结果, 这两份结果的依赖对象是一样的。

RELIES_ON 子句

我们看到Oracle的依赖管理已经很好的为我们找到了某个缓存的函数结果集所依赖的底层对象。但当一个函数变得复杂的时候，即使Oracle能够帮我们维护这种依赖关系，那也是费老劲了。一个比较简单的方式是我们显示地指明某个函数所依赖的对象。这个关键字就是RELIES_ON 子句。

create or replace
function is_manager (p_emp_id IN employees.employee_id%TYPE)
RETURN BOOLEAN
RESULT_CACHE RELIES_ON (departments)
IS
    manager_count NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Manager status confirmation in progress.');
    SELECT  COUNT(1)
    INTO    manager_count
    FROM    departments
    WHERE   MANAGER_ID = p_emp_id;

    IF manager_count > 0 THEN
         RETURN (TRUE);
    ELSE
         RETURN (FALSE);
    END IF;
    
END is_manager;

应用场景

数据库使用频繁的函数，并且这些函数所依赖的数据很少发生变化。同时，函数本身不能具备如下条件的情况下

• 当函数使用Invoker rights ( 使用调用该函数的用户权限执行) 方式执行时(通过AUTHID子句实现)。默认情况下，函数的执行方式为Definer rights （使用定义该函数的用户权限执行，而不管调用函数的用户是否为函数的所有者，还是执行者）。
• 函数有OUT 或IN OUT 参数时
• 函数的IN 参数是BLOB, Collection, Object 或Record 时
• 函数的RETURN 的数据类型是BLOB,Ref Cursor,Object，或者是任何上述类型的集合。

注意事项

在决定一个函数是否需要被缓存前，请检查一下下面几个注意事项。

• 当函数在执行过程中对数据进行更改时不适用。如果这种函数被缓存，后续的任何函数调用都不再执行，很显然这时候你的数据更新操作就不会发生了。
• 当函数除数据查询外，还做了诸如调用系统函数，如UTL_MAIL()，以完成一些特定功能的时不适用。
• 当函数执行过程中使用了一些Session级别的默认设置时，不适用。例如调用TO_CHAR函数对日期类型进行格式转换，如果没有指定目标转换格式的话，那将用Session 的 NLS_DATE_FORMAT参数的设定。因为Function Result Cache是跨 Session共享的，所以一旦缓存，某个Session可能会得到错误的返回结果。

总结

Oracle Server Result Cache 为基于Oracel数据库的管理信息系统提供了一个便利的，有效的性能提升途径。但作为程序员我们必须得清楚什么时候可以用，什么时候不能 用。只有这样，你的系统才能收获这个功能所带来的好处。除了Oracle Server Result Cache 外，Oracle 还提供了Oracle Client Result Cache,全称叫 Oracle Call Interface (OCI) client result cache. 这是一个存在于Client端进程中的内存区，主要为基于OCI 驱动的应用程序 缓存SQL 查询的结果,从而避免了数据库服务器到客户端的网络通讯负载，降低访问时间。如果有兴趣，可参阅Oracle相关文档。