本文共 2491 字，大约阅读时间需要 8 分钟。
Oracle 12c 监控 Result Cache的使用
从下面这个ALTER TABLE说起：
ALTER TABLE sales RESULT_CACHE (MODE FORCE);
这条SQL语句是什么意思？
这里使用 FORCE table annotation 强制数据库在表级别缓存查询结果。
例如：
ALTER TABLE sales RESULT_CACHE (MODE FORCE);SELECT prod_id, SUM(amount_sold) FROM sales GROUP BY prod_id HAVING prod_id=136;SELECT /*+ NO_RESULT_CACHE */ * FROM sales ORDER BY time_id DESC;
上面的查询中，第一个SQL查询会缓存查询结果；但是下面的第二个SQL不会使用 Result Cache特性。
Oracle 12c 中与 Server端和Client端监控相关的系统视图、动态性能视图如下：
Views and Tables with Information About the Result Cache
| View/Table | Description |
|---|---|
|
| Lists various server result cache settings and memory usage statistics. |
|
| Lists all the memory blocks in the server result cache and their corresponding statistics. |
|
| Lists all the objects whose results are in the server result cache along with their attributes. |
|
| Lists the dependency details between the results in the server result cache and dependencies among these results. |
|
| Stores cache settings and memory usage statistics for the client result caches obtained from the OCI client processes. This statistics table contains entries for each client process that uses result caching. After the client processes terminate, the database removes their entries from this table. The client table contains information similar to |
|
| Contains a |
COLUMN name FORMAT a20SELECT name, value FROM V$RESULT_CACHE_STATISTICS;
SQL> COLUMN name FORMAT a20SQL> SELECT name, value 2 FROM V$RESULT_CACHE_STATISTICS;NAME VALUE-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------Block Size (Bytes) 1024Block Count Maximum 12384Block Count Current 2528Result Size Maximum 619(Blocks)Create Count Success 19Create Count Failure 0Find Count 48Invalidation Count 2Delete Count Invalid 0Delete Count Valid 0Hash Chain Length 0-1Find Copy Count 44Latch (Share) 0已选择 13 行。已用时间: 00: 00: 00.11
Client端监控：
SELECT stat_id, SUBSTR(name,1,20), value, cache_id FROM CLIENT_RESULT_CACHE_STATS$ ORDER BY cache_id, stat_id;
转载地址：http://yytdi.baihongyu.com/