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

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

===============================
Error ORA-00020 in alert log
===============================
Mon Nov 09 23:22:03 2015
Archived Log entry 83763 added for thread 1 sequence 83774 ID 0x36162634 dest 1:
Mon Nov 09 23:42:04 2015
Thread 1 advanced to log sequence 83776 (LGWR switch)
  Current log# 1 seq# 83776 mem# 0: /oracle_db/db1/db_igt/ora_redo_01_a.rdo
Mon Nov 09 23:42:05 2015
Archived Log entry 83764 added for thread 1 sequence 83775 ID 0x36162634 dest 1:
Mon Nov 09 23:50:04 2015
ORA-00020: No more process state objects available
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.
Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:

Process J002 submission failed with error = 20
kkjcre1p: unable to spawn jobq slave process
Errors in file /software/oracle/diag/rdbms/igt/igt/trace/igt_cjq0_21012.trc:

Mon Nov 09 23:51:13 2015

ORA-00020: maximum number of processes 0 exceeded

ORA-20 errors will not be written to the alert log for

 the next minute. Please look at trace files to see all

 the ORA-20 errors.

Mon Nov 09 23:51:56 2015

Shutting down instance (abort)

License high water mark = 270

USER (ospid: 5241): terminating the instance

Mon Nov 09 23:51:57 2015

ORA-1092 : opiodr aborting process unknown ospid (30859_47851384046032)

Mon Nov 09 23:51:57 2015

ORA-1092 : opitsk aborting process

Mon Nov 09 23:51:57 2015

ORA-1092 : opiodr aborting process unknown ospid (10833_47625099882960)

Instance terminated by USER, pid = 5241

Mon Nov 09 23:51:59 2015

Instance shutdown complete

===============================
Error ORA-00020 in trace file
===============================

 /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc:

Trace file /software/oracle/diag/rdbms/orainst/orainst/trace/orainst_cjq0_21012.trc

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /software/oracle/111

System name:    Linux

Node name:      tha-ais-2-aps-5

Release:        2.6.18-308.24.1.el5

Version:        #1 SMP Wed Nov 21 11:42:14 EST 2012

Machine:        x86_64

Instance name: igt

Redo thread mounted by this instance: 1

Oracle process number: 274

Unix process pid: 21012, image: oracle@tha-ais-2-aps-5 (CJQ0)

*** 2015-11-09 23:50:04.616

*** SESSION ID:(83.28755) 2015-11-09 23:50:04.616

*** CLIENT ID:() 2015-11-09 23:50:04.616

*** SERVICE NAME:(SYS$BACKGROUND) 2015-11-09 23:50:04.616

*** MODULE NAME:() 2015-11-09 23:50:04.616

*** ACTION NAME:() 2015-11-09 23:50:04.616

ORA-00020: No more process state objects available

ORA-00020: No more process state objects available

ORA-00020: No more process state objects available

*** 2015-11-09 23:50:09.627

ORA-00020: No more process state objects available

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

Check in Database
===============================

COL RESOURCE_NAME FOR A20

COL LIMIT_VALUE FOR A20

SELECT resource_name, current_utilization, limit_value

  FROM V$RESOURCE_LIMIT

 WHERE resource_name in ('sessions','processes','transactions');

RESOURCE_NAME        CURRENT_UTILIZATION LIMIT_VALUE

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

processes                           1041       2000

sessions                            1015       3072

transactions                           7  UNLIMITED

SELECT *
  FROM V$RESOURCE_LIMIT 

 WHERE resource_name in ('sessions','processes','transactions');

RESOURCE_NAME        CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE

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

processes                            288             296        300                   300

sessions                             296             319        335                   335

transactions                  4294965549      4294967295        368             UNLIMITED

SET LINESIZE 120

SET PAGESIZE 1000

COL MACHINE FOR A30

COL USERNAME FOR A30

COL PROGRAM FOR A30

SELECT MACHINE, USERNAME, PROGRAM, COUNT(*) 

  FROM V$SESSION 

GROUP BY MACHINE, USERNAME, PROGRAM 

HAVING count(*) > 5 

ORDER BY COUNT(*) DESC;

MACHINE           USERNAME            PROGRAM                                   COUNT(*)
----------------- ------------------- ---------------------------------------- ---------
esp-vod-9-aps-1   IRL_VODAF_SPARX     JDBC Thin Client                                37
esp-vod-9-aps-1   ZAF_VODAC_SPARX     JDBC Thin Client                                32
esp-vod-9-aps-1   NZL_VODAF_SPARX     JDBC Thin Client                                31
esp-vod-9-aps-1   NZL_VODAF_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   IRL_VODAF_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   ZAF_VODAC_SFIQQ     JDBC Thin Client                                18
esp-vod-9-aps-1   ROM_VODAF_SPARX     JDBC Thin Client                                16
esp-vod-9-aps-1   CZE_VODAF_SBQQQ     proxy@esp-vod-9-aps-1 (TNS V1-V3)               12
jdbcclient        MLT_VODAF_MOCOQ     JDBC Thin Client                                11
esp-vod-9-aps-1   IRL_VODAF_SPARX     notification@esp-vod-9-aps-1 (TNS V1-V3)         6
esp-vod-9-aps-1   ZAF_VODAC_SPARX     notification@esp-vod-9-aps-1 (TNS V1-V3)         6

