You are administering a database, where an application frequently executes identical SQL statements with the same syntax.
How will you optimize the query results without retrieving data blocks from the storage?
A. By setting the CURSOR_SHARING parameter to FORCE.
B. By using the bind variables and setting the CURSOR_SHARING parameter to EXACT.
C. By using the CACHE hint to pin the queries in the library cache
D. By ensuring that RESULT_CACHE_MODE parameter is set to MANUAL and using the RESULT_CACHE hint in the queries.
E. By creating a SQL plan baseline for the identical statements.
Correct Answer: D
Explanation/Reference:
As its name suggests, the query result cache is used to store the results of SQL queries for re-use 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.
Note:
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.
Incorrect:
A, B:CURSOR_SHARING determines what kind of SQL statements can share the same cursors.
Values:
FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
EXACT
Only allows statements with identical text to share the same cursor.
C:The Oracle library cache is a component of the System Global Area (SGA) shared pool.
Similarly to other Oracle
cache structures, the point of the library cache is to reduce work - and therefore to improve
performance - by caching the result of parsing and optimizing SQL or PL/SQL so that subsequent executions of the
same SQL or
PL/SQL require fewer preparatory steps to deliver a query result.
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
