True or False – Optimizer Mode and Tuning Goals

Continuing the true or false blog article series with more questions that seem to have conflicting answers. Today’s quiz is on the topic of the optimizer mode and tuning goals, inspired by a recent thread that I found in a discussion forum. A Google search found a couple of related articles.

When answering the questions, answer True or False, followed by a justification for the answer that you provided.

5. An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:

ALTER SESSION SET OPTIMIZER GOAL = RULE;

6. The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.

7. A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that than a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.

8. The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.

9. The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.

10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins. Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.

11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.

12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.

13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.

14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.

—

I fear that this blog series might be falsely inflating the Google ranking of the Oracle documentation library due to the number of times the documentation library has been referenced – sorry Google users. 🙂

Actions

Information

5 responses

13052010

Narendra(06:10:35) :

1. As of Oracle 10g R1, there are three methods for joining tables or other row sources: star transformation join, nested loops join, and hash join.FALSE. They are sort merge join, nested loops join, and hash join
2. SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.TRUE. Smallest result set means less number of iterations in case of NESTED LOOP join; small hash table to probe in case of HASH JOIN and small data to sort in case of SORT MERGE join
3. Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints. FALSE for SQL Profiles and TRUE for SQL Access Advisor
4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.FALSE. It offers RULE, FIRST_ROWS and ALL_ROWS optimizer modes.
5. An effective approach for query tuning involves enabling the RULE based optimizer either through a RULE hint, or by changing the session-level optimizer goal with the following SQL statement:
ALTER SESSION SET OPTIMIZER GOAL = RULE;FALSE. Do I even need to justify the answer? 🙂
6. The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics.TRUE
7. A SELECT statement requiring 10 seconds and performing 10,000 DB_BLOCK_GETS is automatically less efficient that a transformed version of the SELECT statement that performs 2,000 DB_BLOCK_GETS.FALSE. “automatically less efficient” is an ambiguous and relative term. What if two SELECT statements process different result sets?
8. The FIRST_ROWS optimizer mode is appropriate for OLTP type systems because it allows the small resultsets that are exepected by the end users to be returned as quickly as possible.TRUE.
9. The RULE based optimizer does not support any features introduced in Oracle since the mid 1990s.Not sure but I guess TRUE.
10. Specifying a value for the OPTIMIZER_INDEX_CACHING parameter is recommended, because the value is critical for costing nested loops joins. Its value should be set to indicate the percentage of an index that is likely to remain in Oracle’s buffer cache.FALSE. By using System statistics, it is no longer “recommended” to set value for the OPTIMIZER_INDEX_CACHING parameter.
11. When the PARALLEL_AUTOMATIC_TUNING parameter is set to ON, the cost-based optimizer will artifically increase the calculated cost of index access paths, which increases the frequency of full table scans that may be executed in parallel.No idea. (Yes, I know I can read the linked articles and find out but I find it like appearing for examination with text books. 🙂
12. With the RULE based optimizer, the driving table is the last table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table is the first table listed in the FROM clause of the SQL statement.FALSE. With the RULE based optimizer, the driving table is the first table listed in the FROM clause of the SQL statement. When the Cost-Based optimizer is used, the driving table can be any table listed in the FROM clause of the SQL statement.
13. The Cost-Based optimizer often performs unnecessary full table scans when three or more tables are listed in the FROM clause of a SQL statement.FALSE. The Cost-Based optimizer does not perform full table scans by default, irrespective of how many tables are listed in the FROM clause of a SQL statement
14. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the SQL statement which will be submitted next will return 10 rows.FALSE. Setting the OPTIMIZER_MODE parameter at the session level to FIRST_ROWS_10 instructs the query optimizer that the subsequent SQL statements will be executed to return first 10 rows as quickly as possible.

From the 10g R1 documentation:
“ORA-01986: OPTIMIZER_GOAL is obsolete Cause: An obsolete parameter, OPTIMIZER_GOAL, was referenced.
Action: Use the OPTIMIZER_MODE parameter.”

Not only is the SQL statement missing the _ character between OPTIMIZER and GOAL, but it is also attempting to use a parameter that has been obsolete since Oracle 10g R1 was introduced. I wonder where that SQL statement was found?

Time to take out a somewhat easy answer:
#6 “The default optimizer mode in Oracle 10g R1 is CHOOSE, which means that the RULE based optimizer will be used if none of the objects referenced in the SQL statement have statistics” – FALSE. If memory serves me correctly, CHOOSE was the default optimizer mode in Oracle 9i, but the 10g R1documentation (article link 10 above) states the following:
“CHOOSE – This parameter value has been desupported.
RULE – This parameter value has been desupported.”

I suspect that Oracle Database would not ship with a default parameter that is identified as desupported (on a side-note, CHOOSE still works) in the documentation. The 10g R1 default for OPTIMIZER_MODE is ALL_ROWS.

—
The second half of the question, I believe, is correct even with Oracle Database 11.2.0.1 when the OPTIMIZER_MODE is set to CHOOSE.

Time to answer another one of the questions. #2 “SQL execution speed will be fastest when the first join between two row sources produces the smallest possible result set from the specified row sources.” False. Why? What if the smallest two tables are completely unrelated, and may only be joined using a Cartesian join? Would Oracle Database ever do that – yes. Is it sometimes more efficient and sometimes not – yes.

SET AUTOTRACE OFF
SET PAGESIZE 1000
SELECT /*+ LEADING(S) */
T.PLAN_TABLE_OUTPUT
FROM
(SELECT
SQL_ID,
CHILD_NUMBER
FROM
V$SQL
WHERE
SQL_TEXT LIKE 'SELECT%'
AND SQL_TEXT LIKE '%T1.C1=T2.C1%'
AND SQL_TEXT LIKE '%T4)') S,
TABLE(DBMS_XPLAN.DISPLAY_CURSOR(S.SQL_ID,S.CHILD_NUMBER,'ALLSTATS LAST +COST')) T;

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: