Release 8.0
A58246-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Tuning Release 8.0 A58246-01 |
|
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.
Topics in this chapter include
See Also: "Oracle Expert" on page 4-12. Oracle Expert automates the process of collecting and analyzing data, and contains rules that provide database tuning recommendations, implementation scripts, and reports.
For dramatically better results, tune during the design phase rather than waiting to tune after implementing your system.
By far the most effective approach to tuning is to work proactively. Start off at the beginning of the method described in this chapter, and work your way down.
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 minimize its eventual cost and frustration. Figure 2-1 illustrates the relative 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.
As you can see, the most effective time to tune is during the design phase: you get the maximum benefit at the lowest cost.
Many people believe the tuning process begins when users complain about poor response time. This is usually too late in the process to use some of the most effective tuning strategies. At that point, if you are unwilling to completely redesign the application, you may only improve performance marginally by reallocating memory and tuning I/O.
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 4: Optimizing Oracle Instance Performance, which contains chapters that describe in detail how to tune CPU, memory, I/O, networks, contention, and the operating system.
Oracle8 Concepts: To find performance bottlenecks quickly and easily and determine the corrective action for a production system, you must have a firm understanding of Oracle Server architecture and features.
The recommended method for tuning an Oracle database prioritizes steps in order of diminishing returns: steps with the greatest impact on performance are listed first. For optimal results, therefore, tackle tuning issues in the order listed: from the design and development phases through instance tuning.
Step 1: Tune the Business Rules
Step 3: Tune the Application Design
Step 4: Tune the Logical Structure of the Database
Step 5: Tune Database Operations
Step 7: Tune Memory Allocation
Step 8: Tune I/O and Physical Structure
Step 9: Tune Resource Contention
After completing the steps of the tuning method, reassess your database performance and decide whether further tuning is necessary.
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.
Figure 2-3 illustrates the tuning method:
Decisions you make in one step may influence subsequent steps. For example, in step 5 you may rewrite some of your SQL statements. These SQL statements may have significant bearing on parsing and caching issues addressed in step 7. Also, disk I/O, which is tuned in step 8, depends on the size of the buffer cache, which is tuned in step 7. Although the figure shows a loop back to step 1, you may need to loop back from any step to any previous step.
For optimal performance, you may have to adapt business rules. These concern the high-level analysis and design of an entire system. Configuration issues are considered at this level, such as whether or not to use a multithreaded server system-wide. In this way, the planners ensure that the performance requirements of the system correspond directly to concrete business needs.
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 multitier 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 multithreaded server or transaction monitor approach.
In the data design phase, you must determine what data is needed by your applications. You need to consider what relations are important, and what their attributes are. Finally you need to structure the information to best meet performance goals.
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, that must run sequentially, one process at a time. The requirement of having sequential order numbers, for example, is a synchronization point that results from poor design.
Consider two Oracle8 enhancements that can help you to tune the data design to avoid contention:
Business executives and application designers need to translate business goals into an effective system design. Business processes concern a particular application within a system, or a particular part of an application.
An example of intelligent process design is strategically caching data. For example, in a retail application you can select the tax rate 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 and different versions of the application.
After the application and the system have been designed, you can plan the logical structure of the database. This primarily concerns fine-tuning the index design, to ensure that the data is neither over- nor under-indexed. In the data design stage (Step 2) you determine the primary and foreign key indexes. In the logical structure design stage you may create additional indexes to support the application.
Performance problems due to contention often involve inserts into the same block or incorrect use of sequence numbers. Use particular care in designing the use and location of indexes, the sequence generator, and clusters.
See Also: "Using Indexes" on page 10-2
System designers and application developers must understand Oracle's query processing mechanism to write effective SQL statements. Chapter 8, "Optimization Modes and Hints", discusses Oracle's query optimizer and how to write statements that achieve the fastest results.
Before tuning the Oracle Server itself, be certain that your application is taking full advantage of the SQL language and the Oracle features designed to speed application processing. Use features and techniques such as the following based on the needs of your application:
See Also: "Part 3: Optimizing Database Operations"
Ensure that there is efficient access to data. Consider the use of clusters, hash clusters, B*-tree indexes and bitmap indexes.
Ensuring efficient access may mean adding indexes, or adding indexes for a particular application and then dropping them again. It may mean revisiting your design after you have built the database. You may want to do more normalization or create alternative indexes at this point. Upon testing the application you may find that you're still not obtaining the required response time. Look for more ways to improve the design.
See Also: Chapter 10, "Data Access Methods"
Appropriate allocation of memory resources to Oracle memory structures can have a large impact on performance.
Oracle8 shared memory is allocated dynamically to the following structures, which are all part of the shared pool. Although you explicitly set the total amount of memory available in the shared pool, the system dynamically sets the size of each structure contained within it:
You can explicitly set memory allocation for the following structures:
Proper allocation of memory resources can improve cache performance, reduce parsing of SQL statements, and reduce paging and swapping.
Process local areas include:
Be careful not to allocate to the system global area (SGA) such a large percentage of the machine's physical memory that it causes paging or swapping.
Disk I/O tends to reduce the performance of many software applications. Oracle Server, however, is designed so that its performance need not be unduly limited by I/O. Tuning I/O and physical structure involves these procedures:
See Also: Chapter 15, "Tuning I/O"
Concurrent processing by multiple Oracle users may create contention for Oracle resources. Contention may cause processes to wait until resources are available. Take care to reduce the following kinds of contention:
See Also: Chapter 18, "Tuning Resource Contention"
See your platform-specific Oracle documentation to investigate ways of tuning the underlying system. For example, on UNIX-based systems you might want to tune the following:
See Also: Chapter 17, "Tuning the Operating System"
This section explains how to apply the tuning method:
Never begin tuning without having first established clear objectives: you cannot succeed if there is no definition of "success."
"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.
Create a series of minimum reproducible cases. For example, if you manage to identify a single SQL statement that is causing a performance problem, then run both the original and the revised version of that statement in SQL*Plus (with the SQL trace facility or Oracle Trace enabled) so that you can see statistically the difference in performance. In many cases, a tuning effort can succeed simply by identifying one SQL statement that was causing the performance problem.
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 that exhibits a profile similar to the real one. For example, you could impose 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.
With a minimum repeatable test established, and with a script both to conduct the test and to summarize and report the results, you can test various hypotheses to see the effect.
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.
Keep records of the effect of each change: incorporate record keeping in the script being used to run the test. You should automate the testing process for a number of reasons:
Anecdotal results from tests should be checked against the objective data before being accepted.
A common error made by inexperienced tuners is to cling to preconceived notions about what may be causing the problem at hand. The next most common error is to try various approaches at random.
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.
Beware of doing something rash. Once you have a hypothesis, you may be tempted to implement it globally throughout the system and then wait to see the results. You can ruin a perfectly good system in this way!
Try to avoid preconceptions when you come to a tuning problem. Get users to tell you the symptoms they perceive-but do not expect them to know why the problem exists.
One user, for example, had serious system memory problems over a long period of 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!
Avoid seizing on panaceas that may be bandied about in database folklore, such as "set the GO_FASTER parameter and everything will work faster." Be wary of apocryphal tales-such as the false notion that all tables must be in a single extent for performance to be acceptable.
One of the great advantages of having targets for tuning is that it becomes possible to define success. Past a certain point, it is no longer cost effective to continue tuning a system.
As the tuner you may be confident that the performance targets have been met. You nonetheless must demonstrate this to two communities:
