Oracle has provided access to its wait interface for several releases and with each new release it expands the range of wait information available, so much so that it's hard to not find something to examine. Of course, examination leads to investigation, which leads to tuning, even when there is nothing to tune. Such constant twiddling and tweaking is known as Compulsive Tuning Disorder, or CTD. Unfortunately, the more ways Oracle provides to interrogate the wait interface the more the DBA can fall victim to CTD. To help reduce the urge to tune, a few questions need to be asked regarding the so-called 'problem area'. Let's dig in and ask those questions.
First, and foremost, is the following question:
"What problem are you trying to solve?"
If you can't answer that question then there really isn't a reason to tune anything; you'll never know when you're done and the task will go on and on and on and on and on ... ad infinitum, ad nauseaum with no progress to report and no end in sight. One thing will lead to another as you find more areas to 'tune' based on the wait interface data and blog posts and articles clearly telling you something needs to be fixed. In most cases nothing could be further from the truth.
Next is misleading or misunderstood numbers, mainly in reference to data reads and writes. I've seen some DBAs try to tune the database to reduce logical reads -- it's usually newer DBAs who see the large values for logical reads and conclude there is a problem. The issue isn't the available data, it's isolating one small aspect of the entire performance picture and tuning that to the exclusion of everything else. Large volumes of logical reads aren't necessarily a problem, unless the buffer cache is being reloaded across short periods of time, which brings large volumes of physical reads to consider. Taking each individually may cloud the water and obscure the real issue of a buffer cache that may be too small for the workload; a configuration that once was more than sufficient can, over time, become a performance bottleneck as more and more users are using the database. A database is a changing entity and it needs to be tended, like a garden, if it's going to grow.
The DBA needs to listen to the users since they will be the first to complain when something isn't right and needs attention. Performance is time; for business, time is money, and when tasks take longer and longer to complete less work is getting done. Really wasted effort; if no one but the DBA is going to notice the 'improvement' it's not worth pursuing.
Not all tuning is bad or wasted effort but the DBA needs to have a clear goal in mind and a path to follow that addresses issues and brings them to some sort of resolution, even if it's only a temporary fix until a permanent solution can be implemented. It does no good to constantly look for problems to solve, especially when the users aren't complaining.
When something is wrong the DBA will hear about it; that's the time to step into action and start problem solving. The DBA doesn't need to go looking for problems, they'll show up all by themselves. And if he or she isn't constantly twiddling with this or tweaking that the real issues can be dealt with when they happen. Then the users will stop complaining and peace and joy will reign supreme. Okay, so peace and joy won't necessarily cover the land but the users will stop complaining, at least for a while, and there will be benefit seen from the effort expended.
CTD is thankless, relentless and never-ending, so don't get caught up in wanting to fix everything; it can't be done and there are some things that are, most likely, not worth the effort spent given the small return that investment will generate. It's not enough to know when to stop, the DBA also needs to know when NOT to start; if there is no clear destination to the journey it's best to not begin. There is plenty to do without making work out of nothing.
See all articles by David Fitzjarrell