The overall tuning process is a systematic review based on a finite set of business processes. 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 will significantly impact 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. It is the metric the user sees, and 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. Select some basic, clearly understood problems and solutions. This is often referred to as "gathering the low-hanging fruit." At this point, you evaluate the information gathered during the evaluation of the current state.
Checking the business process first can have great impact on your tuning efforts. For instance, do customer support specialists indicate that their searches for open orders for a specific customer are slow? By looking at what their actual process is, you may find that the operator is not making good use of the workbench search fields, and could benefit from adding parameters to his or her search criteria. In this case, some additional one-one-one training can increase their satisfaction and the performance of their searches.
Next, obtaining benchmarks -- running specific searches at non-load times, non-peak, peak times gives a measure of how results vary throughout the day. If some operators have widely disparate results at the same time, you may need to look at network configuration. For example, if Joe and Mary in operator bay A have no problems, but operations in bay B take twice as long, the network configuration should be analyzed. Note: In one instance, only one operator on a floor was slow. It was determined that someone had connected that cubicle only to a different server at the local switch as a stopgap measure and never set it back. The moral of the story is that you need to look beyond the database for performance solutions.
Perform all benchmarks on a single, tuned PC or laptop client to ensure continuity. If necessary, move the client to perform benchmarks in different areas. If this is not possible, verify client configuration complies with company standards. Comparing results of disparate systems is extremely difficult.
Gather benchmarks for all processes identified as problematic. When all benchmarks have been derived, discuss which processes have priority and what performance goals are appropriate. You may wish to present phased (preliminary, acceptable and target) goals, as well. This is because the final solution may combine a series of user training, network and database or application tuning actions.
Documentation is used to identify the initial definition of the problem, initial conditions, goals agreed upon and the focus for the work to be done. Over time, additional documentation addresses what actions are recommended and the results of the actions. Finally, an assessment outlining the completed actions, results and recommendations for ongoing actions to maintain/further improve the increased performance is recommended.
As mentioned earlier, significant improvement can be made by retraining users in specific areas of application use. Additional training performed should be documented for later use by new users.
Because the solution for a specific area is often iterative, as efforts are defined and redefined, the work performed must be detailed and recorded scrupulously (and hopefully controlled in a change-management system). At each stage, results should be compared to the baseline; performance tuning is a balancing act, where action in one area may have deleterious effects on another.
Also because systems change over time, repeatable scripts will allow the user to verify the degree that changes have/have not occurred... (i.e. "This time last year we had 5,000 orders placed per day, at a average rate of 10 orders per hour for each call-in sales rep.")
The technical components of performance tuning
Technical architecture: Server and network tuning
If there is a problem with the Oracle server, such as an overloaded CPU, excessive memory swapping or a disk I/O bottleneck, no amount of tuning within the Oracle database is going to improve your performance.
Application architecture: Integration of middle tier. Apache Server, Forms/Reports Server.
When evaluating the performance of forms and reports, check both requirements and standards under which they were developed. Forms and reports created using standard defaults may benefit from reducing or relaxing the standards for performance reasons. In one example, approximately 20 Web forms were completely redesigned when it was determined extensive use of check constraints impacted performance. For many of the forms, data input was prevalidated; when the constraints were removed, overall performance improved over 100%.
(a) Instance tuning: Tuning the Oracle SGA is the next step, and all of the Oracle initialization parameters must be reviewed to ensure that the database has been properly configured. This phase of Oracle tuning is directed at looking for resources shortages in the db_block_buffers, shared_pool_size and sort_area_size. Investigate important default parameters for Oracle, such as optimizer_mode.
(b) Object tuning: This phase of tuning looks at the setting for Oracle tables and indexes. Settings such as PCTFREE, PCTUSED and FREELISTS can have a dramatic impact on Oracle performance.
SQL tuning: This is the most time-consuming tuning operation because there can be many thousands of individual SQL statements that access the Oracle database. At a high level, we identify the most common SQL statements, tune each one by carefully reviewing the execution plan for the SQL and adjust the execution plan using Oracle hints.
Networks are increasingly more important as we move to global businesses. Work with your telecom provider to evaluate link capacity and utilization. Request detailed reports. Consider resizing to reduce costs on underutilized links or upgrading those close to capacity. Evaluate network drivers. Is there a more recent, downloadable version? Vendors often update their drivers. This can make a significant improvement.
An up-to-date, accurate, detailed network diagram of both the logical and physical network locating all servers and users can be used to troubleshoot and predict problem areas. Check transaction processing routes to and from the server... if they are not the same, check the routing tables on each. What, if any, network management systems are used? Can any devices be placed in diagnostic mode during evaluations? Check that software and hardware are not operated routinely in diagnostic mode, as this will generally affect performance. Determine number of packet retries and collisions, bandwith utilizaton. Note any traffic management or priority queuing set on any device. Detail any contracts or service level agreements for the LAN and WAN. Identify any protocols installed on the clients.
Compare findings with benchmarks to establish size and extent of performance issues.
Implementing the plan
Having evaluated the current state, and performed numerous measurements, actual changes to the system must be controlled and documented. Because you will most likely continue to assess the performance measures, changes should be documented in detail. A good practice is to establish a repository of network and database assessment scripts. Similarly, record the steps of any changes. All changes should be evaluated on a test system before rolling out to production systems. Before implementing any changes, and each successive change, be sure to make a complete backup and test your ability to restore conditions to their original state. Make only one change at a time. Recovering after multiple changes is frustrating at best and may be impossible.
The sequence of changes will be based on priorities discussed with the users, management and technical personnel. You will also want to run baseline assessment scripts to determine whether the changes are effective. Occasionally, a change may not have the intended effect. This is what makes performance tuning an iterative task.
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.