tuning: Oracle and Expert Systems Technology
Oracle Tips by Burleson Consulting
Mode Defaults Quiz
a way to ensure that your SQL will not change access paths?
Place an ALTER
SESSION command in all production SQL
Place a hint
after the SELECT statement
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.
the following were listed as ways to override the default optimizer
mode? (Choose all that apply)
optimizer_mode in the init.ora file
Use SQL*Plus for
your SQL queries
Add a hint to
the SQL query
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
would most likely be used by a cost-based OLTP query?
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
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.