How to see current utilization of processes/sessions and max utilization?

Using the following SQL one can find the current number of processes and sessions connected and also max utilization so one can check if you need to increase the values of the parameter

SQL> select resource_name, current_utilization, max_utilization from v$resource_limit where resource_name in (‘processes’,’sessions’);

Output:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION
—————————— ——————- —————
processes 146 196
sessions 157 210

How to trace a running process?

Using strace one can trace the system calls being executed by a running process. To stop the strace press control-C

To display the system calls being executed
$ strace -p <pid>

or

To display a summary of system calls being executed
$ strace -cfo <logfile> -p <pid>

Example: In this example smon process was being traced
$ strace -p 1234
Process 1234 attached – interrupt to quit
getrusage(RUSAGE_SELF, (ru….
getrusage(RUSAGE_SELF, (ru….
Pressed <control-C>

$ strace -cfo smon_strace.log -p 1234
Process 1234 attached – interrupt to quit
Process 1234 detached
Pressed <control-C> to detach.

$ cat smon_strace.log
% time seconds usecs/call calls errors syscall
—— ———– ———– ——— ——— —————-
nan 0.000000 0 22 getrusage
—— ———– ———– ——— ——— —————-
100.00 0.000000 22 total

How to get current session id, process id, client process id?

SQL> select b.sid, b.serial#, a.spid processid, b.process clientpid from v$process a, v$session b
where a.addr = b.paddr
and b.audsid = userenv(‘sessionid’);

SID SERIAL# PROCESSID CLIENTPID
———- ———- ——— ———
43 52612 420734 5852:5460

V$SESSION.SID and V$SESSION.SERIAL# are database process id
V$PROCESS.SPID – Shadow process id on the database server
V$SESSION.PROCESS – Client process id, on windows it is “:” separated the first # is the process id on the client and 2nd one is the thread id.

How to find the process id listening on a port?

On Windows – Example trying to find which process is listening on port 1433

Show if any process is listening on port 1433 for example
c:\temp> netstat -ano | find /I “1433” | find /I “LISTEN”
Proto Local Address Foreign Address State PID
TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING 6244

Using tlist or task manager one can find the PID (last column) from the above command to find the process
c:\temp> tlist | find /I “6244”
6244 sqlservr.exe

On Linux
Using netsat find if any process is listening to port 22
$ netstat -an | grep 22 | grep LISTEN
tcp 0 0 :::22 :::* LISTEN

Using fuser one can find the process id listening on the port, the process id is 3788, one has to be root to see the result
[root@localhost ~]# fuser -v 22/tcp
here: 22

USER PID ACCESS COMMAND
22/tcp root 3788 f…. sshd

Using lsof using the port#, below shows SSH is listening to port and process id is 3788. This command needs to be run as root.
[root@localhost ~]# /usr/sbin/lsof -i :22
COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME
sshd 3788 root 3u IPv6 8374 TCP *:ssh (LISTEN)

On AIX
https://www-304.ibm.com/support/docview.wss?uid=swg21264632

process control

# lists jobs in current session
$ jobs

# start job 1 (sleeps for 200 seconds)
$ sleep 200

# suspended job 1 using control-z
[1] + Stopped sleep 200

# start job 2 (sleeps for 400 seconds)
$ sleep 400

# suspended job 2 using control-z
[2] + Stopped sleep 400

# lists jobs in current session shows the jobs are suspended i.e. not running
$ jobs
[2] + Stopped sleep 400
[1] – Stopped sleep 200

# place sleep 400 in background
$ bg %2
[2] sleep 400

# shows job status sleep 400 is running and 1 is stopped/suspended
$ jobs
[1] + Stopped sleep 200
[2] – Running sleep 400

# place sleep 200 in background
$ bg %1
[1] sleep 200

# shows job status both are running
$ jobs
[1] + Running sleep 200
[2] – Running sleep 400

# place sleep 200 job in foreground
$ fg %1
sleep 200

# pressed control-z to suspend sleep 200
[1] + Stopped sleep 200

# show job status sleep 200 is suspended and sleep 400 is running
$ jobs
[1] + Stopped sleep 200
[2] – Running sleep 400

# wait for suspended job wait will return immediately as it is not running
$ wait %1

# show job status sleep 200 is suspended and sleep 400 is running
$ jobs
[1] + Stopped sleep 200
[2] – Running sleep 400

# place job 1 (sleep 200) in background so it will run
$ bg %1
[1] sleep 200

# wait tells to wait for sleep 200 to finish
$ wait %1

[2] + Done sleep 400

ksh – Process

— Get process id of current process
$ echo $$
6113
— shows process id
$ ps
PID TTY TIME CMD
2050 pts/2 00:00:00 ps
6113 pts/2 00:00:00 ksh

Useful when creating a unique temp file example TEMPFILE=/tmp/tmpfile.$$

— Get process id of last background id
$ sleep 10 &
[1] 2078
$ echo $!
2078

— Print parent process id
$ print $PPID
6048

How to get client process id and oracle shadow process id in dedicated server?

— note this will work when not logged in as SYS, as the session id is the same for all background processes
SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.audsid = userenv(‘sessionid’);

Or for SYS you can use the following
SQL> SELECT p.spid oracle_dedicated_process, s.process clientpid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = (select sid from v$mystat where rownum = 1);

ORACLE_DEDIC CLIENTPID
———— ————
2301958 839726

1 row selected.

SQL> !ps -ef | grep 2301958
oracle 2301958 839726 0 15:18:04 – 0:00 oracleTEST (DESCRIPTION=(LOCAL=YES(ADDRESS=(PROTOCOL=beq)))
oracle 2441306 839726 0 15:18:13 pts/4 0:00 grep 2301958

SQL> !ps -ef | grep 839726
oracle 839726 2928804 0 15:18:02 pts/4 0:00 sqlplus
oracle 2441308 839726 0 15:18:19 pts/4 0:00 grep 839726