The Non-Technical Art of Being a Successful DBA Continues

The Foot Rule of Thumb
The Foot Rule of Thumb is pretty simple. You need to experiment and create your own rules of thumb. A few months ago I wrote a series of interview questions for DBAZine. The questions were the basis for a podcast interview with Jonathan Lewis. What impressed me the most about the interview with Jonathan was the amount of time he spent researching how the database worked. He stated that he became an expert by spending time investigating the database's intricacies and documenting the results.

You begin creating your own rules of thumb by reading opinions from folks that you can trust. The industry experts that are both learned in their chosen profession and aren't afraid to provide you with their opinion. If you are interested, I wrote a blog listing my favorite authors and bloggers.

In addition to learning from others, you need to create your own test environments and experiment. One of Oracle's great benefits is that it runs virtually anywhere. If you aren't sure how something works and the manuals aren't explicit enough, build a test case and execute it. I experiment with my Oracle9i and Oracle10G environments on a daily basis to teach others and continue my own database education. If someone asks me a question that I can't answer, we'll log on to my test environment and try it.

How do you make your own rules of thumb? Follow Jonathan Lewis's advice and test, experiment and learn! Afraid that performance will suffer if you place 4 columns in an index to obtain index-only access? Build the index in test, have the developers run transactions that access the new index, monitor on-line transaction performance and find out. Then document the results.

Try different column combinations, multiple index usage, etc. When prototyping complex joins between several tables, build a set of indexes that favors one access path and test the queries in question. Drop the first set of indexes and create indexes that favor another access path. Build the different permutations of indexes that influence the optimizer to join a different set of tables first or allow different join types to be performed. Test the different access paths and record the performance statistics.

The above recommendation may seem to be a time consuming process, but until you learn access paths this is the best approach to take. I have spent hundreds of hours working with access paths and if I could impart one bit of wisdom, it would be the following one: No matter how much time you spend reading other's opinions on how the database works, you need to spend time "in the seat" experimenting and learning on your own.

The same advice goes for learning OEM, backups and recoveries, advanced database features and virtually every other facet of the database. You need to do as all the other experts do - test, experiment and learn.

Repeatable Processes
Repetition, even though it can be boring, is the foundation for a high quality support environment. If the scripts and administrative process worked correctly the first time, chances are they will continue to work correctly in the future.

Automating and documenting complex administrative processes such as production to decision support database refreshes and application upgrade activities will allow future iterations of these activities to be executed more quickly and with less errors. If you have been reading my blogs, you'll understand the importance I place on documentation.

As I have stated in the past, have you ever tried to refresh an ERP application test environment from production when that test environment didn't have enough space to hold all of production's data? 4,000 steps later and you begin to second-guess your choice of professions. The more complex the process is, the greater the need for detailed documentation.

The moral of this story is: If you don't want to be the only one that can perform that 900 step ERP Application production to test refresh, script it and then document it.

DBA Report Cards and the 360-Degree Review Process
Effective measurements are required to judge the success of any activity. The quality of support the DBA team provides should be reviewed on a regular basis. A DBA report card allows business and application development units to provide feedback on DBA support activities.

The report card will allow them to measure how well they feel you are meeting your Service Level Agreement objectives. Here are a few sample questions to start you on your way:

• How would you rate the turnaround times for DBA unit work requests?
• How would you evaluate the DBA unit's responsiveness to questions?
• How would you evaluate the DBA unit's responsiveness to requests for assistance?
• Please rank the quality of communications the DBA unit provides.
• Please rank the overall quality of work the DBA unit provides.

All questions are ranked from 1 to 5 with 5 being the highest. You can also allow the respondents to rank the importance of each question from 1 to 5 with 5 being the highest.

A short survey should be attached to the DBA report card to gather additional information that can be used to improve the quality of support the DBA unit provides to their customers:

• What are your top three technical challenges that you face?
• What are the top three non-technical challenges?
• Please list your current priorities. Rank them in order of importance.
• List the most important services the DBA unit provides. Rank them in order of importance.
• What support services does the DBA unit do a good job of providing?
• What support services should the DBA unit improve upon?
• What additional services would you like the DBA unit to provide?

Meetings can be held with the respondents to discuss their reviews. DBA team members participating in the reviews must be prepared to respond to criticism in a professional manner. But just as its title describes, the 360-degree review process also allows support units to provide feedback on their customer's support requests and work activities. The 360-degree review process provides important feedback to both support units and their customers. Once again, your customers may not know that some of their expectations are unachievable until you tell them the reasons why.

Corrective Action Reports
Oracle is a challenging database to administer. As much as we would like to prevent mistakes, we do make them. That's one of the benefits of being human; we learn from our mistakes. During my 20 years in this field, I've seen all kinds of mistakes made - from little ones to catastrophic. I've also made my fair share of them.

The worst one I ever experienced is when we brought a technician in from a third-party disk vendor to format what we thought was to be a single disk in a huge disk array. We just purchased the arrays and were in the process of assuming support responsibilities from the vendor. The vendor assisted us in the initial setup and data migration from our old storage devices to their new whiz-bang storage system.

The array stored data from dozens of Oracle databases. After 20 minutes of showing our folks how to set the necessary switches and enter the appropriate values at the prompts, the technician hit the enter key with much flair and bravado.

I assumed we would see the one activity light activate for the disk being formatted. Instead, I saw a whole wall of lights begin flashing and twinkling. I thought to myself "Hmmm, this can't be good…". I glanced at the technician and the look of horror on his face confirmed my suspicions. He then reinforced my conclusions when he stammered, "I think I just formatted the entire array." I looked at the DBA next to me and said "Looks like we are in for a looong night."

I continue to be amazed at the rapid improvements in hardware redundancy and reliability. Hardware platforms now have the ability to recognize individual component failures, bypass them, produce diagnostic information and "phone home" to report the problem to the manufacturer. But hardware components will fail. Just as the sun comes up every morning, hardware components will fail, operating systems will get tied up in knots and database bugs will manifest themselves. Such is life for a computer technician.

The key is to document everything that happened during the time period when the problem occurred. Documenting provides you with the information you need to prevent the problem from occurring again and your customers with information on exactly what happened. Its my experience that the more informed a customer is about the problem and the steps your unit is taking to prevent its reoccurrence, the better they feel.

The corrective action report should contain:

• A detailed description of the error that caused the problem.
• The customer impact the problem caused.
• A timeline of the activities that were performed.
• The steps that were performed to correct the problem.
• Mitigating factors that contributed to or exacerbated the problem's impact.
• The steps that will be taken to prevent the problem from occurring again. Include who is responsible for the activity and a date the activity will be completed.

Thanks for Reading,

Chris Foot
Oracle Ace