What is the right time to stop tuning an Oracle database?

The right time to stop tuning an Oracle database is often determined by the point of diminishing

returns - when the cost of further tuning (in terms of time, resources, or money) exceeds the

performance benefits gained. This is often related to the budget allocated for performance tuning.

A (Correct): When the allocated budget for performance tuning has been exhausted, it may be time

to stop tuning unless the benefits of further tuning justify requesting additional budget.

B (Incorrect): Eliminating all concurrency waits from the Top 10 is an unrealistic goal since some waits

are inevitable and can occur due to application design, which might not be possible to eliminate

completely.

C (Incorrect): The buffer cache and library cache hit ratio being above 95% does not necessarily

indicate that the database is fully optimized. Hit ratios are not reliable indicators of database

performance and should not be used as sole criteria to end tuning efforts.

D (Incorrect): Having I/O less than 10% of DB time is not a definitive indicator to stop tuning. It is

essential to consider the overall performance goals and whether they have been met rather than

focusing solely on I/O metrics.

Reference:

Oracle Database Performance Tuning Guide: Introduction to Performance Tuning

Oracle Database 2 Day + Performance Tuning Guide: Understanding the Tuning Process