DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS(zt from asktom)
-- Thanks for the question regarding "DIFFERENCES BETWEEN PROCESSES, SESSIONS AND CONNECTIONS", version 126.96.36.199 originally submitted on 28-Sep-2002 10:19 Eastern US time, last updated 27-Apr-2005 16:37
What's the difference between connections, sessions and processes?
I read a note from Metalink about the difference but I simply dont get it!
May you give a brief explanation?
and we said...
A process is a physical process or thread.
On unix, you can see a process with "ps" for example. It is there.
There are many types of processes in Oracle -- background processes like SMON, PMON, RECO, ARCH, CKPT, EMNn, DBWR, etc..... And user processes 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)
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
I'll try to put it into a single, simple paragraph:
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.