Oracle connections, processes, sessions, connection pooling.

===================================
About Oracle connections, processes, sessions.
===================================

This is an extract from:
Tom Kyte, What's the difference between connections, sessions and processes? 
JDBC Developer’s Guide and Reference 10.2
Expert Oracle JDBC Programming


A connection
A connection is a network, physical connection between you and Oracle database.  
A connection might be of two types:
DEDICATED server and SHARED server.  

A session
A session encapsulates user interaction with the database, from the moment user was authenticated until the moment the user disconnects.
In most cases, one connection corresponds to one session, but it does not have to be so.
Zero, one or more sessions may be established over a given connection to the database.
A process will be used by a session to execute statements.

A process
A process is a physical process or thread.

On Linux, one can see a process with "ps".  
There are two types of processes in Oracle:
Background processes like SMON, PMON, RECO, ARCH, CKPT, EMNn, DBWR, etc...
User processes like dedicated servers or shared server (multi-threaded server, MTS )

Relationship between CONNECTION->SESSION->PROCESS

You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection.

Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS. This is the regular situation in dedicated server connection.  
Sometimes there is a one to many from connection to 
sessions (eg: like autotrace, one connection, two sessions, one process).  

A process does not have to be dedicated to a specific connection or session.
When using shared server (MTS), your SESSION will grab a process from a pool of processes in order to execute a statement.  When the call is over, that process is released back to the pool of processes.

In dedicated server mode.
Each physical connection has one session, and one separate (or dedicated) process (or thread) associated with it.
In this configuration, each entry in V$SESSION would have a unique entry in V$PROCESS, connected by V$SESSION.paddr = V$PROCESS.addr.
V$PROCESS.spid is the actual server pid on the host machine.

Dedicated server is the most commonly used option.
(This is the option SH is using) 

In shared server (multi-threaded server) mode.
A pool of processes is shared by physical connections.
A user has a session.  
A session is your login, it means you are connected and authenticated, and you can do SQL. You have a row in v$session.
When you are not doing SQL, you are inactive and you will NOT be associated with any process.  
When a shared server session becomes active, it is associated with some process.
It only uses a process for the duration of the request/call to the database.

The session does not have a process. A process does not have sessions.  

A process is a resource used by sessions over time. 

===================
Example
===================

[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920 | grep -v grep
[tkyte@tkyte-pc-isdn tkyte]$ sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Sat Sep 28 10:36:03 2002

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

idle> !ps -auxww | grep oracleora920 | grep -v grep
tkyte    19971  0.0  0.1  2196  916 pts/1    S    10:36   0:00 /bin/bash -c ps -auxww | 
grep oracleora920 | grep -v grep

no process, no nothing

idle> connect /
Connected.
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920   19974  1.5  2.2 230976 11752 ?      S    10:36   0:00 oracleora920 
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

got my process now...

idle> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
idle> !ps -auxww | grep oracleora920 | grep -v grep
ora920   19974  0.6  2.3 230976 11876 ?      S    10:36   0:00 oracleora920 
(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

idle> select 1 from dual;
SP2-0640: Not connected

Still have connection, process, but no session. 
The message is a little "misleading". Technically there is a connection, but no session


further, autotrace in sqlplus can be used to show that you can have 
a) a connection
b) that uses a single process
c) to service two sessions:


SELECT username FROM V$PROCESS WHERE username is not null;

USERNAME
--------------
OPS$TKYTE

one session

SELECT username, program FROM V$PROCESS;

USERNAME        PROGRAM
--------------- ------------------------------------------------
                PSEUDO
ora920          oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte           oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

To get number of non backgrouond processes:
SELECT count(*) FROM V$PROCESS WHERE background IS NOT NULL;

Background processes and one dedicated server process.

set autotrace on statistics;

Autotrace for statistics uses ANOTHER session so it can query up the stats for your 
CURRENT session without impacting the STATS for that session!


SELECT username FROM V$PROCESS WHERE username IS NOT NULL;

