RESULT_CACHE管理

SQL> select * from v$result_cache_statistics

        ID NAME                                               VALUE

---------- -------------------------------------------------- --------------------------------------------------
         1 Block Size (Bytes)                                 1024
         2 Block Count Maximum                                104864
         3 Block Count Current                                104864
         4 Result Size Maximum (Blocks)                       5243
         5 Create Count Success                               3921627
         6 Create Count Failure                               356
         7 Find Count                                         3049056
         8 Invalidation Count                                 484024
         9 Delete Count Invalid                               485725
        10 Delete Count Valid                                 3348069
        11 Hash Chain Length                                  16-26

11 rows selected.

查看CACHE具体信息，可以通过cache_id查看具体SQL的cache信息

SELECT ID, TYPE, CREATION_TIMESTAMP, BLOCK_COUNT, COLUMN_COUNT,
PIN_COUNT, ROW_COUNT
FROM V$RESULT_CACHE_OBJECTS

SQL> set serveroutput on
SQL> execute dbms_result_cache.memory_report;
R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 104864K bytes (104864 blocks)
Maximum Result Size = 5243K bytes (5243 blocks)
[Memory]
Total Memory = 107886464 bytes [1.256% of the Shared Pool]
... Fixed Memory = 164776 bytes [0.002% of the Shared Pool]
... Dynamic Memory = 107721688 bytes [1.254% of the Shared Pool]
....... Overhead = 340952 bytes
....... Cache Memory = 104864K bytes (104864 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 104864 blocks
............... Dependencies = 12247 blocks (1110 count)
............... Results = 92617 blocks
................... SQL     = 92617 blocks (87854 count)

PL/SQL procedure successfully completed.

EXECUTE DBMS_RESULT_CACHE.FLUSH;--清理缓存

相关HINTS:

SELECT /*+ RESULT_CACHE */ prod_id, SUM(amount_sold)
FROM sales
GROUP BY prod_id
ORDER BY prod_id;

SELECT /*+ NO_RESULT_CACHE */ prod_id, SUM(amount_sold)
FROM sales
GROUP BY prod_id
ORDER BY prod_id;

使用表级别result_cache

CREATE TABLE sales (...) RESULT_CACHE (MODE DEFAULT);

RESULT_CACHE相关视图：

V$RESULT_CACHE_STATISTICS Lists various server result cache settings and memory
usage statistics.
V$RESULT_CACHE_MEMORY Lists all the memory blocks in the server result cache and
their corresponding statistics.
V$RESULT_CACHE_OBJECTS Lists all the objects whose results are in the server result
cache along with their attributes.
V$RESULT_CACHE_DEPENDENCY Lists the dependency details between the results in the
server cache and dependencies among these results.
CLIENT_RESULT_CACHE_STATS$ Stores cache settings and memory usage statistics for the
client result caches obtained from the OCI client
processes. This statistics table has entries for each client
process that is using result caching. After the client
processes terminate, the database removes their entries
from this table. The client table lists information similar
to V$RESULT_CACHE_STATISTICS.
See Also: Oracle Database Reference for details about
CLIENT_RESULT_CACHE_STATS$
DBA_TABLES, USER_TABLES,ALL_TABLES
Includes a RESULT_CACHE column that shows the result
cache mode annotation for the table. If the table has not
been annotated, then this column shows DEFAULT. This
column applies to both server and client result caching.