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. What is the best way to ensure that your SQL will not change access paths?
Please select the best answer.
  A. Always use optimizer_mode=choose.
  B. Place an ALTER SESSION command in all production SQL.
  C. Use optimizer_mode=all_rows.
  D. Place a hint after the SELECT statement.

2. Which of the following are ways to override the default optimizer mode? (Choose all that apply)
Please select all the correct answers.
  A. Change the optimizer_mode in the init.ora file.
  B. Use SQL*Plus for your SQL queries.
  C. ALTER SESSION SET OPTIMIZER_GOAL.
  D. Add a hint to the SQL query.

3. Why should you never use the “choose” default optimizer mode?
Please select the best answer.
  A. It can slow down queries by estimating statistics at runtime.
  B. It may choose the wrong optimizer mode.
  C. It has a bad habit of choosing full-table scans.
  D. It is obsolete and only used with Oracle7.

4. What hint would most likely be used by a cost-based OLTP query? Please select the best answer.
  A. All_rows
  B. First_rows
  C. choose
  D. rule