USERNAME
--------------
OPS$TKYTE
OPS$TKYTE

Two sessions but....

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SELECT username, program FROM V$PROCESS;

USERNAME        PROGRAM
--------------- ------------------------------------------------
                PSEUDO
ora920          oracle@tkyte-pc-isdn.us.oracle.com (PMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (DBW0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (LGWR)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CKPT)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (SMON)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (RECO)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (CJQ0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (QMN0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (S000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (D000)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC0)
ora920          oracle@tkyte-pc-isdn.us.oracle.com (ARC1)
tkyte           oracle@tkyte-pc-isdn.us.oracle.com (TNS V1-V3)

14 rows selected.

same 14 processes...

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       1095  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

ops$tkyte@ORA920.US.ORACLE.COM>

A connection pooling
Connection pooling is maintained by Application server.
Creating new connection involves creating a new process or a thread in OS, and is both time consuming and exhausting for the host OS .
Rather connection pooling, is a better option.
The application server takes a connection from a pool, marks it as "used", and hands it over to the application.
The application is actually receiving a reference to a Logical Connection, rather than to a Physical Connection, so when application does close() to the connection, it just clear the connection state, and return it to the pool of Physical connections.

See also
ORA-00020: No more process state objects available and ORA-00020: maximum number of processes 0 exceeded

Oracle Shared Server (MTS) vs Dedicated mode

============================
General
============================
The Server can be configured to run at Dedicated or Shared mode,
The Client can be also configured, to run at Dedicated or Shared mode.
So you can have a server configured to run in Shared mode running sessions configured to run in a dedicated mode.

============================

How to tell if a server is running in Dedicated or Shared/MTS mode?
============================
Need to check:
 - server configuration.
 - client configuration
 - the actual sessions status.

=========================
Check server configuration
=========================
check parameters:
- dispatcheres
- shared_servers

dispatchers configures dispatcher processes in the shared server architecture.
max_dispatchers is optional, and must be larger than the number of dispatchers.
shared_servers 0, meaning that shared server is not on. 
                                     1 meaning that shared server is on, or that there are dispatchers.

SELECT name, value 

  FROM V$PARAMETER 

 WHERE name LIKE '%dispatcher%';

NAME              VALUE

---------------- -------------------------------

dispatchers      (PROTOCOL=TCP) (SERVICE=igtXDB)

max_dispatchers

SELECT COUNT(*) FROM V$DISPATCHER;

  COUNT(*)

----------

         1

SELECT name, value FROM V$PARAMETER 
WHERE name LIKE '%shared_servers%'

NAME             VALUE
---------------- ----------
shared_servers   1

=========================
Check client configuration

=========================
In tnsnames.ora
conn_str =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = my_host)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orainst)
    )
  )

=========================
Check the actual sessions status
=========================
See in what configuration sessions are running.

SQL> SELECT DISTINCT (server) FROM V$SESSION ;

SERVER
---------
DEDICATED


=========================
Short Oracle Reference

=========================
Per Oracle documentation: Oracle® Database Net Services Reference Guide 10G

What is parameter SERVER?
Use the parameter SERVER to instruct the listener to connect the client to a specific type of service handler.
service handler handles client requests: either by DEDICATED server or by SHARED server.

What is a service handler?
Service handler is a process that acts a connection point from the listener to the database server. 
A service handler can be a dispatcher or dedicated server.

What is a dispatcher?
A process that enables many clients to connect to the same server without the need for a dedicated server process for each client. 
A dispatcher handles and directs multiple incoming network session requests to shared server processes. 

What is a shared server

A database server that is configured to allow many user processes to share very few server processes, so the number of users that can be supported is increased. 
With shared server configuration, many user processes connect to a dispatcher. 
The dispatcher directs multiple incoming network session requests to a common queue. 
An idle shared server process from a shared pool of server processes picks up a request from the queue. 
This means that a small pool of server processes can serve a large number of clients. 
Contrast with dedicated server.

What is a dedicated server?

A server process that is dedicated to one client connection. 
Contrast with shared server.