By Franck Pachot

.
Every database analysis should start with system load analysis. If the host is in CPU starvation, then looking at other statistics can be pointless. With ‘top’ on Linux, or equivalent such as process explorer on Windows, you see the process (and threads). If the name of the process is meaningful, you already have a clue about the active sessions. Postgres goes further by showing the operation (which SQL command), the state (running or waiting), and the identification of the client.

Postgres

By default ‘top’ displays the program name (like ‘comm’ in /proc or in ‘ps’ format), which will be ‘postgres’ for all PostgreSQL processes. But you can also display the command line with ‘c’ in interactive mode, or directly starting with ‘top -c’, which is the same as the /proc/$pid/cmdline or ‘cmd’ or ‘args’ in ‘ps’ format.


top -c
 
Tasks: 263 total,  13 running, 250 sleeping,   0 stopped,   0 zombie
%Cpu(s): 24.4 us,  5.0 sy,  0.0 ni, 68.5 id,  0.9 wa,  0.0 hi,  1.2 si,  0.0 st
KiB Mem :  4044424 total,   558000 free,  2731380 used,   755044 buff/cache
KiB Swap:   421884 total,   418904 free,     2980 used.  2107088 avail Mem
 
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
20347 postgres  20   0  394760  11660   8696 S   7.6  0.3   0:00.49 postgres: demo demo 192.168.56.125(37664) DELETE
20365 postgres  20   0  393816  11448   8736 S   6.9  0.3   0:00.37 postgres: demo demo 192.168.56.125(37669) idle
20346 postgres  20   0  393800  11440   8736 S   6.6  0.3   0:00.37 postgres: demo demo 192.168.56.125(37663) UPDATE
20356 postgres  20   0  396056  12480   8736 S   6.6  0.3   0:00.42 postgres: demo demo 192.168.56.125(37667) INSERT
20357 postgres  20   0  393768  11396   8736 S   6.6  0.3   0:00.40 postgres: demo demo 192.168.56.125(37668) DELETE waiting
20366 postgres  20   0  394728  11652   8736 S   6.6  0.3   0:00.35 postgres: demo demo 192.168.56.125(37670) UPDATE
20387 postgres  20   0  394088  11420   8720 S   6.6  0.3   0:00.41 postgres: demo demo 192.168.56.125(37676) UPDATE
20336 postgres  20   0  395032  12436   8736 S   6.3  0.3   0:00.37 postgres: demo demo 192.168.56.125(37661) UPDATE
20320 postgres  20   0  395032  12468   8736 R   5.9  0.3   0:00.33 postgres: demo demo 192.168.56.125(37658) DROP TABLE
20348 postgres  20   0  395016  12360   8736 R   5.9  0.3   0:00.33 postgres: demo demo 192.168.56.125(37665) VACUUM
20371 postgres  20   0  396008  12708   8736 R   5.9  0.3   0:00.40 postgres: demo demo 192.168.56.125(37673) INSERT
20321 postgres  20   0  396040  12516   8736 D   5.6  0.3   0:00.31 postgres: demo demo 192.168.56.125(37659) INSERT
20333 postgres  20   0  395016  11920   8700 R   5.6  0.3   0:00.36 postgres: demo demo 192.168.56.125(37660) UPDATE
20368 postgres  20   0  393768  11396   8736 R   5.6  0.3   0:00.43 postgres: demo demo 192.168.56.125(37671) UPDATE
20372 postgres  20   0  393768  11396   8736 R   5.6  0.3   0:00.36 postgres: demo demo 192.168.56.125(37674) INSERT
20340 postgres  20   0  394728  11700   8736 S   5.3  0.3   0:00.40 postgres: demo demo 192.168.56.125(37662) idle
20355 postgres  20   0  394120  11628   8672 S   5.3  0.3   0:00.32 postgres: demo demo 192.168.56.125(37666) DELETE waiting
20389 postgres  20   0  395016  12196   8724 R   5.3  0.3   0:00.37 postgres: demo demo 192.168.56.125(37677) UPDATE
20370 postgres  20   0  393768  11392   8736 S   4.6  0.3   0:00.34 postgres: demo demo 192.168.56.125(37672) DELETE
20376 postgres  20   0  393816  11436   8736 S   4.6  0.3   0:00.37 postgres: demo demo 192.168.56.125(37675) DELETE waiting
20243 postgres  20   0  392364   5124   3696 S   1.0  0.1   0:00.06 postgres: wal writer process

This is very useful information. Postgres changes the process title when it executes a statement. In this example:

  • ‘postgres:’ is the name of the process
  • ‘demo demo’ are the database name and the user name
  • ‘192.168.56.125(37664)’ are the IP address and port of the client.
  • DELETE, UPDATE… are the commands. They are more or less the command name used in the feed back after the command completion
  • ‘idle’ is for sessions not currently running a statement
  • ‘waiting’ is added when the session is waiting on a blocker session (enqueued on a lock for example)
  • ‘wal writer process’ is a background process

