Optimizer Modes   «Prev  Next»

SQL optimizer mode defaults - Quiz

Each question is worth one point. Select the best answer or answers for each question.
1. Which statement about optimizer goal hints and execution plans is correct?
Please select the best answer.
  A. ALL_ROWS guarantees that Oracle will always use the same access path.
  B. FIRST_ROWS(10) permanently fixes the execution plan.
  C. Goal hints influence optimization but do not guarantee an unchanged execution plan.
  D. Every correctly spelled hint is always used by Oracle.

2. Which actions can request a different optimizer goal for a session or an individual query without changing the system-wide default? (Choose all that apply.)
Please select all the correct answers.
  A. Run ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10.
  B. Run the query from SQL*Plus without changing settings or adding hints.
  C. Run ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS_10.
  D. Place an applicable goal hint, such as /*+ ALL_ROWS */, immediately after the SELECT keyword.

3. How should CHOOSE be presented in a course about modern Oracle optimizer modes?
Please select the best answer.
  A. As the recommended default for new applications.
  B. As historical context rather than a supported current OPTIMIZER_MODE choice.
  C. As a mode that always causes a large runtime statistics overhead.
  D. As a mode that modern Oracle automatically selects whenever table statistics are missing.

4. A search screen usually fetches an initial batch of ten rows. Which query hint expresses an optimization goal for that initial batch?
Please select the best answer.
  A. /*+ ALL_ROWS */
  B. /*+ FIRST_ROWS_10 */
  C. /*+ CHOOSE */
  D. /*+ FIRST_ROWS(10) */

5. What provides the strongest evidence that adding an optimizer goal hint improved a query for its application?
Please select the best answer.
  A. The hinted query executes without an error.
  B. The estimated execution plan displays a lower cost.
  C. The hint usage report confirms its use, and comparable executions show an improvement in the required application outcome.
  D. The execution plan contains an index access operation.