OracleSource

Tuning requires several steps.

Start Early :
Performance management must span the application or project
continuously to be fully effective. You should consider it
at the design stage.

Set Objectives :
Do not attempt to tune everything.
Use the ROI (Return on investment) methodology to identify
the portions of the application or project to tune.
Define your objectives in a form accepted and agreed to
by all the interested parties.
Service level agreements (SLAs) form an increasingly
important part of setting the objectives of operations
groups and facilities management teams. You can successfully
extend this concept into the specification of application
requirements.

Tune and Monitor Conformance :

After you have set out and agreed upon the objectives,
you are ready to tune and to reach those objectives,
monitoring your progress as you tune.
You should keep detailed records about the level of
conformance with the requirement.
You should publish indicative measures at regular
intervals, highlighting any deviations or trends.

Work Together :
Database administrators, system administrators, and
programmers should work together as a team, not as adversaries.

Handle Exceptions and Changes :
When effective monitoring is in place and supported at
the same level as the rest of the application, you must
react promptly to exceptions when they are reported.
Use the available data to select a course of action and
then analyze the resulting performance to determine
whether your actions are successful.
If overall changes in use patterns or equipment capability
occur, then you should consider setting new objectives.

Factors to Be Managed

 Schema
• Data design
• Indexes
 Application
• SQL statements
• Procedural code
 Instance
 Database
 User expectations

Performance management can be divided into the following five areas.
Although the areas are separate, they are also interdependent and require different skill sets.
• Schema : tuning deals with the physical structure of the data. If an application has inadequate or inappropriate data design, then tuning the physical allocation, providing indexes, or rewriting programs will not overcome the problem.
• Application : tuning deals with the business functions and the program modules that implement the functions. Tuning the procedural code for the type of application and tuning the embedded SQL statements are also included in this area. If an application is well designed, it may still perform badly. A common reason for this is badly written SQL.
• Instance : tuning deals with the installation of the Oracle9i Server and how it uses memory.
• Database : tuning deals with managing the physical arrangement of data on the disk.
• User Expectations : Users expect consistent performance. They can cope with slower application functions if they understand why the application is slower than usual. The project team should try to build a realistic user expectation of performance, possibly including application messages to warn operators that they are requesting operations that are resource-hungry. The best time to do this is before the design and build phases and as part of the transition phase.


Performance Problems

 Inadequate consumable resources
• CPU
• I/O
• Memory (may be detected as an I/O problem)
• Data communications resources
 Inadequate design resources
 Locking
 Critical Resource
 Excessive Demand

Performance problems occur when a function takes longer to perform than the time allowed. This is because a resource of a particular type is insufficient or inadequate. The resource may be a physical resource, such as available memory buffers to store data blocks, or an artificial resource, such as a lock.
• Inadequate Consumable Resources : A resource may simply be inadequate to meet the need under any circumstances. For example, if you want a function to complete in under one second, a network with a message turnaround time of two seconds will never meet the target. If the limiting factor is a consumable resource, such as CPU power, all the users of that resource are affected.
• Inadequate Design Resources : If the limiting factor is the contention of processes for a design resource, such as a lock, then only users of those specific processes are likely to be affected.
• Locking : Contention due to locking by other transactions or applications might be a problem.

Critical Resource

 Performance depends on the following:
• How many clients need the resource
• How long they must wait for it
• How long they hold it
 Consider limiting demand to maintain acceptable response times.

• Response time is defined as the service time plus the wait time to accomplish a certain task.
• As demand for a resource with a single server increases toward the service rate, queues build up with the queue length increasing exponentially for each increase in demand.
• Even if there are many servers, you can observe the same effect with a single queue. However, multiple servers give a valuable smoothing effect when there is a wide variation in the time for which a resource is occupied by one of its clients before it is available for reallocation.
• The goal is to design, engineer, and tune the system so that the load is never permitted to slow service completion times below the acceptable level.

Note :
• The response time curve shows that the time to service completion grows exponentially when the demand rate is high enough.
• The horizontal dashed line in the graph represents the maximum time to service completion that is still an acceptable response time, as mentioned in the last bullet point.
• At the intersection of the two dashed lines, the response time curve pinpoints the maximum amount of demand that can be handled while still maintaining that acceptable service time.

Excessive Demand

 Greatly increases response time and reduces throughput
 Should be prevented as much as possible by limiting demand to a level that still allows reasonable throughput


• Throughput is defined as the total amount of work accomplished by the system in a given amount of time.
• Too many processes using a system simultaneously may result in the following symptoms.
• Increased Response Time : Most users know and understand the effects of queues on increasing response time. They may be prepared to accept slower response at peak times if the effect is linear. However, both statistical theory and experience show that once response time starts to deteriorate, small increases in load can have a severe effect, which is unacceptable to users.
• Reduced Throughput : Any marked degradation in response time is likely to disrupt the work rate of the users affected. Many people do not understand that adding more users to a system significantly decreases the overall throughput of the system. If system throughput is important, you must ensure that the number of users does not exceed the threshold at which the throughput starts to decline. It is best to limit the number of users mechanically. This may force a change in working patterns to cope with the restriction, but spreading the peak periods across the working day can improve the way the system is used.

Note :
• This is the same graph as on the one used previous slide, with one addition.
• The throughput curve shows that at first, the amount of work that is accomplished goes up with the amount of work that is given to it; that is, the demand rate.
• However, when the demand rate exceeds the amount in which acceptable service times can be maintained, the throughput drops dramatically.