ORACLE PROCESSES, SESSIONS AND CONNECTIONS

In linux/unix systems a process is a physical process or thread which can be seen with "ps" command for example, There are many types of processes in Oracle, Background processes like




ajithpathiyil:/export/home/oracle>>ps -ef|grep ora_
oracle 103 1 0 Oct 02 ? 45:01 ora_lmon_AJITHDB
oracle 89 1 0 Oct 02 ? 27:30 ora_pmon_AJITHDB
oracle 105 1 0 Oct 02 ? 41:55 ora_lmd0_AJITHDB
oracle 159 1 0 Oct 02 ? 29:27 ora_lgwr_AJITHDB
oracle 161 1 0 Oct 02 ? 9:59 ora_ckpt_AJITHDB
oracle 163 1 0 Oct 02 ? 1:56 ora_smon_AJITHDB
oracle 165 1 0 Oct 02 ? 0:02 ora_reco_AJITHDB
oracle 791 1 0 Oct 02 ? 7:00 ora_arc0_AJITHDB
..
..
..
ajithpathiyil:/export/home/oracle>>

etc..... Meanwhil user processes are like dedicated servers or shared server (multi-threaded server -- aka MTS -- configuration)

A connection is a "physical circuit", a pathway to a database. You can be connected to a database yet have 0 or 1 or MORE sessions going on that connection. We can see that with sqlplus, consider (single user system here, its all about me)


ajithpathiyil:/export/home/oracle>>$ ps -auxww |grep oracleora920

ajithpathiyil:/export/home/oracle>>$ 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.

SQL> !ps -auxww |grep oracleora920
ajithpathiyil 19971 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19973 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920
SQL>

no process, no nothing


SQL> connect /
Connected.


SQL> !ps -auxww
grep oracleora920
ora920 19974 1.5 2.2 230976 11752 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ajithpathiyil 19975 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19977 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920

got my process now...

SQL> 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

SQL> !ps -auxww |grep oracleora920

ora920 19974 0.6 2.3 230976 11876 ? S 10:36 0:00 oracleora920 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
ajithpathiyil 19978 0.0 0.1 2196 916 pts/1 S 10:36 0:00 /bin/bash -c ps -auxww
grep oracleora920
ajithpathiyil 19980 0.0 0.1 1736 564 pts/1 S 10:36 0:00 grep oracleora920





SQL> select * from dual;

SP2-0640: Not connected


still have my process, but no session, the message is a little "misleading". Technically -- I have a connection, I don't have a 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:

SQL> select username from v$session where username is not null;

USERNAME
------------------------------
AJITHPATHIYIL

SQL>

one session, ME



SQL> select username, program from v$process;

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

14 rows selected.

SQL>

you can see all of the backgrounds and my dedicated server...

SQL> 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!


ajithpathiyil:/export/home/oracle> select username from v$session where username is not null;

USERNAME
------------------------------
AJITHPATHIYIL
AJITHPATHIYIL


see, 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

SQL> select username, program from v$process;

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

14 rows selected.

SQL>
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

SQL>

A connection is a physical circuit between you and the database.

A connection might be one of many types -- most popular begin DEDICATED server and SHARED server.

Zero, one or more sessions may be established over a given connection to the database as show above with sqlplus.

A process will be used by a session to execute statements.
Sometimes there is a one to one relationship between CONNECTION->SESSION->PROCESS (eg: a normal 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 however, for example 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.



Happy Learning!

Ajithpathiyil