最近遇到了一个比较头疼的数据库优化问题,业务需要对一个每天有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
Property | Description |
---|---|
Parameter type | String |
Syntax | RESULT_CACHE_MODE = { MANUAL | FORCE } |
Default value | MANUAL |
Modifiable | ALTER SESSION , ALTER SYSTEM |
Basic | No |
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 allSELECT
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
Property | Description |
---|---|
Parameter type | Big integer |
Syntax | RESULT_CACHE_MAX_SIZE = integer [K | M | G] |
Default value | Derived from the values of SHARED_POOL_SIZE , SGA_TARGET , and MEMORY_TARGET |
Modifiable | ALTER SYSTEM |
Range of values | 0 to operating system-dependent |
Basic | No |
Oracle RAC | You 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 0
are 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)结果也是不缓存的。