Donald K. Burleson Oracle Tips

Finding Oracle server bottlenecks

Most Oracle databases exist under UNIX, and the UNIX vmstat utility is an excellent source for information about how busy the server might be.  The vmstat utility is available on almost every type of UNIX, and it is the job of the Oracle professional to make sure that their Oracle database has enough server resources.

Each dialect of vmstat reports different information about the current status of the server. Despite these dialect differences, there are only a small number of metrics that are important for Oracle server monitoring. These metrics include:

• r – runqueue – The runqueue value shows the number of tasks executing and waiting for CPU resources. When this number exceeds the number of CPUs on the server, then a CPU bottleneck exists, and some tasks are waiting for execution.

• pi – page in – A page in operation occurs when the server is experiencing a shortage of RAM memory. While all virtual memory server will page out to the swap disk, page- in operations show that the server has exceeded the available RAM storage.  Any non-zero value for pi indicates excessive activity as RAM memory contents are read in from the swap disk.

• us – User CPU – This is the amount of CPU that is servicing user tasks.

• sy – This is the percentage of CPU being used to service system tasks.

• id – idle - This is the percentage of CPU that is idle

• wa – wait – (IBM-AIX only) – This show the percentage of CPU that is waiting on external operations such as disk I/O.

Note that all of the CPU metrics are expressed as percentages. Hence, all of the CPU values (us+sy+id+wa) will always sum to 100.

Waiting CPU resources can be shown in Unix vmstat command output as the second column under the “kthr” (kernel thread state change) heading. Tasks may be placed in the wait queue (“b”) if they are waiting on a resource, while other tasks appear in the run queue (“r”) column. As we see in Figure 5-1, server tasks are queued for execution by the server.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2005_1_awr_proactive_tuning.htm