Release 8.1.5
A67775-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Tuning Release 8.1.5 A67775-01 |
|
A well planned methodology is the key to success in performance tuning. Different tuning strategies vary in their effectiveness. Furthermore, systems with different purposes, such as online transaction processing systems and decision support systems, likely require different tuning methods.
Topics in this chapter include:
"Oracle Expert". Oracle Expert automates the process of collecting and analyzing data. It also provides database tuning recommendations, implementation scripts, and performance reports.
See Also:
For best results, tune during the design phase rather than waiting to tune after implementing your system.
By far the most effective approach to tuning is the pro-active approach. Begin by following the steps described in this chapter under "Prioritized Tuning Steps" .
Business executives should work with application designers to establish performance goals and set realistic performance expectations. During design and development, the application designers can then determine which combination of system resources and available Oracle features best meet these needs.
By designing a system to perform well, you can minimize its implementation and on-going administration cost. 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.
The most effective time to tune is during the design phase: you get the maximum benefit for the lowest cost.
The tuning process does not begin when users complain about poor response time. When response time is this poor, it is usually too late 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.
For example, a bank that employs one teller and one manager. It has a business rule that the manager must approve withdrawals over $20. Upon investigation, you may find that there is a long queue of customers, and decide 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. In this example, regardless of how carefully you may tune the system using the existing business rule, getting better performance will be very expensive.
Alternatively, a change to the business rule may be necessary to make the system more scalable. If you change the rule so the manager only needs to approve withdrawals exceeding $150, you have created 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 possible to reactively 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 the 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.
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. This describes how to tune CPU, memory, I/O, networks, contention, and the operating system. Also refer to Oracle8i Concepts This text provides background on the Oracle server architecture and features so you can locate performance bottlenecks quickly and easily and determine the corrective action. |
The following steps provide a recommended method for tuning an Oracle database. These steps are prioritized in order of diminishing returns: steps with the greatest effect on performance appear first. For optimal results, therefore, resolve 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 these steps, reassess your database performance and decide whether further tuning is necessary.
Tuning 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 return 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 to use a multi-threaded 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. Designers sometimes provide far greater detail than is needed when they write business functions for an application. They document an implementation, rather than simply the function that must be performed. If business executives effectively distill business functions or requirements from the implementation, then designers have more freedom when selecting an 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 pieces of paper. Whereas it would be very difficult to print a million checks per day, it would be relatively easy to record that many direct deposit payments on a tape that 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, using a multi-threaded 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 when data is analyzed to eliminate data redundancy. With the exception of primary keys, any one data element should be stored only once in your database. After the data is normalized, however, you may need to denormalize it for performance reasons. You might, for example, decide that the database should retain frequently used summary values. For example, 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 always maintain a number representing the total value for 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 avoiding data contention. Consider a database 1 terabyte in size on which one 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 down to the partition level, process and instance levels. That is, localize access to data such that any process requiring data within a particular set of values be confined to a particular instance. Contention begins when several remote processes simultaneously attempt to access one particular set of data.
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.
Also consider implementing two Oracle8i enhancements that can help avoid contention:
Chapter 2, "Performance Tuning Methods", "Phase Three - Creating, Populating, and Refreshing the Database", and Oracle8i Concepts for discussions of partitioning and indexes.
See Also:
Business executives and application designers should 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 day.
At this level, you can also consider the configuration of individual processes. For example, some PC users may access the central system using mobile agents, where other users may be directly connected. Although they are running on the same system, the architecture for each type of user 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 the design, use, and location of indexes, as well as in using the sequence generator and clusters.
Before tuning the Oracle server, be certain your application is taking full advantage of the SQL language and the Oracle features designed to enhance application processing. Use features and techniques such as the following based on the needs of your application:
Understanding Oracle's query processing mechanisms is also important for writing effective SQL statements. Chapter 7, "Optimizer Modes, Plan Stability, and Hints", discusses Oracle's query optimizer and how to write statements to achieve optimal performance. This chapter also discusses optimizer statistics management and describes preserving execution plans with the plan stability feature.
Ensure that there is efficient data access. 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 also mean re-analyzing your design after you have built the database. You may want to further normalize your data or create alternative indexes. Upon testing the application, you may find you're still not obtaining the required response time. If this happens, look for more ways to improve the design.
Appropriate allocation of memory resources to Oracle memory structures can have a positive effect on performance.
Oracle8i 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 improves cache performance, reduces parsing of SQL statements, and reduces 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.
|
See Also:
Chapter 19, "Tuning Memory Allocation" and Oracle8i Concepts for information about memory structures and processes. |
Disk I/O tends to reduce the performance of many software applications. The Oracle server, however, is designed so its performance need not be unduly limited by I/O. Tuning I/O and physical structure involves these procedures:
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 types of contention:
See your platform-specific Oracle documentation for ways of tuning the underlying system. For example, on UNIX-based systems you might want to tune the following:
This section explains how to apply the tuning method:
Never begin tuning without having first established clear objectives: you cannot succeed without a definition of "success."
"Just make it go as fast as you can" may sound like an objective, but it is very difficult to determine whether this has been achieved. It is even more difficult to tell whether your results have met the underlying business requirements. A more useful statement of objectives is: "We need to have as many as 20 operators, each entering 20 orders per hour, and the packing lists must be 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 remember 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 repeatable tests. For example, if you identify a single SQL statement that is causing performance problems, 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 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.
For example, assume you need to reduce a 4-hour run to 2 hours. To do this, perform your trial runs using a test environment similar to the production environment. For example, you could impose additional restrictive conditions such as processing one department instead of all 500 of them. The ideal test case should run for more than 1 minute but probably not longer than 5, so you can intuitively detect improvements. You should also measure the test run using timing features.
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.
Remember that with Oracle's caching algorithms, the first time data is cached there is more overhead than when the same date is later accessed from memory. Thus, if you perform two tests, one after the other, the second test should run faster then the first. This is because data that the test run would otherwise have had to read from disk may instead be more quickly retrieved from the cache.
Keep records of the effect of each change by incorporating record keeping into the test script. You also should automate testing. Automation provides a number of advantages:
You should also carefully check test results derived from observations of system performance against the objective data before accepting them.
A common error made by inexperienced tuners is to adhere to preconceived notions about what may be causing the problem. The next most common error is to attempt various solutions at random.
A good way to scrutinize your resolution process is to develop a written description of your theory of what you think the problem is. This often helps you detect mistakes, simply from articulating your ideas. For best results, consult a team of people to help resolve performance problems. While a performance tuner can tune SQL statements without knowing the application in detail, the team should include someone who understands the application and who can validate the solutions the SQL tuner may devise.
Beware of changing something in the system by guessing. Or, once you have a hypothesis that you have not completely thought through, you may be tempted to implement it globally. Doing this in haste can seriously degrade system performance to the point where you may have to rebuild part of your environment from backups.
Try to avoid preconceptions when you address a tuning problem. Ask users to describe performance problems. However, do not expect users to know why the problem exists.
One user, for example, had serious system memory problems over a long period of time. During the morning the system ran well, but performance rapidly degraded in the afternoon. A consultant tuning the system was told that a PL/SQL memory leak was the cause. As it turned out, this was not at all the problem.
Instead, the user had set SORT_AREA_SIZE to 10MB on a machine with 64 MB of memory serving 20 users. When users logged on to the system, the first time they executed a sort their sessions were assigned to a sort area. Each session held the sort area for the duration of the session. So the system was burdened with 200MB of virtual memory, hopelessly swapping and paging.
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 performance targets have been met. You nonetheless must demonstrate this to two communities:
The next section describes application design tuning for designers and programmers.
