1Z0-083 practice question 11 of 35

Which two are true about Oracle Optimizer Statistics, their use, and their collection? (Choose two.)

Full set: 286 questions
Answer options
A: The number of table rows is considered when evaluating the cost of accessing a table using an index.
B: Index balanced B*Tree height is considered when evaluating the cost of using an index. Correct answer
C: The Statistics Advisor can help recommend the best way to gather statistics. Correct answer
D: Statistics collected using DBMS_STATS always yield the best optimizer result.
E: The Statistics Advisor generates actions for all recommendations.
Correct Answer:
B. Index balanced B*Tree height is considered when evaluating the cost of using an index.; C. The Statistics Advisor can help recommend the best way to gather statistics.
Explanation

The Oracle Optimizer relies on statistics to estimate the cost of execution plans. The BTree height (BLEVEL) is a crucial index statistic used to calculate the cost of traversing an index to find the first leaf block. The Statistics Advisor is a component of the SQL Tuning Advisor that analyzes how statistics are gathered. It can diagnose issues and provide recommendations, including generating scripts to implement improved statistics collection methods. These features are central to maintaining optimal query performance.

Why Incorrect

A. The number of table rows (NUMROWS) is a table statistic, not an index statistic. While used to estimate cardinality for the overall operation, BLEVEL is more direct to the index access cost.
D. Statistics can become stale or may not accurately represent skewed data, potentially leading to suboptimal plans. They do not "always" yield the best result.
E. The Statistics Advisor generates recommendations; it may generate scripts for some actions, but it does not automatically generate executable actions for all possible recommendations.

References

1. Oracle Database SQL Tuning Guide, 19c, Chapter 14, "Optimizer Statistics Concepts". (Discusses index statistics like BLEVEL and their use in cost calculation).

2. Oracle Database SQL Tuning Guide, 19c, Section 15.3.1, "Cost-Based Optimizer". (Explains how costs are calculated using statistics).

3. Oracle Database SQL Tuning Guide, 19c, Chapter 6, "Using the Automatic SQL Tuning Advisor". (Describes the role and capabilities of the Statistics Advisor).

Scroll to Top