Oracle Database CPU Utilization Analysis
  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
     vmstat -a

  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
     netstat

  7. ps -ef | grep "search value" -> All executing processes that match the 
                                     search value
     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

Memory Analysis Query Next

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 31st, 2014

  249