ps (process statistics) is an utility that reports a snapshot of the current processes. By default, ps selects all processes with the same effective user ID (euid=EUID) as the current user and associated with the same terminal as the invoker. It displays the process ID (pid=PID), the terminal associated with the process (tname=TTY), the cumulated CPU time in [dd-]hh:mm:ss format (time=TIME), and the executable name (ucmd=CMD). Below is sample default output, which is unsorted.
$ ps PID TTY TIME CMD 10055 pts/1 00:00:00 bash 10116 pts/1 00:00:00 ps
- Identify CPU Intensive processes
$ ps -e -o pcpu,pmem,pid,user,tty,args --no-header| sort -n -k 1 -r | head 6.6 8.5 10363 oracle ? oraclepdb10 (LOCAL=NO) 0.3 9.2 9544 oracle ? ora_j000_pdb10 0.1 18.0 4827 oracle ? ora_mmon_pdb10 0.1 10.4 4825 oracle ? ora_cjq0_pdb10 0.1 0.3 10330 rperumal pts/2 -bash 0.0 8.9 4839 oracle ? ora_q000_pdb10 0.0 7.9 4817 oracle ? ora_lgwr_pdb10 0.0 5.4 4819 oracle ? ora_ckpt_pdb10 0.0 5.0 4823 oracle ? ora_reco_pdb10 0.0 4.4 4829 oracle ? ora_mmnl_pdb10
- Identify Memory Intensive processes
$ ps -e -o pcpu,pmem,pid,user,tty,args --no-header| sort -n -k 2 -r | head 0.0 25.2 4821 oracle ? ora_smon_pdb10 0.0 18.2 4815 oracle ? ora_dbw0_pdb10 0.1 18.0 4827 oracle ? ora_mmon_pdb10 0.1 10.4 4825 oracle ? ora_cjq0_pdb10 0.0 10.3 4813 oracle ? ora_mman_pdb10 0.3 9.2 9544 oracle ? ora_j000_pdb10 0.0 8.9 4839 oracle ? ora_q000_pdb10 8.5 8.5 10363 oracle ? oraclepdb10 (LOCAL=NO) 0.0 7.9 4817 oracle ? ora_lgwr_pdb10 0.0 5.4 4819 oracle ? ora_ckpt_pdb10
Column descriptions for process statistics (ps) output above, from left to right –
- pcpu (%CPU) – % of cpu utilization
- pmem (%MEM) – % of memory utilization
- pid (PID) – Unix process ID
- user (USER) – Unix user name
- tty (TTY) – The name of the device the process is currently running in
- args (COMMAND) – simple name of executable or command
Notes on pipe (|)
- sort -n -k 1 -r, sorts the output in descening order based on the value of %CPU
- sort -n -k 2 -r, sorts the output in descening order based on the value of %MEM
- head displays the first 10 rows of the sorted output, which is default
You can obtain Oracle session information for a given pid using the below query.
SELECT vs.username, vs.osuser, vs.program, vp.spid, vs.sid, vs.serial#, vs.machine, vs.terminal, vq.sql_text FROM v$session vs, v$process vp, v$sql vq WHERE vs.paddr = vp.addr AND vp.spid = '&ospid' AND vs.sql_address = vq.address(+) AND vs.sql_hash_value = vq.hash_value(+);
If you run the query with the os process id of 10363, you get the output similar to below;
USERNAME OSUSER PROGRAM SPID SID SERIAL# MACHINE TERMINAL SQL_TEXT ---------- ---------- ---------- ------------ ---------- ----------- ---------- ---------- ------------------------------ PERUMAL rperumal sqlplus@db 10363 159 979 dblx131.la pts/1 select count(*) from t lx131.lab. b.perumal. perumal.or org g (TNS V1- V3)
Column descriptions for SQL output –
USERNAME - Name of the Oracle user or schema OSUSER - Name of the OS user connected to Oracle Database PROGRAM - Program or Tool used to connect to Oracle Database SPID - Unix Process ID of the Oracle Session SID - Oracle Session identifier SERIAL# - Session serial number. Used to uniquely identify a session's objects. MACHINE - Operating system machine name TERMINAL - Operating system terminal name SQL_TEXT - First thousand characters of the SQL text for the current cursor
You can learn more about the available options by issuing man ps in the command prompt.