New Features for Administrators - Question 047

Consider the following statement:

SQL> EXECUTE DBMS _STATS.GATHER_SCHEMA_STATS (-
2> ownname => 'OE', -
3> estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
4> method opt => 'for all columns size AUTO');

What is the effect of 'for all columns size AUTO' of the METHOD_OPT option?

A. The Oracle server creates a new histogram based on existing histogram definitions for all table, column, and index statistics for the OE schema.

B. The Oracle server creates a histogram based on data distribution regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.

C. The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.

D. The Oracle server creates a histogram based on application usage, regardless of data distribution, for all table, column, and index statistics for the OE schema.

Answer: C

Explanation:

The Oracle server creates a histogram based on data distribution and application usage of the column/s for all table, column, and index statistics for the OE schema.

Incorrect Answers

A: The Oracle server does not create a new histogram based on existing histogram.

B: Histogram creation is not regardless of how the application uses the column/s for all table, column, and index statistics for the OE schema.

D: The Oracle server creates a histogram not only based on application usage, but based on data distribution also.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 220-222

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 180-181

Chapter 6: Oracle9i SQL, PL/SQL New Features