May 13, 2010
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.
- Troubleshooting Oracle Performance book
- Asktom.oracle.com on SQL Profiles
- Asktom.oracle.com on Optimizer Mode
- Oracle 10g R1 Documentation Performance Tuning Guide
- Oracle 10g R2 Documentation Performance Tuning Guide
- Oracle 10g R1 Documentation – Automatic SQL Tuning
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.
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.
3. Oracle Database 10g’s SQL Profiles and SQL Access Advisor help to change execution plans for queries without adding hints.
4. Oracle Database 10g offers three optimizer modes, controlled by the OPTIMIZER_MODE initialization parameter: RULE, COST-BASED, and CHOOSE.
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. :-)