SQL结果缓存

面对大量数据的实时统计分析需求,通过调整索引、内存管理等手段优化数据库性能,但多用户并发下CPU成为瓶颈。采用Oracle 11g的SQL结果缓存特性,通过RESULT_CACHE Hint和相关初始化参数配置,有效提升查询效率。注意,当执行计划变化或特定操作(如DELETE)时,结果缓存可能不生效,需谨慎使用。

  最近遇到了一个比较头疼的数据库优化问题,业务需要对一个每天有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

PropertyDescription
Parameter typeString
SyntaxRESULT_CACHE_MODE = { MANUAL | FORCE }
Default valueMANUAL
ModifiableALTER SESSIONALTER SYSTEM
BasicNo

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

PropertyDescription
Parameter typeBig integer
SyntaxRESULT_CACHE_MAX_SIZE = integer [K | M | G]
Default valueDerived from the values of SHARED_POOL_SIZESGA_TARGET, and MEMORY_TARGET
ModifiableALTER SYSTEM
Range of values0 to operating system-dependent
BasicNo
Oracle 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)结果也是不缓存的。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值