Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 295

Step 4
Let's check the result cache section and see what has changed.

SQL>SET SERVEROUTPUT ON;
SQL>execute DBMS_RESULT_CACHE.MEMORY_REPORT(TRUE);

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 = 251680K bytes (251680 blocks)
Maximum Result Size = 12584K bytes (12584 blocks)
[Memory]
Total Memory = 207000 bytes [ 0.004% of the Shared Pool ]
... Fixed Memory = 12784 bytes [0.000% of the Shared Pool]
....... Memory Mgr = 200 bytes
....... Bloom Fltr = 2K bytes
....... = 2088 bytes
....... Cache Mgr = 5552 bytes
....... State Objs = 2896 bytes
... Dynamic Memory = 194216 bytes [0.004% of the Shared Pool]
....... Overhead = 161448 bytes
........... Hash Table = 64K bytes (4K buckets)
........... Chunk Ptrs = 62920 bytes (7865 slots)
........... Chunk Maps = 31460 bytes
........... Miscellaneous = 1532 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 23 blocks
........... Used Memory = 9 blocks
............... Dependencies = 2 blocks (2 count)
............... Results = 7 blocks
................... SQL = 7 blocks (1 count)

One execution of the query has changed the memory report. .004% of the dynamic memory section has been
allocated to the result cache section to store 9 blocks of data. What's the math behind the 9 blocks of data? Two blocks
are allocated to the dependent objects that are part of the query ( ORDER_LINE and ORDERS) and 7 blocks are allocated
to the result set of the query. There are 5 columns in the result set all having datatype NUMBER and the result has
300 rows. This amounts to about 5.5 blocks of data, which is rounded to 6 blocks; one additional block is used for the
query metadata.

■ the result cache block size should not be confused with the data block size. as indicated in the report, the
result cache block size is 1K and the database block size is 8K.

Note

A useful view to check on the objects stored in the result cache is the V$RESULT_CACHE_OBJECTS view. It provides
most of the information contained in the result cache, including object dependency and invalidations when the
underlying data or object changes.
The following subset of data from the V$RESULT_CACHE_OBJECTS view gives the basic information pertaining to
the query and its results. The output lists two dependencies and one result. The result also contains information such
as the number of columns and the total number of rows in the result set.