Examine the query:
The RESULT_CACHE_MODE parameter is set to MANUAL for the database.
Which two statements are true about the usage of the result cache?
A. The SQL runtime environment checks whether the query result is cached in the result cache; if the result exists, the optimizer fetches the result from it.
B. The SQL runtime environment does check for the query result in the result cache because the RESULT_CACHE_MODE parameter is set to MANUAL.
C. The SQL runtime environment checks for the query result in the result cache only when the query is executed for the second time.
D. If the query result does not exist in the cache and the query is executed, the result is generated as output, and also sorted in the result cache.
Correct Answer: AD
Explanation/Reference:
Note:
*result_cache_mode: the result cache can be enabled in three ways: via hint, alter session or alter system. Default is MANUAL which means that we need to explicitly request caching via the RESULT_CACHE hint;
*As its name suggests, the query result cache is used to store the results of SQL queries for reuse in subsequent executions. By caching the results of queries, Oracle can avoid having to repeat the potentially time-consuming and intensive operations that generated the resultset in the first place (for example, sorting/aggregation, physical I/O, joins etc). The cache results themselves are available across the instance (i.e. for use by sessions other than the one that first executed the query) and are maintained by Oracle in a dedicated area of memory. Unlike our homegrown solutions using associative arrays or global temporary tables, the query result cache is completely transparent to our applications. It is also maintained for consistency automatically, unlike our own caching programs.
*RESULT_CACHE_MODE specifies when a ResultCache operator is spliced into a query’s execution plan.
Values:
/MANUAL
The ResultCache operator is added only when the query is annotated (that is, hints).
/FORCE
The ResultCache operator is added to the root of all SELECT statements (provided that it is valid to do so).
For the FORCE setting, if the statement contains a NO_RESULT_CACHE hint, then the hint takes precedence over the parameter setting.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
