Identifying CPU and Memory Intensive processes on a database server using PS

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 –

  1. pcpu (%CPU) – % of cpu utilization
  2. pmem (%MEM) – % of memory utilization
  3. pid (PID) – Unix process ID
  4. user (USER) – Unix user name
  5. tty (TTY) – The name of the device the process is currently running in
  6. 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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload the CAPTCHA.