15 Tuning the Result Cache

15.1.1.1 Benefits of Using the Server Result Cache

The benefits of using the server result cache depend on the application. OLAP applications can benefit significantly from its use. Good candidates for caching are queries that access a high number of rows but return a small number, such as those in a data warehouse. For example, you can use advanced query rewrite with equivalences to create materialized views that materialize queries in the result cache instead of using tables.

15.1.1.2 Understanding How the Server Result Cache Works

When a query executes, the database searches the cache memory to determine whether the result exists in the result cache. If the result exists, then the database retrieves the result from memory instead of executing the query. If the result is not cached, then the database executes the query, returns the result as output, and stores the result in the result cache.

When users execute queries and functions repeatedly, the database retrieves rows from the cache, decreasing response time. Cached results become invalid when data in dependent database objects is modified.

The following sections contains examples of how to retrieve results from the server result cache:

• How Results are Retrieved in a Query

• How Results are Retrieved in a View

SELECT /*+ RESULT_CACHE */ department_id, AVG(salary)
  FROM hr.employees
 GROUP BY department_id;

--------------------------------------------------------------
| Id | Operation          | Name                       |Rows
--------------------------------------------------------------
| 0 | SELECT STATEMENT    |                            | 11
| 1 |  RESULT CACHE       | 8fpza04gtwsfr6n595au15yj4y |
| 2 |   HASH GROUP BY     |                            | 11
| 3 |    TABLE ACCESS FULL| EMPLOYEES                  | 107
--------------------------------------------------------------

SELECT id, type, creation_timestamp, block_count,
       column_count, pin_count, row_count
  FROM V$RESULT_CACHE_OBJECTS
 WHERE cache_id = '8fpza04gtwsfr6n595au15yj4y';

        ID TYPE       CREATION_ BLOCK_COUNT COLUMN_COUNT  PIN_COUNT  ROW_COUNT
---------- ---------- --------- ----------- ------------ ---------- ----------
         2 Result     06-NOV-11           1            2          0         12

WITH summary AS
( SELECT /*+ RESULT_CACHE */ department_id, avg(salary) avg_sal
    FROM hr.employees
   GROUP BY department_id )
SELECT d.*, avg_sal
  FROM hr.departments d, summary s
 WHERE d.department_id = s.department_id;

---------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                       | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                            |   11 |   517 |     7  (29)| 00:00:01 |
|*  1 |  HASH JOIN            |                            |   11 |   517 |     7  (29)| 00:00:01 |
|   2 |   VIEW                |                            |   11 |   286 |     4  (25)| 00:00:01 |
|   3 |    RESULT CACHE       | 8nknvkh64ctmz94a5muf2tyb8r |      |       |            |          |
|   4 |     HASH GROUP BY     |                            |   11 |    77 |     4  (25)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMPLOYEES                  |  107 |   749 |     3   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | DEPARTMENTS                |   27 |   567 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

15.1.2.1 Benefits of Using the Client Result Cache

OCI drivers, such as OCCI, the JDBC OCI driver, and ODP.NET, support client result caching. Performance benefits of using the client result cache include:

• Reduced query response time

  When queries are executed repeatedly, the application retrieves results directly from the client cache memory, resulting in faster query response time.

• More efficient use of database resources

  The reduction in server round trips may result in substantial performance savings of server resources, such as server CPU and I/O. These resources are freed for other tasks, thereby making the server more scalable.

• Reduced memory cost

  The result cache uses client memory, which may be less expensive than server memory.

15.1.2.2 Understanding How the Client Result Cache Works

The client result cache stores the results of the outermost query, which are the columns defined by the OCI application. Subqueries and query blocks are not cached.

Figure 15-1 illustrates a client process with a database login session. This client process has one client result cache shared amongst multiple application sessions running in the client process. If the first application session runs a query, then it retrieves rows from the database and caches them in the client result cache. If other application sessions run the same query, then they also retrieve rows from the client result cache.

Figure 15-1 Client Result Cache

Description of "Figure 15-1 Client Result Cache"

The client result cache transparently keeps the result set consistent with session state or database changes that affect it. When a transaction changes the data or metadata of database objects used to build the cached result, the database sends an invalidation to the OCI client on its next round trip to the server.

15.2.1.1 Sizing the Server Result Cache Using Initialization Parameters

Table 15-1 lists the database initialization parameters that control the server result cache.

To change the memory allocated to the server result cache: 

• Set the value of the RESULT_CACHE_MAX_SIZE initialization parameter to the desired size.

  In an Oracle Real Application Clusters (Oracle RAC) environment, the result cache is specific to each database instance and can be sized differently on each instance. However, invalidations work across instances. To disable the server result cache in a cluster, you must explicitly set this parameter to 0 for each instance startup.

15.2.1.2 Managing the Server Result Cache Using DBMS_RESULT_CACHE

The DBMS_RESULT_CACHE package provides statistics, information, and operators that enable you to manage memory allocation for the server result cache. Use the DBMS_RESULT_CACHE package to perform operations such as retrieving statistics on the cache memory usage and flushing the cache.

