SQL结果缓存

  最近遇到了一个比较头疼的数据库优化问题，业务需要对一个每天有50w条数据加载的维度表进行实时统计分析，并且统计分析的sql是根据界面选择动态生成的（组合情况太多）。最开始考虑写个过程每天晚上预先统计分析，但是情况太多根本没法预先统计。也考虑过改变表设计使用星型模型，发现转换后数据会翻7倍左右，一天的数据就变成近400w，经过测试根本行不通。然后从索引上想办法，使用了四个全文索引，四个bitmap索引，查询有了比较大的提高。接着sga调整、把表顶在内存（keep pool）、使用大页(hugepage)管理、索引开启并发等。最终一周的统计单条sql在1s以内，基本没有物理读，不过这种sql都是比较消耗cpu的，一致性读比较高，在多用户并发下，性能会降低，比较多，使用LoadRunner测试，发现在50用户并发查询时，平均响应时间在12s多。使用nmon发现，性能的瓶颈在cpu。最后没办法，想到oracle 11g有的sql结果缓存的特性，测试效果还算理想。下面大概总结下结果缓存特性的使用，其实很简单。

相关hint：

RESULT_CACHE、NO_RESULT_CACHE

当你设置RESULT_CACHE_MODE为MANUAL时，如果想缓存SQL结果则必须在SQL中添加RESULT_CACHE这个hint.并且如果你这个SQL的结果已经缓存，下次查询时也必须在sql中有RESULT_CACHE这个hint,数据库才会从缓存的结果集中去结果，否则会从基表中查询。

相关的初始化参数：

RESULT_CACHE_MODE

PropertyDescriptionParameter typeStringSyntaxRESULT_CACHE_MODE = { MANUAL | FORCE }Default valueMANUALModifiableALTER SESSION, ALTER SYSTEMBasicNo

RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query's execution plan.

Values:

• MANUAL

  The ResultCache operator is added only when the query is annotated (that is, hints).

• FORCE

  The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).

  Note:

  FORCE mode is not recommended because the database and clients will attempt to cache all queries, which may create significant performance and latching overhead. Moreover, because queries that call non-deterministic PL/SQL functions are also cached, enabling the result cache in such a broad-based manner may cause material changes to the results.

For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.

RESULT_CACHE_MAX_SIZE

PropertyDescriptionParameter typeBig integerSyntaxRESULT_CACHE_MAX_SIZE = integer [K | M | G]Default valueDerived from the values of SHARED_POOL_SIZE, SGA_TARGET, and MEMORY_TARGETModifiableALTER SYSTEMRange of values0 to operating system-dependentBasicNoOracle RACYou must either set this parameter to 0 on all instances to disable the result cache, or use a nonzero value on all instances. Disabling the result cache on some instances may lead to incorrect results.

RESULT_CACHE_MAX_SIZE specifies the maximum amount of SGA memory (in bytes) that can be used by the Result Cache. Values of this parameter greater than 0are rounded up to the next multiple of 32 KB. If the value of this parameter is 0, then the feature is disabled.

相关初始化试图：

V$RESULT_CACHE_DEPENDENCY

V$RESULT_CACHE_MEMORY

V$RESULT_CACHE_OBJECTS

V$RESULT_CACHE_STATISTICS

有人说结果缓存有个缺陷，就是同样的sql，如果执行计划发生变化，可能会有错误的查询结果，这个我还没有验证，待验证。

我做实验发现，对一个表如果执行delete操作，别的用户的查询语句即使加hint（result_cache）结果也是不缓存的。