===============================
Solution
===============================

The computed number for PROCESSES is derived form SESSIONS number.
In 10g, use following formula:

(1.1 * PROCESSES) + 5

In 11g R1 onward it changed to


(1.5 * PROCESSES) + 22

When increasing PROCESSES parameter, one should also increase SESSIONS and TRANSACTIONS parameters.

For Oracle 10g: the default formula for determining  these parameter values is as follows:

    

processes=x

sessions=x*1.1+5

transactions=sessions*1.1

for example:

processes sessions transactions
      300      335          370
      500      555          610    
      600      665          730
      900      995         1095
     1000     1105         1215

     1500     2280

     2250     3420

     3000     4528

These parameters can't be modified in memory. 

You have to modify the spfile only (scope=spfile) and bounce the instance.

ALTER SYSTEM SET PROCESSES=500 SCOPE=SPFILE;

ALTER SYSTEM SET SESSIONS=555 SCOPE=SPFILE;

ALTER SYSTEM SET TRANSACTIONS=610 SCOPE=SPFILE;

SHUTDOWN ABORT;

STARTUP;





===============================
Preliminary session option.
===============================
The -prelim option. 
Sometimes, because the threashold of session is reached, a DBA cannot login to the database in order to perform a SHUTDOWN command. 
In this case, the solution would be to use the -prelim option. 
When opening a session in Preliminary mode, it will not try to create private session structures in the SGA. 
This allows you to connect to perform debugging or shutdown operations. 

For Example:
sqlplus /nolog

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014

 Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 @ SQL> connect /
 ERROR:
 ORA-00020: maximum number of processes (1000) exceeded

exit

sqlplus -prelim "/ as sysdba"

 SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014
 Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 SQL> shutdown abort
 ORACLE instance shut down.
 SQL> exit

 Disconnected from ORACLE


===============================
Checking Current Situation
===============================

SET lines 140 pages 1000
COL RESOURCE_NAME FOR A20

SELECT *
  FROM v$resource_limit
 WHERE resource_name='processes';


RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
------------- ------------------- --------------- ------------------ --------------------
processes                     249             255                300                  300

SET linesize 140

COL run_date FOR A20

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,

       username AS user_name,

       count(*) AS sessions

FROM V$SESSION 

WHERE username IS NOT NULL 

GROUP BY username

ORDER BY count(*) DESC;

RUN_DATE             USER_NAME                        SESSIONS

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

20170308 16:57:10    AUT_TMOQQ_USERA                       149

20170308 16:57:10    AUT_TMOBI_USERB                        57

20170308 16:57:10    AUT_MAXMO_USERC                        11

20170308 16:57:10    AUT_TMOBI_USERD                        10

20170308 16:57:10    SYS                                     1

20170308 16:57:10    SYSTEM                                  1

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

Checking Historic Situation
===============================

SET lines 200 pages 1000

COL begin_interval_time FOR A20

SELECT HIST_SNAPSHOT.snap_id,

       HIST_SNAPSHOT.begin_interval_time,

       HIST_RESOURCE_LIMIT.current_utilization,   

       HIST_RESOURCE_LIMIT.max_utilization,

       HIST_RESOURCE_LIMIT.initial_allocation

 FROM DBA_HIST_RESOURCE_LIMIT HIST_RESOURCE_LIMIT, 

      SYS.DBA_HIST_SNAPSHOT   HIST_SNAPSHOT

WHERE HIST_RESOURCE_LIMIT.resource_name='processes'

  AND HIST_RESOURCE_LIMIT.snap_id=HIST_SNAPSHOT.snap_id

ORDER BY HIST_SNAPSHOT.snap_id DESC;

SELECT parsing_schema_name,COUNT(*) 

FROM DBA_HIST_SQLSTAT 

WHERE snap_id=51158 

GROUP BY parsing_schema_name;

===============================
Monitoring from Database
===============================
When answering "Why did ORA-00020 occur, first option would be to query historical data.
If that data is not available, or not meaningful, it is possible to log sessions count to a table every N minutes.
Following example used Oracle JOB mechanism to call every 5 minutes a procedure.
The Procedure would log data to a table



---------------------------------------
Create Tablespace
---------------------------------------
CREATE TABLESPACE MONITOR_TBS DATAFILE '/oracle_db/db1/db_igt/ora_monitor_table_01.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M EXTENT MANAGEMENT LOCAL;

---------------------------------------
Create Sequence
---------------------------------------
CREATE SEQUENCE MONITOR_SEQ INCREMENT BY 1 START WITH 1 NOCACHE NOCYCLE;

---------------------------------------
Create Table
---------------------------------------
-- Create table
create table MONITOR_SESSIONS
(
  seq_id                    NUMBER,
  run_date                  VARCHAR2(30),
  user_name                 VARCHAR2(30),
  sessions                  NUMBER
)
tablespace MONITOR_TBS;


---------------------------------------
Grants as sysdba
---------------------------------------
GRANT SELECT ON SYS.V_$SESSION TO AUT_TMOQQ_USERA;


---------------------------------------
Code
---------------------------------------
CREATE OR REPLACE PACKAGE BODY ADMIN_MONITOR IS

-----------------------------------------------------------
   PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2) IS
     PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
     INSERT INTO SGA_W_LOG( procedure_name,  data ,ts_last_modified)
       VALUES ( p_module_name, p_msg_text, SYSDATE);
     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       NULL;
   END;
-----------------------------------------------------------
  PROCEDURE monitor_sessions  IS
     v_module_name VARCHAR2(30);
     v_seq_id      NUMBER;
  BEGIN
    
    v_module_name := 'monitor_sessions';
    SELECT MONITOR_SEQ.nextval INTO v_seq_id FROM DUAL;
    
    INSERT INTO MONITOR_SESSIONS (seq_id,run_date, user_name, sessions)
    SELECT v_seq_id, run_date, user_name, sessions FROM (
         SELECT TO_CHAR(SYSDATE, 'YYYYMMDD hh24:mi:ss') AS run_date,
                username AS user_name,
                count(*) AS sessions
            FROM V$SESSION
           WHERE username IS NOT NULL
        GROUP BY username ) SESSIONS_COUNT;

     COMMIT;
   EXCEPTION
     WHEN OTHERS THEN
       write_sga_w_log(v_module_name, 'Unexpected Error: '||SQLERRM);
   END monitor_sessions;

END ADMIN_MONITOR;

-----------------------------------------------------------
CREATE OR REPLACE PACKAGE ADMIN_MONITOR IS
  PROCEDURE write_sga_w_log(p_module_name IN VARCHAR, p_msg_text IN VARCHAR2);
  PROCEDURE monitor_sessions;
END ADMIN_MONITOR;



---------------------------------------
Job - to run every 5 minutes
---------------------------------------
DECLARE
   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_MONITOR.monitor_sessions;', 
                  NEXT_DATE => SYSDATE + 5/1440, 
                  INTERVAL => 'SYSDATE + 5/1440'
 );
 COMMIT;
END;
/

Sample output from the monitoring table:

COL run_date FOR A30
COL user_name FOR A30
SET LINESIZE 120
SET PAGESIZE 400

SQL> select * FROM MONITOR_SESSIONS ORDER BY seq_id desc, sessions desc;

    SEQ_ID RUN_DATE                       USER_NAME                        SESSIONS
---------- ------------------------------ ------------------------------ ----------
         3 20170308 17:03:27              AUT_TMOQQ_SPARX                       152
         3 20170308 17:03:27              AUT_TMOBI_SHARB                        58

         3 20170308 17:03:27              AUT_MAXMO_IPNQQ                        12

         3 20170308 17:03:27              AUT_TMOBI_OVMDQ                        10

         3 20170308 17:03:27              SYSTEM                                  1
         2 20170308 16:58:26              AUT_TMOQQ_SPARX                       150
         2 20170308 16:58:26              AUT_TMOBI_SHARB                        57
         2 20170308 16:58:26              AUT_MAXMO_IPNQQ                        11
         2 20170308 16:58:26              AUT_TMOBI_OVMDQ                        10
         2 20170308 16:58:26              SYSTEM                                  1
         2 20170308 16:58:26              SYS                                     1
         1 20170308 16:53:26              AUT_TMOQQ_SPARX                       150
         1 20170308 16:53:26              AUT_TMOBI_SHARB                        57
         1 20170308 16:53:26              AUT_MAXMO_IPNQQ                        11
         1 20170308 16:53:26              AUT_TMOBI_OVMDQ                        10

         1 20170308 16:53:26              SYSTEM                                  1

===============================
Monitoring from Linux
===============================

#/bin/bash

. /etc/sh/orash/oracle_login.sh igt

sqlplus / << EOF

select username, count(username) from v\$session group by username having count(username)>10 order by 2;

select count(*) from v\$session;

exit

EOF