Performance Tuning - Question 125

Which two statements are true regarding the use of DB_CACHE_ADVICE init.ora parameter? (Choose two)

A. Setting the parameter to READY reserves space in the buffer cache to store information about different buffer cache sizes, but no CPU overhead is incurred.

B. Setting the parameter to READY reserves space in the shared pool to store information about different buffer cache sizes, but no CPU overhead is incurred.

C. Setting the parameter to ON reserves space in the buffer cache to store information about different buffer cache sizes, and CPU overhead is incurred as statistics are collected.

D. The V$DB_CACHE_ADVICE view contains information that predicts the estimated number of physical reads for different cache sizes for each buffer cache setup in the SGA.

Answer: C, D

Explanation:

Each cache size simulated has its own row in this view, with the predicted physical I/O activity that would take place for that size. The DB_CACHE_ADVICE parameter is dynamic, so the advisory can be enabled and disabled dynamically to allow you to collect advisory data for a specific workload.

Two minor overheads are associated with this advisory:

• CPU: When the advisory is enabled, there is a small increase in CPU usage, because additional bookkeeping is required.

• Memory: The advisory requires memory to be allocated from the shared pool (about 100 bytes for each buffer).

The V$DB_CACHE_ADVICE view is populated when the DB_CACHE_ADVICE parameter is set to ON. This view shows the simulated miss rates for a range of potential buffer cache sizes.

Incorrect Answers

A: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY.

B: The DB_CACHE_ADVICE init.ora parameter should be set to ON, not READY. Also it reserves space in the buffer cache, not the shared pool.

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

Chapter 5: Tuning the Database Buffer Cache