This is very useful information, especially because we have, on the same sampling, the Postgres session state (idle, waiting or running an operation) with the Linux process state (S when sleeping, R when runnable or running, D when in I/O,… ).

Oracle

With Oracle, you can have ASH to sample session state, but being able to see it at OS level would be great. It would also be a safeguard if we need to kill a process.

But, the Oracle processes do not change while running. They are set at connection time.

The background processes mention the Oracle process name and the Instance name:


[oracle@VM122 ~]$ ps -u oracle -o pid,comm,cmd,args | head
 
  PID COMMAND         CMD                         COMMAND
 1873 ora_pmon_cdb2   ora_pmon_CDB2               ora_pmon_CDB2
 1875 ora_clmn_cdb2   ora_clmn_CDB2               ora_clmn_CDB2
 1877 ora_psp0_cdb2   ora_psp0_CDB2               ora_psp0_CDB2
 1880 ora_vktm_cdb2   ora_vktm_CDB2               ora_vktm_CDB2
 1884 ora_gen0_cdb2   ora_gen0_CDB2               ora_gen0_CDB2

The foreground processes mention the instance and the connection type, LOCAL=YES for bequeath, LOCAL=NO for remote via listener. 


[oracle@VM122 ~]$ ps -u oracle -o pid,comm,cmd,args | grep -E "[ ]oracle_|[ ]PID"
 
  PID COMMAND         CMD                         COMMAND
21429 oracle_21429_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21431 oracle_21431_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21451 oracle_21451_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21517 oracle_21517_cd oracleCDB1 (LOCAL=NO)       oracleCDB1 (LOCAL=NO)

You need to join V$PROCESS with V$SESSION on (V$PROCESS.ADDR=V$SESSION.PADDR) to find the state, operation and client information

For the fun, you can change the program name (ARGV0) and arguments (ARGS).

The local connections can change the name in the BEQueath connection string:


sqlplus -s system/oracle@"(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=$ORACLE_HOME/bin/oracle)(ARGV0=postgres)(ARGS='(DESCRIPTION=(LOCAL=MAYBE)(ADDRESS=(PROTOCOL=BEQ)))')(ENVS='OLE_HOME=$ORACLE_HOME,ORACLE_SID=CDB1'))" <<< "host ps -u oracle -o pid,comm,cmd,args | grep -E '[ ]oracle_|[ ]PID'"
 
  PID COMMAND         CMD                         COMMAND
21155 oracle_21155_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21176 oracle_21176_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21429 oracle_21429_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21431 oracle_21431_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21451 oracle_21451_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21517 oracle_21517_cd oracleCDB1 (LOCAL=NO)       oracleCDB1 (LOCAL=NO)
22593 oracle_22593_cd postgres (DESCRIPTION=(LOCA postgres (DESCRIPTION=(LOCAL=MAYBE)(ADDRESS=(PROTOCOL=BEQ)))

The remote connection can have the name changed from the static registration, adding an ARVG0 value on the listener side:


LISTENER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
SID_LIST_LISTENER=(SID_LIST=
 (SID_DESC=(GLOBAL_DBNAME=MYAPP)(ARGV0=myapp)(SID_NAME=CDB1)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1))
 (SID_DESC=(GLOBAL_DBNAME=CDB1_DGMGRL)(SID_NAME=CDB1)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1))
 (SID_DESC=(GLOBAL_DBNAME=CDB2_DGMGRL)(SID_NAME=CDB2)(ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1))
 )

When reloading the listener with this (ARGV0=myapp) to identify connection from this MYAPP service

[oracle@VM122 ~]$ sqlplus -s system/oracle@//localhost/MYAPP <<< "host ps -u oracle -o pid,comm,cmd,args | grep -E '[ ]oracle_|[ ]PID'"
  PID COMMAND         CMD                         COMMAND
21155 oracle_21155_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21176 oracle_21176_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21429 oracle_21429_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21431 oracle_21431_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21451 oracle_21451_cd oracleCDB2 (LOCAL=NO)       oracleCDB2 (LOCAL=NO)
21517 oracle_21517_cd oracleCDB1 (LOCAL=NO)       oracleCDB1 (LOCAL=NO)
24261 oracle_24261_cd myapp (LOCAL=NO)            myapp (LOCAL=NO)

However, I would not recommend to change the default. This can be very confusing for people expecting ora_xxxx_SID and oracleSID process names.

Post Views: 2,589
Thumbnail [60x60]
by
Oracle Team