Performance Tuning - Question 089

Which two statements about plan stability and stored outlines are true? (Choose two)

A. You can group outlines in categories.

B. You can only have one stored outline per SQL statement.

C. Plan stability only wants when SQL statements match textually.

D. Stored outlines are saved in the data dictionary (SYS schema).

E. Stored outlines become invalid when you analyze the associated objects.

Answer: A, C

Explanation:

Oracle9i maintains predefined execution plans in the data dictionary in the form of stored outlines. Ascertain whether the statement you have identified can be lumped into two or more broad categories of statements. Through a process known as plan equivalence, a SQL statement must exactly match the original statement used to generate an outline in order for the stored outline to be used. Even an identical statement with the addition of a hint or comment will not be considered equivalent for stored outline usage.

Incorrect Answers

B: You can have as many stored outline per SQL statement as you need.

D: Stored outlines are saved in the OUTLN schema.

E: Stored outlines do not become invalid when you analyze the associated objects. That’s why they are used to preserve predefined execution plans.

OCP: Oracle 9i Performance Tuning Study Guide, Joseph C. Johnson, p. 131-140

Chapter 3: SQL Application Tuning and Design