Oracle tuning: Oracle and Expert Systems Technology Oracle Tips by Burleson Consulting

 

SQL Optimizer Mode Defaults Quiz

Question:

What is a way to ensure that your SQL will not change access paths?

1. Always use optimizer_mode=choose

2. Place an ALTER SESSION command in all production SQL

3. Use optimizer_mode=all_rows

4. Place a hint after the SELECT statement

Correct Answer:

D

Explanation:

The correct answer is D, place a hint after the SELECT statement.  This is because a hint for always force the use of the optimizer of your choosing.  Question: What is the best way to ensure that your SQL will not change access paths?  A is incorrect because the “choose” mode is the most unreliable optimizer mode.  B is incorrect because it is not practical to add ALTER SESSION statements to production code.  C is incorrect because the all_rows hint may change the access path as the statistics for the tale and index change.

Question:

Which of the following were listed as ways to override the default optimizer mode? (Choose all that apply)

1. Change the optimizer_mode in the init.ora file

2. Use SQL*Plus for your SQL queries

3. ALTER SESSION SET OPTIMIZER_MODE

4. Add a hint to the SQL query

Correct Answer:

C, D

Explanation:

The correct answers are C, ALTER SESSION SET OPTIMIZER_MODE, and D, add a hint to the SQL query.  These are the only ways to change the default optimizer.  Question: Which of the following were listed as ways to override the default optimizer mode? (Choose all that apply)  A is incorrect because optimizer will change the default, not override it.  B is incorrect because SQL*Plus has no effect on the optimizer mode.

 

Question:

What hint would most likely be used by a cost-based OLTP query?

1. All_rows

2. First_rows_n

3. choose

4. rule

Correct Answer:

B

Explanation:

The correct answer is B, the first_rows_n hint.  This is because first_rows_n will return table rows data faster than the all_rows hint.  Question: What hint would most likely be used by a cost-based OLTP query?  A, C and D are incorrect because they are not the most appropriate for cost-base OLTP queries.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.