Introduction to this series
Tuning a database or database applications is a time-intensive, repetitive task. Over time, the database changes in many ways and must be reevaluated. In addition, changes made to improve performance will need to be evaluated. Did the change actually improve performance? If so, how much? How much effort did it take to achieve the gain (or loss)? What is "good performance" and how can we make it better?
This series identifies a standard approach for performing or evaluating database tuning efforts. It is applicable to data warehouses, custom databases, custom database applications or Oracle Applications Database. The criteria used in the evaluations will depend on individual circumstances. For example, a data warehouse has different performance requirements than a customer order application. These requirements will affect the database architecture, which affects how performance can be optimized. In short, the goal is to identify how performance can be optimized, as measured by how operations are transacted by users.
When users start complaining that the database is too slow, the natural desire is to tune everything. But the real answer, based on customer needs, is a combination of training, tuning and business process management. This primer identifies the basic elements of performance tuning, the tools used and a basic methodology for obtaining the information used to evaluate a database. It is designed for the non-technical user i.e., the applications user, project managers or anyone who would like understand what those techies and sysadmins do, and why they talk to themselves...
It identifies the basic steps for each stage of performance tuning, explains what tools may be used and function or expected results yielded by the tool. It also provides a set of basic questions for initial performance problem assessment -- a manager's checklist to guide the non-technical manager to the business of performance tuning.
What is good performance?
There are a number of published articles on what constitutes good -- and bad -- performance. These articles are usually defined for a specific architecture. This is because the machine configuration -- size, speed and number of CPUs significantly impacts the system. Simply put, some systems have more horsepower than others. However, CPU is not the most important measure in dealing with performance issues. The most important measure is response time. Time is the metric the user understands, time is the measure the manager uses and time is the most effective measure of performance improvement. While there are many important ratios that measure whether a database is working effectively, the goal is for the database to effectively support user transactions. Tuning hit ratios and cache statistics is like patching potholes. Sometimes patching fixes the problem; sometimes repaving is better. That's why a more effective measure of performance tuning results is response time. Response time concentrates on the specific business process that users have identified as problematic.
Planning a tuning engagement is like the initial stages of any project and uses the same steps:
1. Determine what you've got.
How big is it in terms of users, machines, operating systems and connections. This is the current state.
2. Determine what they think they need.
What does the user see as the primary problem? No matter what you actually find (good, needing improvement, dearly beloved, etc.), you will frame your results and recommendations in terms of the user's perceptions. This is the future state, or the "to-be" state. It establishes an understanding of perceived priorities.
3. Determine the details of what you have.
In this case, you or your tame techie will use commands and scripts to retrieve detailed information about each area under consideration.
4. Establish goals.
If it ain't broke, don't fix it. What is it that returns the most value for the user? Remember that tuning for tuning's sake is not effective….
5. Prepare a plan.
Documenting your plan is an important management. If you are lucky, when you distribute your plan, you'll get feedback from all sides. (Recite this daily: "Feedback is my friend.") Feedback allows you to plan targeted communication as results are achieved.
6. Work the plan.
Your improvement plan will probably have multiple phases. At each phase completion, evaluate the accomplishments in comparison to both the original baseline and with accomplishments of previous phases. Update your communications, give feedback to the users regarding results.
7. Keep going until you reach your goal or reassess your goals.
As time elapses, priorities may change. For instance, a company with seasonal sales may need to have all performance enhancements completed before the sales rush or put in place some temporary "fixes" for the duration. Care must be taken when introducing temporary solutions, however.
Copyright 2003, Carol Francum.
Click for step 2 of the performance tuning series.
About the author
Carol Francum, CIO of Ashford Systems Group and SearchOracle.com resident Oracle Applications expert, has over 20 years experience in Information Systems design and development, including 10 years an an Oracle developer, DBA, designer and implementor and six years in Oracle consulting for DBA, applications custom development and functional solutions. Her broad Oracle Applications experience includes various stints as an in-house trainer and workshop instructor, in which she developed instruction plans and exercises for Oracle Advanced Pricing and Order Management for 10.7 to 11i. Ask her your questions about Oracle Applications today.
