From the bestselling book, Oracle Wait Event Tuning — High Performance with Wait Event Interface Analysis by Stephen Andert, published by Rampant TechPress; it can be purchased directly from Rampant TechPress here.

by Stephen Andert

When the users start calling in death threats because “the database is slow,” it pays to have something that you can use to find out why, so you can fix it quickly. This is more challenging than it may seem at first, since there are many components in today’s complex world. Is the problem with the user’s PC, their local area network, the application server, the link between the application server and the database server, or the database itself? If the application is Web-based, then add to that list of possible suspects the wide-area network, the Internet and the Web server. If the problem is in the database itself, is it the buffer cache not containing enough information for quick retrieval, execution plans being aged out of the shared pool, locking or latching issues, excessive parsing, or any of the myriad of other components in today’s Oracle RDMS?

The Wait Interface is one way for you to identify from the database where the problem that is affecting your users is located. For example, if the Wait Interface indicates a problem with I/O operations (db file sequential read), then you probably have a storage problem. It would make sense to work with your system administration group to see what they can do to alleviate the I/O bottleneck. There are other causes such as bad SQL or bad statistics that could be causing unnecessary full table scans, so it is important to understand the Wait Interface and what it is telling you about your whole system, both inside and out of your database.

The Importance of Holistic Tuning

What is holistic tuning, anyway? Does it involve hypnosis or natural herbs and vitamins? Merriam-Webster’s online dictionary defines holistic as “relating to or concerned with wholes or with complete systems rather than with the analysis of, treatment of, or dissection into parts.”

In other words, it is simply acknowledging that in the same way the human body has the “elbow-bone connected to the shoulder bone,” database performance is tied to not only the database, but to the application server, the network, storage subsystem and many other factors. Due to this interconnectivity, any tuning activities need to include a way to identify the source of the problem, even if it is not within the database itself. Then these tuning activities should help you to determine what impact fixing that problem will have on the rest of the system.

As technical people, we tend to like things that fit into a box. One plus one is two. Two multiplied by two is four. Red mixed with blue is purple. It is important to remember there are times when one plus one is three. For example, when tuning an Oracle database, it is important to keep in mind that database performance is more than the sum of the performance of its parts; e.g., server, I/O subsystem, application queries: there are complex interrelationships between these parts. The effective database tuner needs to be aware that “cooperative changes” may be necessary.

If you ever had s’mores while camping, you understand this already. (If you have not, here is the scenario.) Making s’mores involves roasting marshmallows over a campfire and putting them on a chocolate and graham cracker sandwich. The marshmallow melts the chocolate a little bit, and makes for a treat that is so good, everyone comes back and wants some more (somemore ... somore ... s’more), which is how these treats got their name. The point is that while a graham cracker, a chocolate bar, and a marshmallow are all tasty snacks, when they are combined, the result is tastier than the components separately.

Why should a DBA worry about things other than the database? Is it not enough to demonstrate that the database is fine, and let the system administrators worry about things like operating system tuning, and network engineers worry about network capacity issues?

First, in some places, the same person does some or all of these jobs. Regardless, when management hears about a performance problem, they do not care who fixes the problem; just that it gets fixed.

Second, it is much more satisfying to be a part of a solution than to simply say, “it’s not a problem with my database.” Being a part of the solution is also a better career option, because management generally prefers team players that participate in finding solutions rather than simply passing the “hot potato” to the next department.

Definition

Much has been written about different tuning approaches, as much has been written about different dieting techniques. There are books about the protein diet, the melon diet, the cabbage diet, and even the ice-cream diet, just to name a few.

Likewise, there are many books and sources for different ideas on tuning databases. Some of them are based on facts. Others are pure fiction that may have worked or appeared to work under specific circumstances, but as far as a day-in and day-out methodology, they work about as well as the ice-cream diet does for someone trying to lose weight.

Some of the misguided approaches have a foundation in truth, but are based on older versions of Oracle and are no longer effective methods of enhancing performance.

So what, then, is holistic tuning? Is it implementing all of the existing practices? No, that would be like filling up your soda with a little of all the flavors. Except for a very few (like my kids), most people would say that mix tastes terrible. Even with the mixed drinks at the local bar, there are specific mixes, not just a jumble of different elements.

Trying to implement a tuning project that includes wildly different schools of thought is a recipe for chaos. “Increase the buffer cache to increase the buffer cache hit ratio.” “Increase the sort_area_size parameter so more sorts can be done in memory.” “Add more CPUs since the idle time for the host has vanished.” “Migrate to a new machine since you need more RAM to support the larger SGA needed to accommodate your larger buffer size.”

In the end, you are still getting calls from irate users and their managers because the “database is still too slow.” And now, the system administrators are saying that it cannot be a system problem since they just installed a million dollars worth of equipment that you said was needed.

To make matters worse, since the recommendations were yours, and they have not done any good, convincing people that any future changes you propose have any chance of working is now much more difficult. All of the changes might have been good in isolation, but by neglecting the whole system, each change either improved nothing or made something else suffer more.

The solution to this problem is, of course, simple: make and recommend only changes that will fix the problem and make the database run more efficiently. If doing this were that easy, you would not be reading this book. But it can be that easy if you start applying the concepts introduced so far.

One way to think of tuning in a holistic manner is to remember that everything you think of changing — database parameters, server specifications, application/code enhancements, and so on — might be just addressing a symptom and not the underlying cause of the performance problem.

For instance, suppose your CPUs have very little idle time, so you decide that a CPU upgrade is needed. Unfortunately, the CPUs were not the underlying cause of the slowness the users were experiencing, so when the additional (or faster) CPUs are brought online, I/O requests or buffer gets, or whatever the actual cause for the slowness, are now processed faster than ever and the users start screaming even louder since the system upgrade has actually made the system slower.

By using wait analysis to look for the cause of the slowness, instead of just identifying symptoms that we have been told are the causes, we can fix the problem instead of the symptom. We can do this by using the Wait Interface; we can look at the whole system from a database perspective and find the root cause instead of seeing only database symptoms.

--

Stephen Andert is a database administrator for many years and has been working with Oracle for over 5 years. He has been working with various different relational databases for over 12 years. He has been a technical reviewer for several Oracle books from O’Reilly & Associates, authored an article on SQL*Loader tuning, and has presented at local and international Oracle user group events. Stephen is an Oracle8 and Oracle8i Oracle Certified Professional. His current book is Oracle Wait Event Tuning — High Performance with Wait Event Interface Analysis.