DBAs have for too long thought of themselves as mere gatekeepers to databases. This mentality has pushed us down the corporate food chain; increasingly, we are not thought of as problem solvers. But we can change negative perceptions and begin positively impacting our companies and helping our customers.

Instability

Let’s face it, instability in our work lives as database administrators is usually caused by changes that happen in the environments we manage. These changes can range from minor DDL changes to complete reworkings of applications. And as we watch these items being released into production, we often feel a gut-level uneasiness — an uneasiness rooted in knowing deep down that not all the proper walk-throughs, testing, performance evaluations, and recovery scenarios have been covered. Hence, the uneasiness, because we all know that when something goes wrong, it is usually viewed as the DBA’s fault, and we are left to pick up the pieces and fix them.

Attempts at Stability through Policy

Typically, DBAs attempt to stabilize an unstable environment by putting policies in place. Policies in themselves are not bad. But how well you can implement policies may be limited in direct proportion to how many “cowboys” you work with. If you find yourself working with such freedom-loving personality types, try to make policy changes slowly as these individuals are usually very reluctant to embrace any new procedure that they perceive as limiting their autonomy and control. In such cases, you will, no doubt, find yourself instating policies such as the following:

1. Every change to the database must have a requirements document
2. Development must have a sign-off from DBAs on the new technology
3. No changes can be made after 3:00 pm on Friday
4. The Production database can’t be used for Test / Stress / QA
5. ALL changes must be approved by the DBA
6. ALL changes must be implemented by the DBA
7. ALL source must go through QA & Test

Listing 1: Short List of Policies for a DBA

Although DBAs attempt to use such policies so that the databases we manage — and our lives — are bound by structure, we continue to fall victim to the chaos caused by when:

• Everyone is in a rush
• Organizations don’t do things the right or proper way the first time
• Situations are imposed that lead to data inconsistencies and integrity issues
• DBA is unaware of others’ actions that impact the database
• Others are not willing tell you when their actions are impacting the database
• After a problem, DBA are expected to find, pick-up, and reassemble the broken pieces

As DBAs, you and I are bound to serve the users of the database systems ... but wouldn’t it be much better for everyone if we could provide assistance during regular business hours to proactively alleviate problems before they happen?

Finding Solutions to Problems

No matter how we try to avoid them, DBAs will always face problems that only we can find and fix. When I think of finding problems within databases, I believe there are only two real methods: the Lazy Method and the Intelligent Method. By these names alone, you may be able to determine which method you use, and which you should use. The fact of the matter is, I constantly see DBAs (myself included) falling into the traps of the Lazy Method instead of focusing on the behaviors I associate with the Intelligent Method.

Lazy Method

As you can see in Figure 1, the initial steps to problem solving using the Lazy Method are triggered by the occurrence of an incident such as user complaints, system crashes, or system unavailability. After DBAs are notified of an incident, they move directly to the investigation stage, in which they begin combing through manuals and Web sites for information, and begin sorting through their reserve of scripts in an attempt to find out what has just happened. With luck, the investigation stage will lead to detecting a problem, and putting in place a solution. But this methodology has shortcomings, as shown in in Listing 2. I have also seen many DBAs spend too much time in the investigation phase of problems that have not actually impacted their user base. Investigations are fine if you are building on detection mechanisms that are part of the Intelligent Method of isolating a problem. But if you find yourself endlessly investigating your database without using what you learn to isolate any problems, you should step back from the situation and ask yourself whether or not you are actually helping things improve.

1. Your actions are reactive, not proactive, in nature
2. The events that trigger an investigation are often very specific to an incident, narrow in scope, and the solutions typically do not take the full health of a database into consideration.
3. Most of your time is spent in problem detection, not problem solving
4. Because of the time wasted in detecting the problem, using this method inherently wastes money
5. Customers / users drive the work flow of the database administrators
6. Database administration group is seen as ineffective

Listing 2: Lazy Methodology Shortcomings

Figure 1: Lazy methodology.

Intelligent Method

By comparing the Lazy Method of problem detection with the Intelligent Method, you will quickly glimpse how you can build stability in your environment. In Figure 2, you can see that our base is now supported by problem detection, not pointless investigation. We now have a proactive mechanism and are not driven by user complaints. When you build a methodology that is rooted in detection, the time spent investigating and in finding solutions is greatly reduced. For instance, in a simple scenario, if you receive a user complaint about poor response time, you might start looking at items such as memory consumption, disk I/O rates, CPU percentages, and resource wait events, hoping to find a glimpse of what was wrong. On the other hand if you already were proactive and had a detection method in place for these and other type of issues, you might easily be directed to locking contention. Therefore, under this model, if you do receive requests from your users, or if concerns are raised about how the database is performing, you can quickly go to your detection mechanism and either prove the stability of the database or be driven to modify it. With this methodology, you can quickly realize benefits, such as those depicted in Listing 3.

When asking yourself how to begin in problem detection, consider the points in Listing 4. These questions should help you decide whether or not you should be working on that particular issue. What is sometimes very hard for DBAs to grasp is the fact that problems by nature are only problems if they produce pain for someone or for some entity within the business. For instance, take the following issue and see if it passes the test. Your database box spikes at 2am every morning. You might think this is a problem that requires a resolution, but if no one knows about it and all batch jobs go out on time, I would venture to say there are more pressing issues that need your attention.

1. User complaints are circumvented and drastically reduced
2. Time searching for solutions is reduced
3. Database administration group is seen as being very effective, and as trusted custodians of corporate information
4. Allows database administrators to work on more strategic issues that do effect the bottom line and direction of the company
5. You finally have confirmation that it is possible to find problems before they happen

Listing 3: Intelligent Methodology Benefits

1. Has the issue been seen by others?
2. Will someone benefit from you working on the issue?
3. Are you solving a real problem that is causing some pain for your users?
4. Before solving the problem, are you be able to determine what was the true root cause?
5. Will solving the problem eliminate it from recurring?

Listing 4: What to Focus On for Problem Detection

Figure 2: Intelligent methodology.

Intelligent Methodology

As DBAs, you and I need to get rid of the notion that our role within an organization is to be the “go-fers” of database administration, at the beckoning of everyone else. It is time that we take action to create positive change that can benefit and drive a company. But we can only do this if we step back, assess, and then change the way we do our jobs. If we focus on issues that are true pain points for the organization, we will reduce the amount of time we spend within the databases and increase the amount of time we spend helping the business to succeed, this will go a long way toward driving us up the corporate food chain.

--

James F. Koopmann is committed to providing guidance and technical savvy to solve today's operational issues as well as give insight to tomorrow's opportunities. Koopmann is an accomplished author with technical papers in Oracle publications such as Oracle Magazine, Oracle Professional, Exploring Oracle, various local Oracle user group publications and across the Web. He is an Oracle Certified Professional DBA and speaker at local and international Oracle user groups. He may be reached at jkoopmann@pinehorse.com or www.pinehorse.com.