Connection/Session/Server Processes

Connection是Oracle实例和用户进程之间的通讯通道.

Session是用户通过用户进程与Oracle实例建立的一个特定的连接.比如,用户启动sql*plus,输入有效的用户名和密码,就可以与Oracle实例建立一个Session.

Oracle创建Server Processes用于控制/处理连接到Oracle实例的用户进程请求.

[@more@]

process是指操作系统连接到数据库的最大进程(并发进程)，包括后台进程和每个当前连接的用户进程（每用户算一个进程）
session=process*1.1+5

进程数的确不是用户数,一个用户可以有多个进程,但此处的进程数实际上限制了用户连接的个数,当连接的进程达到这个数值后,其它用户就无法再进行连接了

在参数文件中有三个参数
processes
license_max_sessions
license_max_users
这三个参数相互作用影响着用户连接数

license_max_sessions （同时连接数据库的会话数）
license_max_users （数据库能够创建的用户数）
如果设置为0就是不加限制。

Oracle创建Server Processes用于控制/处理连接到Oracle实例的用户进程请求.

You Asked (Jump to Tom's latest followup)

Hi Tom

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?

Thank you 

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)


[tkyte@tkyte-pc-isdn tkyte]$ ps -auxww | grep oracleora920
[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
tkyte    19971  0.0  0.1  2196  916 pts/1    S    10:36   0:00 /bin/bash -c ps 
-auxww | grep oracleora920
tkyte    19973  0.0  0.1  1736  564 pts/1    S    10:36   0:00 grep oracleora920

no process, no nothing

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

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

idle> 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:


ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is 
not null;

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

one session, ME

ops$tkyte@ORA920.US.ORACLE.COM> 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.

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

ops$tkyte@ORA920.US.ORACLE.COM> 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!


ops$tkyte@ORA920.US.ORACLE.COM> select username from v$session where username is 
not null;

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


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

ops$tkyte@ORA920.US.ORACLE.COM> 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>


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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14926/viewspace-799812/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14926/viewspace-799812/