End Date: 13-04-2019 10:00 AM IST
Part III, "Optimizing Instance Performance" discusses the factors involved in the tuning and optimizing of an Oracle database instance.
When considering instance tuning, take care in the initial design of the database to avoid bottlenecks that could lead to performance problems. In addition, you must consider:
Allocating memory to database structures
Determining I/O requirements of different parts of the database
Tuning the operating system for optimal performance of the database
After the database instance has been installed and configured, you must monitor the database as it is running to check for performance-related problems.
126.96.36.199 Performance Principles
Performance tuning requires a different, although related, method to the initial configuration of a system. Configuring a system involves allocating resources in an ordered manner so that the initial system configuration is functional.
Tuning is driven by identifying the most significant bottleneck and making the appropriate changes to reduce or eliminate the effect of that bottleneck. Usually, tuning is performed reactively, either while the system is in preproduction or after it is live.
The most effective way to tune is to have an established performance baseline that you can use for comparison if a performance issue arises. Most database administrators (DBAs) know their system well and can easily identify peak usage periods. For example, the peak periods could be between 10.00am and 12.00pm and also between 1.30pm and 3.00pm. This could include a batch window of 12.00am midnight to 6am.
It is important to identify these peak periods at the site and install a monitoring tool that gathers performance data for those high-load times. Optimally, data gathering should be configured from when the application is in its initial trial phase during the QA cycle. Otherwise, this should be configured when the system is first in production.
Ideally, baseline data gathered should include the following:
Application statistics (transaction volumes, response time)
Operating system statistics
Disk I/O statistics
In the Automatic Workload Repository, baselines are identified by a range of snapshots that are preserved for future comparisons. See "Overview of the Automatic Workload Repository".
188.8.131.52 The Symptoms and the Problems
A common pitfall in performance tuning is to mistake the symptoms of a problem for the actual problem itself. It is important to recognize that many performance statistics indicate the symptoms, and that identifying the symptom is not sufficient data to implement a remedy. For example:
Slow physical I/O
Generally, this is caused by poorly-configured disks. However, it could also be caused by a significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.
Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes.
Excessive CPU usage
Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs.
184.108.40.206 When to Tune
There are two distinct types of tuning: