Oracle session, server process and virtual circuit in shared server mode (MTS)

Oracle has 3 types of service handlers which act as connection points to an Oracle database :
Dedicated server process
Shared server process (MTS)
Database Resident Connection Pooling (DRCP).

1. Dedicated server mode
It’s the most used one. One session has a dedicated process. It’s 1 vs 1 relationship. To trace a session or kill a session, you can just kill either the session or the process. It didn’t impact other sessions or processes.

SQL to get the sid and spid:

select s.sid, p.spid
from v$session s, v$process p
where s.paddr = p.addr
;

2. Shared server mode (MTS).
In this mode, one server process will work for multiple sessions. Between them, there is an agent, called dispatcher, which will take care of the clients. It will put clients requests/replies to a queue of virtual circuit (A piece of shared memory). An idle shared server picks up the virtual circuit from the queue, and services the request. Let’s see a picture from oracle document:

In this mode, a session didn’t have direct connection to a server process. It could be one server for the session requests this time, and another in next request. Strictly, it is not a 1 vs many relationship. The session and process map is dynamic. It makes it different to kill (from OS level) or trace a session in shared server mode.

If we run the same SQL previously, it will return different results depends on the whether the session is active.
If the session is active, it will return the server process id.

SQL>  select a.spid, b.sid, b.PADDR from v$process a,v$session b where a.ADDR = b.PADDR and b.sid     =6;

SPID                            SID PADDR
------------------------ ---------- ----------------
28676                             6 000000049368E638

$ ps -ef|grep 28676
  oracle 27430 22706   0 22:48:40 pts/12      0:00 grep 28676
  oracle 28676  1529   0 20:32:21 ?           0:31 ora_s010_WENJIN

If the session is idle, it will return its dispatcher process id.

SQL> select a.spid, b.sid, b.PADDR from v$process a,v$session b where a.ADDR = b.PADDR and b.sid =6;

SPID                            SID PADDR
------------------------ ---------- ----------------
7397                              6 000000049367A9C0

$ ps -ef|grep 7397
  oracle   230 22706   0 22:55:30 pts/12      0:00 grep 7397
  oracle  7397  1529   0 02:01:29 ?           1:39 ora_d001_WENJIN

When the session is active, killing the SPID from OS level will terminal the server process, and it will terminal the session as well.
If the process is not working for the session anymore when you kill it (this is very likely for the short-request session), it will not impact the session itself. It will just connect to another server process. The circuit and dispatcher didn’t changed. And this action may impact other sessions that the same process that is working on.

When the session is idle, killing the SPID from OS level will terminal the dispatch process, and it will terminal all sessions that uses that dispatcher.

To avoid get the dispatch SPID, but the process SPID only, using this SQL:

--it will only have data when the session is active, and a process is working for it
select s.sid, p.spid
from v$session s, v$circuit c, v$process p
where c.saddr=s.saddr and c.server=p.addr
and s.sid = &sid.
;

To get the dispatcher name, using this SQL:

select s.sid, d.name
from v$session s, v$circuit c,v$dispatcher d
where c.saddr=s.saddr and c.dispatcher=d.paddr
and s.sid =&sid.
;

We can kill the dispatch D001 using SQL, or using kill at OS level:

SQL> alter system shutdown immediate 'D001';

--in alert log, you will see this
idle dispatcher 'D001' terminated, pid = (21, 3)

--if we kill it from OS
Shell> kill -9

--in alert log, you will see this
found dead shared server 'S010', pid = (40, 1)

To get the list of sessions that will be impacted by killing a dispatch:

select s.sid, d.name
from v$session s, v$circuit c,v$dispatcher d
where c.saddr=s.saddr and c.dispatcher=d.paddr
and d.name='D001'
;
       SID NAME
---------- ----
       772 D001
       773 D001

2 rows selected.

For trace a session in shared mode, we have to be very careful because tracing a session will trace its process. When that process works for another session, that session will be in trace mode as well. In a busy system, the trace event will be spread very quickly. It will result most sessions are in trace mode, and generate lots of trace files. To disable the trace, we have to disable all trace on all sessions and server processes level.

3. Database Resident Connection Pooling (DRCP).
Currently DRCP interface is only available for OCI and OCCI clients. It didn’t support JDBC driver. This limited its usage a lot.