In step 1 we identified the elements of a basic performance tuning plan. The next step is to determine the "current state" of the system. The current state is documented in order to form a baseline for comparison in later stages. We will also look at determining relative priorities with users and management, and how to define the details of the current state.
The current state
Determining the current state involves both users and the technical architecture of the database. This includes working with knowledgeable users, often called power users, to find out what they see as problems. Ask your power users if they have observed any specific trends, jot down what time problems occur and what they were doing. Take the time to observe the user performing their problematic tasks and record the steps. Using the same steps every time establishes a baseline for comparison. When working with the user, take care to ask questions, but not to jump to conclusions or make promises about how much you will be able to change. You are in the evaluation phase and changes may be technical, training related or both.
Check the database size and basic parameters. This can be done using the enterprise manager or a set of basic SQL scripts. Do not make changes at this time. It is important to establish a baseline for comparison first.
Check the database architecture. Identify network nodes used, the size and location of database files. Identify mount points. Identify network connectivity parameters, including packet sizes. Prepare a network diagram for future reference.
Check how database transactions are accomplished. How many users are there? What software and hardware do they use? What transactions have been identified as problematic? Later technical personnel will check the actual code used in transactions to determine effectiveness. Is the system shared (i.e., does a single server house production and development instances)? Are other types of applications housed on the system? In one corporate IT department, top-of-the-line mainframes housed database applications as well as manufacturing production tracking applications, creating contention for resources. Changes made must be evaluated in concert with ALL operations affected.
Can CPUs be added? What is the predicted effect and how does it compare with the cost to add? Scalable architectures may allow different sizes and numbers of CPUs. CPUs provided by different manufacturers may have different ratings as well. Whatever machine is run, a general rule of thumb is to know your CPU utilization target. Often this is a goal of 30 to 50 percent. What this means is that during normal, off-peak operation, measurements should indicate sufficient additional capacity for peak times, with an allowance for times of unusual load. Oracle Applications performance is not stable when CPU is over 80%.
Is the system maintained internally or externally? Is there a maintenance requirement that stipulates availability or capacity to be provided? If there are service level agreements (SLAs) in place, some improvements may be made at less (or more) cost. Additionally, the more organizations involved, the more time it may take to change performance.
It is important to document the baseline, distribute the information and maintain accurate records over time. Where more than one organization is involved, the need to establish priorities agreed upon -- or negotiated with all interested parties is particularly important.
Typical documentation (a.k.a. "deliverables") for a performance tuning project will include:
1. System or technical architecture document
2. Network architecture
3. Database architecture
4. Capacity plan
5. Change management plan
6. Test plans and procedures
7. Maintenance plan
Plan for your deliverables early in the project. (Establish a plan, use the plan to focus, fill in the plan as you go.) Evaluate existing documentation to examine whether the data is still valid. Existing documentation may provide key information regarding previous problems, agreements that have been made or projections where problems are expected to occur.
While examining the system and objectively asking questions of the users, you must consider what gives the most bang for the buck.
Base performance goals on the customer's system, established baseline:
1. Meet with stakeholders to determine their perception of what is needed.
2. Meet with primary users identified as knowledgeable personnel by the stakeholder. Observe problems. Understand the business process. List ALL customer issues.
3. Get baselines.
4. Based on preliminary information, agree on improvement goals with customer. Establish that applications performance improvements made by procedural changes, network architectural changes, database changes or SQL tuning. In many instances, retraining users, concurrent manager re-llocation, can affect major improvements.
Make sure that the customer agrees with your strategy and present the goals in a measurable context. Establish priorities for each area to be addressed. Document the baseline, performance goals. Goals should be stated clearly and concisely and with measurable parameters. For instance:
1. Sales order booking takes 45 seconds for one order line. We need it to run in five seconds for one order line.
2. Custom report: Update raw material costs takes 30 minutes to complete in off-peak hours. We need regular updates throughout the day without impacting sales order pricing.
3. Web pricing request takes 17 seconds to retrieve and display 4X4 cost/delivery matrix.
Be careful what you promise. Vendor published baselines represent aggressively tuned, optimal systems. Any promises you make to your users should be based on improving existing performance and any existing SLAs.
Evaluating the baseline will take into account information from all levels of users. A multilayer approach helps provide a 360-degree view of the system. Each type of user is asked the same questions and encouraged to give detailed examples. (When the user asserts, "Performance is rotten! I can't get my work done!" they are asked, "What are you doing when you notice it is so bad? Is it always bad, or is it sometimes bad? Does everyone have the same problem?"
The assessment process...
Is highly collaborative. It takes information from all types of users/interested parties.
Focuses on business. It identifies business processes, rather than imposing a technical solution.
Helps identify change impact. By identifying business processes affected, it assists in creation of important performance metrics.
Leads to measurable benefits. The metrics identified will be used in subsequent comparisons.
Supports future vision. The assessment and the performance tuning/analysis will often be used to support the justification for technical purchases such as more memory, additional hard drives or faster hard drives. It may also suggest changing existing configuration. On one system, a combination of disk shadowing and the selected RAID configuration created considerable degradation. What is a suitable RAID configuration for software development or manufacturing systems may not be suitable for databases.
May identify new models. As you discuss the system and obtain information, you may need to assure each set of users that you have no particular agenda and that your goal is to obtain as complete information as possible. Assure them that the results of the investigation will be available, and no solution technical or otherwise can be decided until all information is available. This is where having a planned set of deliverables is critical.
The goal for the assessment is to...
Define the problem(s) quickly. The users responses will probably be able to be combined into categories. These categories provide the general areas where you will concentrate your efforts.
Define the problem accurately. Your problems should be reported in terms of measurable behaviors. Often the performance tuning will be alleviated in stages. One set of "fixes" will improve performance, but additional improvements may be made with subsequent actions.
Identify the appropriate resources. Your problem definition will include the scope of the tuning's responsibility. For instance, while your information may reveal that hardware components are undersized, the purchase and installation may not be possible in near term. Identify the times that resources are available; certain types of testing may be required to be performed in off-hours, or some personnel may be unavailable during certain periods.
Solve the problem quickly. By structuring the problem statements, the analysis and the recommended solutions, you prepare the customer for your methods and the time it will take to analize.
You may provide test/reporting scripts for the user to evaluate functional timing. Generally, you will want to limit any testing tools you may provide to "power" users. Let them know that these scripts may impact performance, and should not be run continuously. Power users can be your best friends. They can alert you to trends that you may not otherwise see.
Click for step 3 of the performance tuning series.
Copyright 2003, Carol Francum.
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.