New Features for Administrators - Question 038

Which operation causes an index to be considered "used" while monitoring is turned on for that particular index?

A. Only when the index is specified in a hint

B. When data is fetched by an execution plan that uses that index

C. When a statement is executed and the execution plan contains a reference to the index

D. When a statement is parsed and the resulting execution plan contains a reference to the index

Answer: D

Explanation:

When a statement is parsed and the resulting execution plan contains a reference to the index an index will be considered "used".

Incorrect Answers

A: It will be considered as “used” when a statement is parsed and the resulting execution plan contains a reference to the index, not only when the index is specified in a hint.

B: An index will be considered "used" when a statement is parsed and the resulting execution plan contains a reference to the index, not when data is fetched by an execution plan that uses that index.

C: It will be considered as “used” when a statement is parsed and the resulting execution plan contains a reference to the index, not when a statement is executed and the execution plan contains a reference to the index.

OCP Oracle9i Database: New Features for Administrators, Daniel Benjamin, p. 212-214

Chapter 4: Performance and Scalability Enhancements

Oracle 9i New Features, Robert Freeman, p. 56-59

Chapter 2: Oracle9i Architecture Changes