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 -- CPU Parameters SELECT name ,isdefault ,value ,default_value ,description FROM v$parameter WHERE INSTR(LOWER(name),'cpu') >0 ;
23522