connection和session的关系

1.官方解释：

1.1 connection:

A connection is a physical path from a client to an Oracle instance. A connection is established either over a network or over an IPC mechanism. A connection is typically between a client process and either a dedicated server or a dispatcher. However,using Oracle’s Connection Manager (CMAN), a connection may be between a client and CMAN, and CMAN and the database.

1.2 session

A session is a logical entity that exists in the instance. It is your session state, or a collection of data structures in memory that represents your unique session. It is what would come first to most people’s minds when thinking of a “database connection.” It is your session in the server, where you execute SQL, commit transactions, and run stored procedures.

1.3 connection vs. session

A connection may have zero, one, or more sessions established on it. Each session is separate and independent,even though they all share the same physical connection to the database. A commit in one session does not affect any other session on that connection. In fact, each session using that connection could use different user identities!

In Oracle, a connection is simply a physical circuit between your client process and the database instance—a network connection, most commonly. The connection may be to a dedicated server process or to a dispatcher. As previously stated, a connection may have zero or more sessions, meaning that a connection may exist with no corresponding sessions. Additionally, a session may or may not have a connection. Using advanced Oracle Net features such as connection pooling, a physical connection may be dropped by a client, leaving the session intact (but idle). When the client wants to perform some operation in that session, it would reestablish the physical connection.

2.示例

[oracle@testdb ~]$sqlplus edu/jyedu

SQL*Plus: Release 10.2.0.4.0 - Production on星期一7月19 11:48:51 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

­SQL>select sid,serial#,server,paddr,status from v$session where username='EDU';

SID SERIAL# SERVER PADDR STATUS
---------- ---------- --------- ---------------- --------
130 11356 DEDICATED 0000000072A66118 ACTIVE

SQL>set autotrace statistic

SQL>select sid,serial#,server,paddr,status from v$session where username='EDU';

SID SERIAL# SERVER PADDR STATUS
---------- ---------- --------- ---------------- --------
130 11356 DEDICATED 0000000072A66118 ACTIVE
141 27115 DEDICATED 0000000072A66118 INACTIVE

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

从例子可以看到，进程(paddr=0000000072A66118)对应了两个session(130,141)，即我们通过一个dedicated server拥有了两个sessoin，其实那个inactive的session(141)就是autotrace session,用来watch我们真正的sessoin的，可以知道autotrace在我们发出insert,delete,update,select,merge时会作出一下动作：

1)用当前的connection新建1个new session,当然，如果创建后就不用再创建了

2)然后new session会读取original session的v$sessstat视图并记录统计信息

3)接着在original session中执行DML

4)执行完毕后，new session会再次读取original session的v$sessstat信息，并且把和之前的差别统计出来，显示给用户看.

如果关闭autotrace,那么new session会消失，如下：

SQL>set autotrace off
SQL> select sid,serial#,server,paddr,status from v$session where username='EDU';

SID SERIAL# SERVER PADDR STATUS
---------- ---------- --------- ---------------- --------
130 11356 DEDICATED 0000000072A66118 ACTIVE

此时在sqlplus中执行disconnect操作：

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

在另外的sqlplus登录界面里查询v$session

[oracle@testdb ~]$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on星期一7月19 12:31:05 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>select sid,serial#,server,paddr,status from v$session where username='EDU';

no rows selected
SQL>select addr,pid,spid,username from v$process where addr='0000000072A66118';

ADDR PID SPID USERNAME
---------------- ---------- ------------ ---------------
0000000072A66118 22 32110 oracle

发现此时session(sid=130)已经没有了，但用之前的paddr=0000000072A66118还能查出信息，表明connection还在，表明1个connection可以对应0个session.

再次原来的sqlplus里面执行connect操作:

SQL>connect edu/jyedu
Connected.

SQL>select sid,serial#,server,paddr,status from v$session where username='EDU';

SID SERIAL# SERVER PADDR STATUS
---------- ---------- --------- ---------------- --------
141 27145 DEDICATED 0000000072A66118 ACTIVE

发现1个new session在原来的connection(paddr=0000000072A66118 )基础上又被创建了。

经典例子：

有A/B两个城市，需要从A运送白菜到B城

我们先建设一条公路
然后运送白菜过去，包括准备白菜和运送白菜以及返回等一系列的动作。

一条公路，可以运送0-n次的白菜
当然从A到B的公路也可能不只一条。
某一次运送白菜，可以在真正上路时才开通某一条道路
一次运送不会影响别的运送的状态


对应数据库
A代表客户端进程
B代表服务器端进程
公路代表连接，
运送一次白菜代表一个会话

一个连接可以进行多次的会话
一个会话可以不依赖于某个连接，甚至没有连接(当我准备好了，真正开始运送时再建立连接)
一个会话不会影响别的会话