Optimum CPU utilization on the server keeps Oracle database performing efficiently.
If CPU utilization in the server reaches up to 100%, and for other reasons such
as too many open connections etc. the database may reject connections and shut
down all the dependent applications. The CPU utilization has to be monitored
both in the database and at the server/host.
In linux/unix servers, the following commands can be used for CPU Utilization,
network I/O analysis:
1. top -> shows the top CPU processes
top [-] [d delay] [p pid] [q] [c] [C] [S] [s] [i] [n iter] [b]
2. sar -> System Activity Report
(example below shows CPU utilization every 5 seconds display 10 lines)
sar -u 5 10
3. vmstat -> virtual memory statistics
4. iostat -> Report CPU and I/O statistics (example below shows I/O stats once
in 5 seconds)
iostat -d 5
5. mpstat -> Microprocessor statistics (example below shows 5 statistics among
all processors at 1 second interval.
mpstat 1 5
6. netstat -> Network status, open connections
7. ps -ef | grep "search value" -> All executing processes that match the
ps -ef | grep "ora"
Note: For all available options, use man page for respective commands shown above
-- Useful query
SELECT n.statistic#, CAST(n.name AS VARCHAR2(40)) name, s.value
FROM v$statname n
INNER JOIN v$mystat s
ON n.statistic# = s.statistic#
WHERE INSTR(LOWER(n.name),'memory') >0
-- Query Output