17 - Hardware Resource Usage Tuning

Abstract

This chapter introduces hardware resource usage tuning, specifically dealing with central processing unit (CPU) usage, memory usage, and I/O usage. More of the details on how to tune these various areas of an oracle installation are also covered in this chapter. CPU usage can be best assessed at peak times and thus peak CPU workload times. High network activity with many small queries can cause unnecessarily high CPU activity. It is often the case that breaking SQL code into object-like, independent chunks is a cause of excessive network activity. One should be aware that breaking SQL code into small constituent parts is more natural to object designs than relational design. On the contrary, reduction of complexity into smaller parts leads to simplicity in tuning, especially when it comes to SQL code. There is a fine balance between making transactions small enough and using large complex SQL code join statements. Oracle Database uses memory as a number of buffers to cache both instructions and data. These buffers are placed into both RAM and virtual memory. The purpose of the buffers is to help speed up performance. It is usually best to contain all buffers in RAM and not spread them into virtual memory.