| Oracle7 Server Tuning |  Library |
 Product |
 Contents |
 Index |
| Oracle7 Server Tuning | Library |
Product |
Contents |
Index |
Methodology is key to success in performance tuning. Different tuning strategies offer diminishing returns, and it is important to use the strategies with the maximum gains first. Furthermore, systems with different purposes, such as online transaction processing systems and decision support systems, may require different approaches.
Business executives must collaborate with application designers to establish justifiable performance goals and set realistic performance expectations from the start. During design and development, the application designers can then determine which combination of system resources and available Oracle features will best meet these needs.
By designing a system to perform well, you can best minimize its eventual cost and frustration. Figure 2-1 illustrates the relative cost of tuning during the life of an application.
Cost of Tuning During the Life of an Application
To complement this view, Figure 2-2 shows that the relative benefit of tuning an application over the course of its life is inversely proportional to the cost expended.
Benefit of Tuning During the Life of an Application
As you can see, the most effective time to tune is during the design phase: you get the maximum benefit at the lowest cost.
Consider, for example, a bank which employs one teller and one manager. It has a business rule that the manager must approve any withdrawals exceeding $20. Upon investigation, you may find that there is a long queue of customers, and deduce that you need more tellers. You may add 10 more tellers, but then find that the bottleneck moves to the manager's function. However, the bank may determine that it is too expensive to hire additional managers. Regardless of how carefully you may tune the system using the existing business rule, getting better performance will be very expensive.
Upon stepping back, you may see that a change to the business rule may be necessary to make the system more scalable. If you change the rule such that the manager need only approve withdrawals exceeding $150, you have come up with a scalable solution. In this situation, effective tuning could only be done at the highest design level, rather than at the end of the process.
It is nonetheless possible to work reactively to tune an existing production system. To take this approach, start at the bottom of the method and work your way up, finding and fixing any bottlenecks. A common goal is to make Oracle run faster on the given platform. You may find, however, that both Oracle Server and the operating system are working well: to get additional performance gains you may have to tune the application or add resources. Only then can you take full advantage of the many features Oracle provides that can greatly improve performance when properly used in a well designed system.
Note that even the performance of well designed systems can degrade with use. Ongoing tuning is therefore an important part of proper system maintenance.
See Also: Part IV: Optimizing Oracle Instance Performance, which contains chapters that describe in detail how to tune CPU, memory, I/O, networks, contention, and the operating system.
Oracle7 Server Concepts: To quickly and easily find performance bottlenecks and determine the corrective action for a production system, you must have a firm understanding of Oracle Server architecture and features.
Step 3: Tune the Application Design
Step 4: Tune the Logical Structure of the Database
Step 7: Tune Memory Allocation
Step 8: Tune I/O and Physical Structure
Step 9: Tune Resource Contention
Step 10: Tune the Underlying Platform(s)
Note that this is an iterative process. Performance gains made in later steps may pave the way for further improvements in earlier steps--so additional passes through the tuning process may be useful.
The following figure illustrates the tuning method:
Performance problems encountered by the DBA may actually be caused by problems in design and implementation, or by inappropriate business rules. People commonly get in too deep when they write the business functions of an application. They document an implementation, rather than simply the function that must be performed. If business executives use care in abstracting the business function or requirement from the implementation, then designers have a wider field from which to choose the appropriate implementation.
Consider, for example, the business function of check printing. The actual requirement is to pay money to people; the requirement is not necessarily to print up pieces of paper. Whereas it would be very difficult to print up a million checks per day, it would be relatively easy to record that many direct deposit payments on a tape which could be sent to the bank for processing.
Business rules should be consistent with realistic expectations for the number of concurrent users, the transaction response time, and the number of records stored online that the system can support. For example, it would not make sense to run a highly interactive application over slow wide area network lines.
Similarly, a company soliciting users for an Internet service might advertise 10 free hours per month for all new subscribers. If 50,000 users per day signed up for this service, the demand would far exceed the capacity for a client/server configuration. The company should instead consider using a multi-tier configuration. In addition, the signup process must be simple: it should require only one connection from the user to the database, or connection to multiple databases without dedicated connections, making use of a multi-threaded server or transaction monitor approach.
The database design process generally undergoes a normalization stage, in which data is analyzed to ensure that no redundant data will be held anywhere. One fact should be stated in one and only one place in the database. Once the data is carefully normalized, however, you may need to denormalize it for performance reasons. You might, for example, decide that the database should hold frequently required summary values. Rather than forcing an application to recalculate the total price of all the lines in a given order each time it is accessed, you might decide to include the total value of each order in the database. You could set up primary key and foreign key indexes to access this information quickly.
Another data design consideration is the avoidance of contention on data. Consider a database 1 terabyte in size, on which a thousand users access only 0.5% of the data. This "hot spot" in the data could cause performance problems.
Try also to localize access to the data--localize it to each process, to each instance, and to each partition. Contention begins when access becomes remote, and the amount of contention determines scalability.
In Oracle Parallel Server, look for synchronization points--any point in time, or part of an application, which must run sequentially, one process at a time. The requirement of having sequential order numbers, for example, is a synchronization point which results from poor design.
See Also: Chapter 2, "Performance Tuning Method"
An example of intelligent process design is strategically caching data. For example, in a retail application you can select the tax rate only once at the beginning of each day, and cache it within the application. In this way you avoid retrieving the same information over and over during the course of the day.
At this level also, you can consider configuration of individual processes. For example, some PC users may be accessing the central system using mobile agents, whereas other users may be directly connected. Although they are running on the same system, the architecture is different. They may also require different mail servers an different versions of the application.
Performance problems due to contention often involve inserts into the same block, and using sequence numbers incorrectly. Use particular care in designing the use and location of indexes, the sequence generator, and clusters.
See Also: Using Indexes on page 8-6
See Also: Chapter 8, "Data Access Methods"
Process local areas include:
See Also: Chapter 13, "Tuning Memory Allocation".
"Just make it go as fast as you can" may sound like an objective, but it will be very difficult to determine whether this has been achieved. It will be even more difficult to tell whether your results have met the underlying business requirements. A more useful statement of objectives is the following: "We need to have as many as 20 operators each entering 20 orders per hour, and the packing lists produced within 30 minutes of the end of the shift."
Keep your goals in mind as you consider each tuning measure; consider its performance benefits in light of your goals.
Also bear in mind that your goals may conflict. For example, to achieve best performance for a specific SQL statement, you may have to sacrifice the performance of other SQL statements running concurrently on your database.
If you must cut a 4 hour run down to 2 hours duration, you will probably find that repeated timings take too long. Perform your initial trials against a test environment which exhibits a similar profile to the real one. For example, you could input some additional restrictive condition such as processing one department instead of all 500 of them. The ideal test case will run for more than 1 minute, so that improvements can be seen intuitively, as well as measured using timing features. It should run for less than 5 minutes, however, so that test execution does not consume an excessive proportion of the time available.
Bear in mind that Oracle's caching algorithms mean that the first time data is visited there is an additional overhead. Thus, if two approaches are tried one after the other, the second will always have a tactical advantage: data which it would otherwise have had to read from disk may be left in the cache.
Each time you think that you are onto something, try explaining it to someone else. Often you yourself will spot mistakes, simply from having gone through the discipline of articulating your ideas. For best results you should build a team of people to resolve performance problems. While a performance tuner can tune SQL statements without knowing the application in detail, the team should include someone who does understand the application and who can validate the solutions that the SQL tuner may devise.
One user, for example, had serious memory problems for a long time. In the morning the system ran well, but performance then dropped off very rapidly. A consultant called in to tune the system was told that a PL/SQL memory leak was the cause. As it turned out, this was not at all the problem. Rather, the user had set SORT_AREA_SIZE to 10 MB on a machine with 64 MB of memory, and had 20 users. When users came on to the system, the first time they did a sort they would get their sort area. The system thus was burdened with 200 MB of virtual memory and was hopelessly swapping and paging.
Many people will speculate about the cause of the problem. Ask questions of those affected, and of those responsible for the system. Listen to the symptoms that users describe, but do not accept prima facie their notions as to the cause!
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |