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

In the output above, a section of the buffer is allocated to a SQL operation, a PL/SQL operation, and an
invalid operation.

Limitations of the Result Cache
Not every feature will be able to do everything possible. Every feature has a few limitations in some of its operation.
Result cache also has few limitations.

1. The primary limitation of the result cache is the fact that the results are stored in cache

only. This means when the instance fails or is shut down for maintenance, the data is
cleared. If it is highly critical that the result sets are permanently stored in the database,
options such as materialized views should be used.

2. Result cache is a memory structure that does not contain any physical data that a

materialized view would contain. Materialized views are database objects, and the result
cache is a memory structure and data is not persistent.

3. If the data changes frequently or is volatile in nature, the caches will have to be invalidated

and rebuilt frequently. This makes the use of result cache feature expensive and can
increase memory management. These frequent invalidations can also create adverse
inconsistent response times.

4. The result cache feature has to be manually used by using the HINT with specific queries.

The AUTO option is available but is not supported.

5. There are a few types of operations that are not supported when using this feature. For

example, queries cannot include or use SQL functions such as CURRENT_DATE,
CURRENT_TIMESTAMP, LOCAL_TIMESTAMP, SYS_CONTEXT, SYS_GUID, SYS_TIMESTEMP,
USERENV , and so on. If they are used, the following type of error occurs:

ERROR at line 1:
ORA-00904: "SYS_TIMESTAMP": invalid identifier
Tables in SYS and SYSTEM schema
Sequence CURRVAL and NEXTVAL pseudo columns

■ When using the result cache feature, Oracle bypasses the buffer cache and transaction layer consistent reads,
so queries will execute fast with less Cpu. this makes buffer cache and other resources available for other operations.

Note

In-Memory Cache
In Oracle Database 12c (12.1.0.2) a new feature is introduced, one that will allow self-optimization of a certain type of
database queries that requires looking at several rows of data at the same time, such as in the case of a data warehouse
operation. While OLTP operations are almost always row-based retrieval, where users requesting one or a few rows of
data, data warehouse operations are range retrievals.
In-memory database cache is a new feature where Oracle will store data in two formats in the buffer: both the
traditional row format (stored in the buffer cache) optimized for OLTP operations and a new columnar format optimized
for analytical functions/data warehouse operations stored in a new cache area called in-memory area . This double format
architecture is transparent to the application accessing the data, meaning no application level changes are required.
The in-memory area is a section of the SGA that has a fixed memory section (Figure 9-5 ) defined by the
parameter INMEMORY_SIZE .