4/13/13
Intr oduction to Or acl e Per for mance Tuning
Oracle8 Tuning Oracle8 Tu ning Release 8.0 A58246-01
Library Product Contents Index
1 Intr oduction oduction to Oracl racle e Performance Performance Tun T unii ng The Oracle Oracle Server is a sophisticated sophisticated and highly tunable software product. Its flexibility allows you to make small adjustments that affect database performance. By tuning your system, you can tailor its performance to best meet your needs. This chapter gives an overview of tuning issues. Topics in this chapter include: What Is Performance Tuning? Who Tunes? Setting Set ting Performance Targets Targets Setting Set ting User Expectations Evaluating Ev aluating Performance Performance
What Is Performance Tuning? Performance must be built in! Performance tuning cannot be performed optimally after a system is put into producti production on.. To To achi achieve eve perform performan ance ce targ targets of response response tim time, throu throug ghput, put, and and constra constraiints you you must tun tunee application analysis, design, and implementation. This section introduces some fundamental concepts: Trade-offs Between Response Time and Throughput Critical Resources Effects Ef fects of Excessiv Excess ivee Demand Adjust djustments ments to Relieve Problems
T rade-of rade-offs fs Between Between Response Response T ime and Through T hroughpu putt Goals for tuning vary, depending on the needs of the application. Online transaction processing (OLTP) applications define performance in terms of throughput. These applications must process thousands or even millions of very small transactions per day. By contrast, decision support systems (DSS applications) define perform performan ance ce in in terms terms of response response tim time. Dem Demands ands on the the database database that that are made by users of DSS appli applications cations vary dramatically. One moment they may enter a query that fetches only a few records, and the next moment they may enter a massive parallel query that fetches and sorts hundreds of thousands of records from various different tables. Throughput becomes more of an issue when an application must support a large number of users running DSS queries. docs.or acl e.com/cd/A58617_01/ser ver .804/a58246/intr o.htm
1/6
4/13/13
Introduction to Oracle Performance Tuning
Response Time Because response time equals service time plus wait time, you can increase performance in two ways: by reducing service time by reducing wait time Figure 1-1 illustrates ten independent tasks competing for a single resource.
Figure 1-1 Sequential Processing of Multiple Independent Tasks
In this example only task 1 runs without having to wait. Task 2 must wait until task 1 has completed; task 3 must wait until tasks 1 and 2 have completed, and so on. (Although the figure shows the independent tasks as the same size, the size of the tasks will vary.) In parallel processing, if you have multiple resources, then more resources can be assigned to the tasks. Each independent task executes immediately using its own resource: no wait time is involved. Note:
System Throughput System throughput equals the amount of work accomplished in a given amount of time. Two techniques of increasing throughput exist: Get more work done with the same resources (reduce service time). Get the work done quicker by reducing overall response time. To do this, look at the wait time. You may be able to duplicate the resource for which all the users are waiting. For example, if the system is CPU bound you can add more CPUs.
Wait Time The service time for a task may stay the same, but wait time will go up as contention increases. If many users are waiting for a service that takes 1 second, the tenth user must wait 9 seconds for a service that takes 1 second.
Figure 1-2 Wait Time Rising with Increased Contention for a Resource docs.oracle.com/cd/A58617_01/server.804/a58246/intro.htm
2/6
4/13/13
Introduction to Oracle Performance Tuning
Critical Resources Resources such as CPUs, memory, I/O capacity, and network bandwidth are key to reducing service time. Added resources make possible higher throughput and swifter response time. Performance depends on the following: How many resources are available? How many clients need the resource? How long must they wait for the resource? How long do they hold the resource? Figure 1-3 shows that as the number of units requested rises, the time to service completion rises.
Figure 1-3 Time to Service Completion vs. Demand Rate
To manage this situation, you have two options: You can limit demand rate to maintain acceptable response times. Alternatively, you can add multiple resources: another CPU or disk.
Effects of Excessive Demand Excessive demand gives rise to: greatly increased response time reduced throughput docs.oracle.com/cd/A58617_01/server.804/a58246/intro.htm
3/6
4/13/13
Introduction to Oracle Performance Tuning
If there is any possibility of demand rate exceeding achievable throughput, a demand limiter is essential.
Figure 1-4 Increased Response Time/Reduced Throughput
Adjustments to Relieve Problems Performance problems can be relieved by making the following adjustments: adjusting unit You can relieve some problems by using less resource per transaction or by reducing service consumption time. Or you can take other approaches, such as reducing the number of I/Os per transaction. adjusting functional demand
Other problems can be abated by rescheduling or redistributing the work.
adjusting capacity
You can also relieve problems by increasing or reallocating resource. If you start using multiple CPUs, going from a single CPU to a symmetric multiprocessor, you will have multiple resources you can use.
For example, if your system's busiest times are from 9:00 AM until 10:30, and from 1:00 PM until 2:30, you can plan to run batch jobs in the background after 2:30, when more capacity is available. In this way you can spread out the demand more evenly. Alternatively, you can allow for delays at peak times.
Figure 1-5 Adjusting Capacity and Functional Demand
docs.oracle.com/cd/A58617_01/server.804/a58246/intro.htm
4/6
4/13/13
Introduction to Oracle Performance Tuning
Who Tunes? Everyone involved with the system has some role in the tuning process. When people communicate and document the system's characteristics, tuning becomes significantly easier and faster.
Figure 1-6 Who Tunes the System?
Business executives must define and then reexamine business rules and procedures to provide a clear and adequate model for application design. They must identify the specific kinds of rules and procedures that can influence the performance of the whole system. Application designers must design around potential performance bottlenecks. They must communicate the system design so that everyone can understand the flow of data in an application. Application developers must communicate the implementation strategies they choose so that modules and SQL statements can be quickly and easily identified during statement tuning. Database administrators (DBAs) must carefully monitor and document system activity so that they can identify and correct unusual system performance. Hardware and software administrators must document and communicate the configuration of the system so that everyone can design and administer the system effectively. Decisions made in application development and design have the most impact on performance. Once the application is deployed, the database administrator usually has the primary responsibility for tuning-within the limitations of the design. Chapter 3, "Diagnosing Performance Problems in an Existing System" for keys that can help database administrators (DBAs) to identify performance problems and solve them reactively. See Also:
Setting Performance Targets Whether you are designing or maintaining a system, you should set specific performance goals so that you know when to tune. You can spend needless time tuning your system without significant gain if you attempt to alter initialization parameters or SQL statements without a specific goal. When designing your system, set a specific goal: for example, an order entry response time of less than three seconds. If the application does not meet that goal, identify the bottleneck causing the slowdown (for example, I/O contention), determine the cause, and take corrective action. During development, you should test the application to determine whether it meets the designed performance goals before deploying the application. docs.oracle.com/cd/A58617_01/server.804/a58246/intro.htm
5/6
4/13/13
Introduction to Oracle Performance Tuning
Tuning is usually a series of trade-offs. Once you have determined the bottlenecks, you may have to sacrifice some other areas to achieve the desired results. For example, if I/O is a problem, you may need to purchase more memory or more disks. If a purchase is not possible, you may have to limit the concurrency of the system to achieve the desired performance. However, if you have clearly defined goals for performance, the decision on what to trade for higher performance is simpler because you have identified the most important areas.
Setting User Expectations Application developers and database administrators must be careful to set appropriate performance expectations for users. When the system carries out a particularly complicated operation, response time may be slower than when it is performing a simple operation. In cases like this, the slower response time is not unreasonable. If a DBA should promise 1-second response time, consider how this might be interpreted. The DBA might mean that the operation would take 1 second in the database-and might well be able to achieve this goal. However, users querying over a network might experience a delay of a couple of seconds due to network traffic: they will not receive the response they expect in 1 second.
Evaluating Performance With clearly defined performance goals, you can readily determine when performance tuning has been successful. Success depends on the functional objectives you have established with the user community, your ability to measure objectively whether or not the criteria are being met, and your ability to take corrective action to overcome any exceptions. The rest of this tuning manual describes the tuning methodology in detail, with information about diagnostic tools and the types of corrective actions you can take. DBAs who are responsible for solving performance problems must keep a wide view of the all the factors that together determine response time. The perceived area of performance problems is frequently not the actual source of the problem. Users in the preceding example might conclude that there is a problem with the database, whereas the actual problem is with the network. A DBA must monitor the network, disk, CPU, and so on, to find the actual source of the problem-rather than simply assume that all performance problems stem from the database. Ongoing performance monitoring enables you to maintain a well-tuned system. Keeping a history of the application's performance over time enables you to make useful comparisons. With data about actual resource consumption for a range of loads, you can conduct objective scalability studies and from these predict the resource requirements for load volumes you may anticipate in the future. See Also:
Chapter 4, "Overview of Diagnostic Tools"
Copyright © 1997 Oracle Corporation. Prev
Next
docs.oracle.com/cd/A58617_01/server.804/a58246/intro.htm
All Rights Reserved.
Library Product Contents Index
6/6
