Which two statements are true about the SQL Query Result Cache? (Choose two.)
A.
It can store the query results for temporary tables.
B.
It can be set at the system, session, or query level.
C.
It is used only across statements in the same session.
D.
Cached query results become invalid when the data accessed by the query is modified.
B, D.
http://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT1920
RESULT_CACHE Hint on queries
RESULT_CACHE_MODE – Modifiable ALTER SESSION, ALTER SYSTEM
docs.oracle.com/database/121/SQLRF/sql_elements006.htm#SQLRF20004
docs.oracle.com/database/121/REFRN/refrn10270.htm#REFRN10270
SQL Query Result Cache
The SQL query result cache is a subset of the server result cache that stores the results of queries and query fragments. Most applications benefit from this performance improvement. Consider an application that runs the same SELECT statement repeatedly. If the results are cached, then the database returns them immediately. In this way, the database avoids the expensive operation of rereading blocks and recomputing results.
When a query executes, the database searches 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 then stores the result in the result cache. The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of database objects used to construct that cached result.
Users can annotate a query or query fragment with a RESULT_CACHE hint to indicate that the database should store results in the SQL query result cache. The RESULT_CACHE_MODE initialization parameter determines whether the SQL query result cache is used for all queries (when possible) or only for annotated queries.
docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA646
“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” So it’s not A.
B ,D
B.
About the Result Cache
A result cache is an area of memory, either in the Shared Global Area (SGA) or client
application memory, that stores the results of a database query or query block for reuse.
The cached rows are shared across SQL statements and sessions unless they become stale.
Setting the Result Cache Mode
Set the value of the RESULT_CACHE_MODE initialization parameter to determine
the behavior of the result cache. You can set this parameter for the instance (ALTER SYSTEM),
session (ALTER SESSION), or in the server parameter file.
Using SQL Result Cache Hints
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.
http://docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#GUID-433B9410-AA0F-4E03-ACAD-D90DE73F1C85
D.
Understanding How the Server Result Cache Works
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.
docs.oracle.com/database/121/TGDBA/tune_result_cache.htm#TGDBA616