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:

Shared server mode

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.

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

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

  1. Krishna says:

    Database Resident Connection Pooling is available through many Opensource drivers like PHP, Perl and Python too. With Oracle Database 12c, it is exposed through JDBC as well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: