connection和session的关系

本文解析了Oracle数据库中连接(connection)与会话(session)的概念及其关系,详细介绍了两者之间的区别与联系,包括它们如何在不同的场景下工作,例如专用服务器和共享服务器模式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

-----   from ask tom.

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

connection might be one of many types -- most popular begin DEDICATED server and SHARED server.  Zeroone 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 (ega normal dedicated server connection).  Sometimes there is a one to many from connection to sessions (eglike autotrace

one connectiontwo sessionsone 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 overthat process is released back to the pool of processes.

 

1.      连接(connection)是一个物理的概念,它指的是一个通过网络建立的客户端和专有服务器(Dedicated Server)或调度器(Shared Server)的一个网络连接。
2.
会话(session)是一个逻辑的概念,它是存在于实例中。
一个连接可以拥有多个会话也可以没有会话,同一个连接上的不同会话之间不会相互影响。

 

 

一个connection可以对应0Nsession. 但是,某一个时间点,只有一个sessionactive. 就像在sqlplus,set autotrace on时候,一个connection对应两个session, 一个foreground,一个background. 某个时刻点,只有一个是活动的.

反过来,一个session 在它的生命周期中,也是可以跨越多个connection.

 

譬如: mts 方式下,为什么说一个session不好跟踪,就是这样的. 因为,服务这个sessionconnection,(也就是process),经常发生变化的.

 

 

 

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.示例

 

SQL> conn seagull/aaaa
Connected.
SQL> select username||'  '||sid||'  '||serial#||'  '||server||'  '||paddr||'  '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL  140  61  DEDICATED  2A220948  ACTIVE


SQL> set autotrace on statistics
SQL> select username||'  '||sid||'  '||serial#||'  '||server||'  '||paddr||'  '||status from v$session where username='SEAGULL';

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL  140  61  DEDICATED  2A220948  ACTIVE
SEAGULL  142  231  DEDICATED  2A220948  INACTIVE


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

SQL>


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

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

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

3)接着在original session中执行DML

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

 

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

 

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

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL  140  61  DEDICATED  2A220948  ACTIVE

SQL>

 

此时在sqlplus中执行disconnect操作:

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

 

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

SQL> select * from v$session where username='SEAGULL';

now rows return

SQL> select addr,pid,spid,terminal,username from v$process where addr='2A220948';

ADDR            PID SPID         TERMINAL                       USERNAME
-------- ---------- ------------ ------------------------------ ---------------
2A220948         21 13132        pts/0                          oracle10

发现此时session(sid=140)已经没有了,但用之前的paddr='2A220948'还能查出信息,表明connection还在,表明1connection可以对应0session.

 

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

SQL> connect seagull/aaaa
Connected.


查询v$session

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

USERNAME||''||SID||''||SERIAL#||''||SERVER||''||PADDR||''||STATUS
--------------------------------------------------------------------------------
SEAGULL  146  103  DEDICATED  2A220948  ACTIVE

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

 

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

转载于:http://blog.itpub.net/10173379/viewspace-662348/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值