Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Recommendations
The following paragraphs provide recommendations for changes in:
- I/O
- CPU
- Oracle
I/O
Changes in the I/O infrastructure, such as a technology change, would not map to a substantial improvement on system performance. Most performance gain is expected to be obtained by fine tuning the application layer and improving the efficiency of its usage of the I/O subsystem.
Reduce the "cost of I/O" by optimizing the application layer. Sometimes by just reducing the amount of I/O, each I/O can become cheaper because less I/O means less contention on I/O resources. This could only be achieved by an in-depth analysis of the I/O demand by the application and database design. This path would lead to further data path multithreading, improving efficiency on the I/O infrastructure. Fine tuning of parallel queries and Oracle partitioning, with its associated mapping to the I/O infrastructure, for example, can improve performance substantially. See more details on the Oracle-related recommendations in the "Oracle" paragraph.
Reduce the "cost of I/O" by optimizing the infrastructure layer. Some performance improvement can be obtained by increasing the number of spindles and/or controllers for the "hot spot" devices. This is associated with the distribution of database hot spots (tablespaces, datafiles and indexes) over multiple disk arrays. Further, volume-level stripping can also be considered an alternative to improve I/O response time.
CPU
Alleviate the CPU share spent in I/O wait mode in order to improve overall system performance. Note, however, that adding CPU power to such an out-of-balance system, without changing its I/O utilization pattern, may do nothing to improve performance. In fact, in some cases it makes the situation worse, because more CPU power will drive more I/O requests. Since the disks cannot handle the present load, the queues just get longer, requiring more CPU time for I/O queue management and on I/O wait.
Improve the effectiveness of I/O subsystem utilization first and then verify the impact on CPU utilization. Moderate CPU expansion may reduce CPU wait in user mode, potentially improving performance by that factor (6 to 8 percent)—see associated risk n reported on the item previously. Simulations on the effect of increasing CPU power are presented in the "Resolving CPU and I/O Bottlenecks Through Modeling and Capacity Planning" section of this article and indicate its convenience.
Oracle
Oracle parallel queries and table partitioning have a major impact on specific queries response time. Many instances of the same query, however, may be touching a specific I/O path or media at the same time. It would better to explore the possibility of duplicating "hot spots" from the application perspective, such as indexes, files, or partitions, and implement some kind of load balancing at the application or Oracle level. This can greatly improve I/O performance by improving I/O multithreading efficiency and manageability. This approach may be particularly feasible on a read-only database.
Oracle server processes read from database files when a requested data item cannot be found within the Oracle buffer cache. Waiting for data from disk is one of the most common reasons for delays experienced by server processes, so any reduction in these delays helps to improve performance. You can optimize data file I/O by trying to improve the hit rate in the buffer cache and striping data files across a sufficient number of disks. The alternative is to reduce the number of physical reads per executions at queries and the Oracle partitioning level.
Review system parameters for large Oracle databases. Changing some system parameters can have a positive impact on performance. For example, the /etc/system parameter, shmmax, can be changed to 0xffffffff.