This section describes how to manage the server result cache using the DBMS_RESULT_CACHE package and contains the following topics:

• Viewing Memory Usage Statistics for the Server Result Cache

• Flushing the Server Result Cache

SQLSET SERVEROUTPUT ON
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 = 1024 bytes
Maximum Cache Size = 950272 bytes (928 blocks)
Maximum Result Size = 47104 bytes (46 blocks)
[Memory]
Total Memory = 46340 bytes [0.048% of the Shared Pool]
... Fixed Memory = 10696 bytes [0.011% of the Shared Pool]
... State Object Pool = 2852 bytes [0.003% of the Shared Pool]
... Cache Memory = 32792 bytes (32 blocks) [0.034% of the Shared Pool]
....... Unused Memory = 30 blocks
....... Used Memory = 2 blocks
........... Dependencies = 1 blocks
........... Results = 1 blocks
............... SQL = 1 blocks

PL/SQL procedure successfully completed.

15.2.4.1 Read Consistency Requirements

For a snapshot to be reusable, it must have read consistency. For a result set to be eligible for caching, at least one of the following conditions must be true:

• The read-consistent snapshot used to build the result must retrieve the most current, committed state of the data.

• The query points to an explicit point in time using flashback query.

If the current session has an active transaction referencing objects in a query, then the results from this query are not eligible for caching.

15.2.4.2 Query Parameter Requirements

Cache results can be reused if they are parameterized with variable values when queries are equivalent and the parameter values are the same. Different values or bind variable names may cause cache misses. Results are parameterized if any of the following constructs are used in the query:

• Bind variables

• The SQL functions DBTIMEZONE, SESSIONTIMEZONE, USERENV/SYS_CONTEXT (with constant variables), UID, and USER

• NLS parameters

15.2.4.3 Restrictions for the Result Cache

Results cannot be cached when the following objects or functions are in a query:

• Temporary tables and tables in the SYS or SYSTEM schemas

• Sequence CURRVAL and NEXTVAL pseudo columns

• SQL functions CURRENT_DATE, CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, USERENV/SYS_CONTEXT (with non-constant variables), SYS_GUID, SYSDATE, and SYS_TIMESTAMP

The client result cache has additional restrictions for result caching.

15.3.1.1 Using the RESULT_CACHE Hint

When the result cache mode is MANUAL, the /*+ RESULT_CACHE */ hint instructs the database to cache the results of a query block and to use the cached results in future executions.

Example 15-5 shows a query that uses the RESULT_CACHE hint.

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

15.3.1.2 Using the NO_RESULT_CACHE Hint

The /*+ NO_RESULT_CACHE */ hint instructs the database not to cache the results in either the server or client result caches.

Example 15-6 shows a query that uses the NO_RESULT_CACHE hint.

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

15.3.1.3 Using the RESULT_CACHE Hint in Views

The RESULT_CACHE hint applies only to the query block in which the hint is specified. If the hint is specified only in a view, then only these results are cached. View caching has the following characteristics:

• The view must be one of the following types:

  • A standard view (a view created with the CREATE ... VIEW statement)

  • An inline view specified in the FROM clause of a SELECT statement

  • An inline view created with the WITH clause

• The result of a view query with a correlated column (a reference to an outer query block) cannot be cached.

• Query results are stored in the server result cache, not the client result cache.

• A caching view is not merged into its outer (or referring) query block.

  Adding the RESULT_CACHE hint to inline views disables optimizations between the outer query and inline view to maximize reusability of the cached result.

Example 15-7 shows a query of the inline view view1.

SELECT *
  FROM ( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count
           FROM hr.employees 
          GROUP BY department_id, manager_id ) view1
 WHERE department_id = 30;

Assume that the same session run a query of the view view2 as shown in Example 15-8.

WITH view2 AS
( SELECT /*+ RESULT_CACHE */ department_id, manager_id, count(*) count
    FROM hr.employees 
   GROUP BY department_id, manager_id ) 
SELECT *
  FROM view2 
 WHERE count BETWEEN 1 and 5;

15.3.2.1 Using the DEFAULT Table Annotation

The DEFAULT table annotation prevents the database from caching results at the table level.

Example 15-9 shows a CREATE TABLE statement that uses the DEFAULT table annotation to create a table sales and a query of this table.

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

SELECT prod_id, SUM(amount_sold)
  FROM sales 
 GROUP BY prod_id 
 ORDER BY prod_id;

15.3.2.2 Using the FORCE Table Annotation

The FORCE table annotation forces the database to cache results at the table level.

Using the sales table created in Example 15-9, assume that you decide to force result caching for this table, you can do so by using the FORCE table annotation.

Example 15-10 shows an ALTER TABLE statement that uses the FORCE table annotation on the sales table.

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;

This example includes two queries of the sales table. The first query, which is frequently used and returns few rows, is eligible for caching because of the table annotation. The second query, which is a one-time query that returns many rows, uses a hint to prevent result caching.