Using VMSTAT

vmstat = virtual memory stats

Procs

• r - the number of processes waiting for run time


• b - the number of processes in uninterruptible sleep

Shows you if there are processes blocking your system from running smoothly. The b column shows processes in sleep state. If these numbers are high then you have some waiting processes and may have some problems.

Example r = 5 means that 5 processes are currently waiting to execute. When the CPU is pegged at 100% the severity of the CPU starvation wont be reflected in the percentage of CPU utilization but the run queue will clearly show the impact as processes back up in the queue

b can be used to show CPU power…if the value is constantly greater than zero then you may have a CPU bottleneck. Use ps to list out the most CPU intensive processes.

Memory

• swpd - amount of virtual memory used


• free - freem memory in the system


• buff - the amount of memory used as buffers


• cache - the amount of memory used as cache.


• inact - the amount of inactive memory. (-a option)


• active - the amount of active memory. (-a option)

Swap

• si - memory pages in


• so - memory pages out

If you have a lot of activity in so it may indicate that you need more physical ram. the system is constantly paging memory out to disk. you need to take these stats over a period of time to get a feel for how your system performs.

If the values are constnaly greater than 0 then you have a memory issue. use ps to find out the most memory intensive processes.

IO

• bi - Blocks received from a block device (blocks/s).


• bo - Blocks sent to a block device (blocks/s).

System

• in - The number of interrupts per second, including the clock.


• cs - The number of context switches per second.

CPU

• us - Time spent running non-kernel code. (user time, including nice time)


• sy - Time spent running kernel code. (system time)


• id - Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.


• wa - Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.


• st - Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown.

If us is high there is a possibility that some of the user initiated processes are consuming high CPU

If wa is high then there is an issue with the disk storage subsystem and you can use iostat to find sources of I/O contention.

Oracle Slow Session Check

The following queries will allow you to find the sessions currently logged into the database that have accumulated the most time on CPU or for certain wait events. Use them to identify potential sessions to trace using 10046. These queries are filtering the sessions based on logon times less than 4 hours and the last call occurring within 30 minutes. This is to find more currently relevant sessions instead of long running ones that accumulate a lot of time but aren’t having a performance problem. You may need to adjust these values to suit your environment.

Find Sessions with the Highest CPU Consumption

-- sessions with highest CPU consumption
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;

Find Sessions with Highest Waits for a Certain Type

-- sessions with the highest time for a certain wait
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name' 
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;

10g or higher: Find Sessions with the Highest DB Time

-- sessions with highest DB Time usage
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time (sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND  sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;