Every good story needs a few archetypes to entertain, educate, and enlighten its audience. While the world of database administration may appear to be the last thing that comes to mind when you think of an illustrative short story, here’s one complete with a hero, a conflict, a mentor and a moral:
The Hero
As a database administratorﾧ, it’s the call you dread. The office staff has ganged up on you. They’ve spoken to their manager at length about your deplorable database and the manager now approaches you with an unfriendly look in his eyes and a litany of collective complaints.
“It’s way too slow,” he says. “Online performance is stopping everyone from doing their jobs. Batch jobs like billing runs are sluggish. Customers are leaving in droves, abandoning shopping carts because it’s taking too long to refresh pages. The company is hemorrhaging money!”
As the hero in this scenario, it’s your job to save the day. The fate of the entire company’s fortunes now rests completely on your sturdy shoulders.
The Goal
Like any good hero, you realize that if your database is coughing along, there are things that you can do to remedy the critical situation. You are able to look at technical problems without fear—and fix them. Your goal is to set things right in the world again.
As a seasoned veteran of many database battles, the first thing you do is to deploy Oracle’s performance improvement model. It’s an incremental process that will test a variety of performance hits and then measure results.
You decide on your benchmarks to measure improvements. This helps you focus on where you need to aim. You decide, for example, that you need to measure the number of customer interactions occurring every second or how long it takes to do a billing run. Whatever your criteria happens to be, you set your target. After all, you need to know where you’ve been so that you know where to go. Establishing a firm baseline is your line in the sand. Naturally, you’ll want to measure the peak and off-peak times to understand current capacities. With your parameters in place, you test for common problems in performance for Oracle databases.
The Conflict
You talk to the database, asking what it is doing during a user session. Is it idling? Is it processing? Is it waiting? Using V$SESSION, you query the database to gauge its current state. You find some clues. Perhaps, it’s slow because it’s idle for long periods when an application is in session. You conclude that since the application is underperforming, the database has decided that there is no work to do.
However, what do you do if the answer doesn’t show up this elegantly?
Like Batman with his utility belt, Oracle has given you some tools, too. One tool is Oracle’s Automatic Database Diagnostic Monitor. This self-diagnostic software highlights database issues by exploring the data in the AWR (Automatic Workload Repository)ﾧ. It periodically takes snapshots of performance database statistics. If this doesn’t do the job, then there’s always Toad for Oracle—a tool to measure diagnostics in real time using visuals. You can peer into the heart of database processes, isolate bottlenecks, and resolve them.
If any of these steps work, you’re golden. You’ll be given the day off, a bonus in your paycheck and your own employee parking space for the rest of the month. However, what if none of this works or if it works but database performance issues arise again.
The Mentor
What if, despite heroic effort, the dragon is still alive and well and even looks reinvigorated, inspired and ready to go again. Every good story has a mentor, who steps in when the hero has done everything he knows to save the day. Since this is a tech story, the mentor is not a person. Database administration is all about speed, efficiency, performance.
Simply switching to a third party tool can instantly change everything because there is a noticeable improvement in performance.
Database performance tuningﾧ helps with diagnosing existing performance problems, identifying unknown problems, and configuring, optimizing, and analyzing storage engines. By methodically reviewing your operating environment and application infrastructure, you improve not only database performance but also resource management.
The Moral
Companies opt for MySQL because they believe it will save them operating costs, but if MySQL installations have been implemented badly, it’s going to mean many years of sluggish performance and panic-stirring downtime. What was saved on licensing costs is lost with poor performance issues. Fortunately, with the right answer, the kingdom can be restored.