Performance Tuning - Question 109

Which three actions will cause queries to place a table’s blocks at the most-recently-used end of the LRU list? (Choose three)

A. Creating a table with the CACHE option.

B. Querying the table by using a CACHE hint.

C. Ensuring the query performs a full table scan.

D. Defining the table without the option for caching.

E. Altering an existing table to set the CACHE option.

F. Ensuring the query does not retrieve data through index lookup.

G. Creating a separate database buffer cache to hold cached table.

Answer A C E

Explanation:

If you are creating a table with the CACHE option or altering an existing table to set the CACHE option will place a table’s blocks at the most-recently-used end of the LRU list. Blocks will be placed at the most-recently-used end of the LRU list if the query performs a full table scan.

Incorrect Answers

B: Querying the table by using a CACHE hint will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.

D: Defining the table without the option for caching will not place blocks in the LRU list.

F: This statement will not cause queries to place a table’s blocks at the most-recently-used end of the LRU list.

G: You don’t need to create a separate database buffer cache to hold cached table.

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

Chapter 5: Tuning the Database Buffer Cache